Macro to fetch email addresses for a list of names from Exchange server

I came across an interesting problem the other day... one of my colleagues had left our firm in a hurry. But she wanted to send a farewell mail to all her colleagues - she'd got a list of names by copying the names in the "To" list from a mail opened in her office Outlook. However, these were only names (as they existed in our Exchange Server) and did not contain the actual email address.

I thought it was a simple thing to give her the email address list corresponding to those names right from my Outlook window... but after a frustrating 60 mins of trying all possible combinations, I had to accept defeat.

Instead, I made a little macro that does the job - just provide the list of names as on the exchange server, and it will fill the next column with the email addreses. Download the Excel sheet having macro here .. Check out the Readme worksheet for instructions.

Note: PREREQUISITE
You should have a signed-in session with Outlook connected to your Exchange server before running the macro.

For those who are curious about how it works, the code simply accesses the Global Address List of your exchange server through your signed in Outlook account, fetches the usernames (cn values from the exchange directory) and then manually appends the company domain (this is configurable) to it. Of course it won't work for those with multiple company domains... You can check out the code in the macro's section of the excel sheet (press Alt-F11 after opening the excel sheet).

For more robust handling like getting the correct whole smtp address (instead of appending the domain manually) is possible but it would entail using extended MAPI (lots n lots of code) or extra libraries CDO / Redemption with C++.

But VBA suits my purpose. And that's what good software is all about - meeting the need (not the wants)!