Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Retrieving the Members of Outlook Distribution Lists

Last week we featured a script that not only showed how you can use a script to retrieve a list of all the contacts in your Outlook Contacts folder, but also showed how you can export that list to Microsoft Excel.

Thank you, thank you. But please, hold your applause until the end.

At any rate, we positioned that script as a sort of introduction to working with contacts in Outlook, noting that we were going to skip over certain tasks, tasks like retrieving contacts from any subfolders within the Contacts folder or tasks like echoing back information about all the members of an Outlook distribution list. No doubt you thought to yourself, “Ah, I get it: this is an introduction to working with contacts, so the Scripting Guys – who always put the interests of their readers ahead of everything else – are keeping things nice and simple for us. Good idea, Scripting Guys!”

Thank you, and you’re right: that would have been a good idea had we actually thought of it. To tell you the truth, however, the Scripting Guy responsible for this column isn’t exactly on the cutting-edge of technology: he doesn’t own a cell phone (no, he’s not brain-damaged…at least not much); he doesn’t see what’s the big deal about high-definition TV; and he has neither subfolders nor distribution lists in his Contacts folder. Consequently, he was totally oblivious to all these issues until our eagle-eyed editor started nagging him incessantly about them.

Um, we mean, until our eagle-eyed editor helpfully brought them to his attention.

Regardless, this Scripting Guy was too lazy to address those issues in last week’s article, but vowed to address them “in the near future.” To tell you the truth we didn’t believe him either, but guess what: today he’s going to show you how to retrieve the members of an Outlook distribution list.

Well, just as soon as he creates an Outlook distribution list; give him a minute here. OK, he’s ready now:

Hey, everyone, and welcome to today’s column. Apparently there’s some interest in being able to list all the members of an Outlook distribution list. Well, why didn’t you say so? Here’s a script that reports back all the members of all the distribution lists found in the Contacts folder in Outlook. We’ll take a look at the script, then talk about how it works:

Const olFolderContacts = 10

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")

Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items
intCount = colContacts.Count

For i = 1 To intCount
    If TypeName(colContacts.Item(i)) = "DistListItem" Then
        Set objDistList = colContacts.Item(i)
        Wscript.Echo objDistList.DLName
        For j = 1 To objDistList.MemberCount
    Wscript.Echo objDistList.GetMember(j).Name & " -- " & _
               objDistList.GetMember(j).Address
        Next 
        Wscript.Echo
    End If
Next

You’re right: it’s not a very long script, but it does look a little bit complicated, doesn’t it? But that’s because Outlook doesn’t provide an easy way to grab a collection of distribution lists (for example, the way you can search Active Directory and quickly retrieve a list of users or groups). Instead, we have to look at each and every item in the Contacts folder and check to see if the item happens to be a distribution list. If it is, we can then bind to that item (the distribution list) and methodically echo back all the members. Otherwise, we just skip it and check the next item in the Contacts folder. Kind of the old-fashioned, brute force method, but it works.

Ah, come on, it’s not that bad. In fact, the script starts out pretty easy. We begin by defining a constant named olFolderContacts and setting the value to 10; we’ll use this constant to tell the script which Outlook folder to bind to. We then create an instance of the Outlook.Application object and use the GetNamespace method to bind to the MAPI namespace. (Which, by the way, is the only namespace that we can bind to. Nevertheless, we can’t skip this line of code or the script will fail.)

After we’ve made a connection to the MAPI namespace we then bind to the Contacts folder and retrieve a collection of all the items stored in that folder; that’s what we do with this line of code:

Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items

Having retrieved the collection we then use the Count property to determine the number of items in the folder. Why? Well, remember, we’re going to have to examine each item individually to see whether or not it happens to be a distribution list. To do that, we need to set up a For Next loop that runs from 1 to the number of items found in the folder. (In other words, if there are 69 items in the Contacts folder, our loop will run from 1 through 69.)

So how do we know if an item is a distribution list? That’s what we use this line of code for:

If TypeName(colContacts.Item(i)) = "DistListItem" Then

Here we’re using the VBScript function TypeName to figure out exactly what we’re dealing with. TypeName is used to return information about a variable. For example, if you pass TypeName the name of a string variable, it reports back “String”. Pass it the name of a Null variable and it reports back “Null”. Pass it the name of an object, however, and it reports back the name of that object. In the Outlook object model distribution lists are instances of the DistItemList object. Therefore, we check to see if the TypeName of the item is equal to "DistListItem".

So what if we do have our hands on a distribution list? Well, we then bind to the list itself and echo back the DLName (the name of the list). That requires two lines of code:

Set objDistList = colContacts.Item(i)
Wscript.Echo objDistList.DLName

We then create a second For Next loop; this one loops from 1 to the number of people on the distribution list. How do we know the number of people on a given distribution list? That’s easy: we just check the value of the MemberCount property:

For j = 1 To objDistList.MemberCount

For each person on the list we then echo back the person’s name and his or her email address:

Wscript.Echo objDistList.GetMember(j).Name & " -- " & _
    objDistList.GetMember(j).Address

Notice that we need to use the GetMember method to retrieve the Name and Address for each recipient. As you probably figured out, with GetMember you pass the method an index number and it rushes out and retrieves information for the distribution list member with that index number.

When you run the script you should get back output similar to this:

IT List
Ken Myer -- kenmyer@fabrikam.com
Pilar Ackerman -- packerman@contoso.com
Jonathan Haas -- jhaas@contoso.com

Before you ask, no, we can’t echo back things like the person’s business phone number or birthday. Why not? That’s easy: that information isn’t available for anyone included on a distribution list. Business phone number and birthday are properties of Outlook contacts; however, people on distribution lists aren’t contacts. Instead, they are instances of the Recipient object, an object that has relatively few properties. (For a complete list, see the Microsoft Outlook VBA Language Reference.)

Of course, you can have Outlook contacts on your distribution lists; in that case all those other properties – such as business phone and birthday – will be available if you access these people as contacts. If you access them as recipients (that is, members of a distribution list) you’ll have only a limited set of properties to work with.

As we noted, the Address property we used in our script is the email address for the recipient. Because we’re dealing with an email address, we’re going to be subject to Outlook security; when you run the script the following dialog box is going to appear on screen:

You’ll need to select the Allow access for check box, pick a time period, and then click Yes before the script can retrieve email information for your recipients.

Now, about listing all the contacts found in any subfolders of the – you’re right: distribution lists are more than enough for one day. We’ll deal with subfolders later.

Until then, do they really make little portable phones that you can carry around with you anywhere you go? Fascinating. And TVs that show pictures in color? Amazing.