Exporting an Outlook Distribution List to Excel

Here’s a question I stumbled across last week on Twitter.

What is the secret to exporting an Outlook email contact list to an Excel file? Bueller…. anyone… holla.

— Caity Rogowski (@caityrogo) May 9, 2013

As I explained in my reply to Caity, there is a way to do it. This YouTube video from The SmartVA shows how. In short, you have to open the distribution list, act like you’re going to send it to someone else in internet format, open the attachment in the resulting email, copy the contents, then launch Excel and paste the list into a spreadsheet. That’s too many steps, especially if you need to export distribution lists regularly. We should able to export a distribution list with, at most, two actions: select the list, export it. If only there were some way to automate the process.

VBA to the rescue. With a few lines of code, we can make exporting a distribution list a no-brainer. Select the list and run the macro. Out comes a spreadsheet with the names in column A and the addresses in column B. It’s hard to get much simpler than that. Hopefully this solution will be more to Caity’s liking, yours too, than the process the video describes.

Advertisement

Script Adding Entries to Distribution Lists in the GAL

Last week, a reader named Ashwath posted a comment asking about the possibility of updating a public distribution list. Here’s what Ashwath said.

I am trying to find an automated way to add members to a Public Distribution List on the Global Address List. Right now, the only way to do it is to manually look up for a contact one by one in the Global Address List and then add them, but I’ve close to 300+ names to do this. Would you be help to help with a macro for either outlook 2007/Excel 2007 that can automatically pick e-mail addresses from an excel sheet and then add them to the PDL?

P.S: This Distribution List is not an Outlook Distribution List. It is a Distribution List created on the GLobal Address List by my ‘IT’ department; so there is no easy way to copy and paste multiple e-mail addresses.

Public distribution lists (i.e. a mail enabled Active Directory groups) have little to do with Outlook from a scripting perspective. They reside in Active Directory and are not created or updated through the scripting of Outlook. Instead, they are handled through ADSI (Active Directory Services Interfaces) scripting. Although the solution doesn’t involve Outlook, I could have written the necessary script in VBA and run it from either Outlook or Excel. There’s little point to doing it in Outlook, and it makes more sense to keep the script outside of Excel since that will give Ashwath more flexibility. In my opinion, the best solution is to create the script using VBscript, which is the approach I’ve taken here. One of the benefits of using VBscript is that Ashwath can drop the script in his computer’s Send to folder, allowing him to send a spreadsheet to the script for processing. With this approach, Ashwath can even update different distribution lists from the same spreadsheet.

Replacing Outlook Distribution Lists with Categories

Categories are one of Outlook’s most powerful features. Besides organizing items into groups they also enable you to color code them. For example you could create and assign a category to all messages, appointments, contacts, and tasks pertaining to a given project that would color them blue. Another, somewhat lesser known, feature is that you can use categories in lieu of a distribution list. For those of you who didn’t know about this capability, here are the steps you’d follow to do this in Outlook 2007:

1. Switch to your Contacts folder
2. Switch to the By Category. view
3. Select a category by clicking on the group header
4. Click Actions -> Create -> New Message to Contact

Using categories instead of distribution lists is actually quite useful. You don’t have to create or maintain any lists, just categorize your contacts as you add them. Another advantage is that there is no limit to the number of items assigned to a category, but there is a limit on the number of members in a distribution list. The limit is actually a size limit, but it effectively limits the maximum number of distribution list members to about 125 – 130 entries. For best results Microsoft recommends keeping distribution lists to no more than 50 – 70 members. The problem with using categories as distribution lists is that there are too many steps involved and you cannot add contacts to the To, CC, or BCC line like you can a distribution list. Is there a way to overcome these issues? The answer is Yes.