How Many Members Are in an Outlook Distribution List?

I ran across a tweet today from Gillian Vrooman that raised an issue I had never been concerned with: how many members are there in a given Outlook distribution list?  Here’s what she said: “Seriously? Why can’t I find a total number of members listed into an #Outlook 2007 personal distribution list? It should be simple”.  I agree with Gillian, it should be simple. Unfortunately it isn’t.  Outlook does not have a built in means of showing the number of members in the list even though the underlying object has a property MemberCount that keeps count of the number of members.

There are a couple of potential solutions for getting the count.  In my opinion the simplest one is to use a macro that displays the count.  Here’s what such a macro might look like.

Sub ShowDistListMembershipCount()
    Const MACRO_NAME = "Dist List Members"
    Dim olkList As Object
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set olkList = Application.ActiveExplorer.Selection(1)
        Case "Inspector"
            Set olkList = Application.ActiveInspector.CurrentItem
    End Select
    If TypeName(olkList) = "DistListItem" Then
        msgbox "There are " & olkList.MemberCount & " member(s) in the list.", vbInformation + vbOKOnly, MACRO_NAME
        msgbox "No distribution list open/selected.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    Set olkList = Nothing
End Sub

Adding the Code to Outlook. Adding the code to Outlook is simple. Here’s how.
1. Start Outlook
2. Click Tools > Macro > Visual Basic Editor
3. If not already expanded, expand Microsoft Office Outlook Objects
4. If not already expanded, expand Modules
5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
7. Edit the code as needed. I included comments wherever something needs to or can change
8. Click the diskette icon on the toolbar to save the changes
9. Close the VB Editor

Using the Code. The code is simple to use. After opening or selecting a distribution list run the macro. It’ll display a dialog-box that shows the number of members the list is composed of.

I’m going to tweet this solution back to Gillian. I hope she finds it useful. Hopefully you will too.


26 comments on “How Many Members Are in an Outlook Distribution List?

  1. Alternate method is to:
    create a dummy message with the list
    expand the list
    select all
    copy to Word
    find and replace: “; ” with “^p” [or anything!]
    dialog box shows number of replacements
    add 1 to that number

  2. Pingback: Outlook 2010 Distribution List Unknown |

    • Sunny,

      It’s not a problem with the macro. Outlook itself has some quirks and one of them is that sometimes enabling macros doesn’t work until you try and run a macro. Add this code to the ThisOutlookSession module, then close and restart Outlook. On restarting you should be prompted to enable macros. Answer in the affirmative. Then try my macro. It should work. If it does, then you can delete this code.

      Private Sub Application_Startup()
          msgbox "Macros should now be enabled."
      End Sub
    • Gotcha.
      Done that, macros enabled.
      Here’s what happens now: I open my distribution list, run the macro, and get the “No distribution list open/selected” msg. It most certainly is open, and I happen to know there are 648 people on it because I exported it as text and did a line count in Word.
      I hate Outlook.

    • “… 648 people on it …”

      You must be opening an Exchange distribution list in the GAL not a personal distribution list in Outlook. I say that because a personal distribution list won’t hold 648 entries. They’re limited to about 120 – 130 depending on the length of the names and addresses. An Exchange distribution list is actually a group in Active Directory. Although they serve the same purpose the two are very different things. Of course you can add an Exchange distribution list to your contacts in Outlook. However, the resulting entry will contain a single member which is a pointer back to the Exchange list. If you run this macro against that list it will tell you the list has one member. That’s because the macro is using the MemberCount property of the distribution list item. Getting the true count would require a different approach, one that iterates through the members expanding lists and sub-lists.

    • It’s a personal list, not an Exchange list.
      I attempted to import it into Outlook 2003, but was prevented from doing so because of the limit..
      However, I successfully imported it into Outlook 2007, although it complained about it every step of the way “This version now supports longer lists, whereas previous versions only supported . Do you wish to contine?” (or something similar). I just kept clicking “yes” until they all came in.
      It did import, and I use it weekly. It’s my own personal list, not part of the global address list.
      It would be nice if Outlook could provide this simple piece of information about the number of entries. Things like this were so much easier in Eudora. Man, I loved that mail client.

    • Don’t I feel foolish. Somehow I missed that Office 2007 SP2 did away with the previous limit. Thanks for pointing that out. I’ve no experience with Eudora, so I can’t comment on it. Is it programmable like Outlook?

      Since this is a personal distribution list the code should work. To verify that I created a DL with 648 to match what you have. I then ran the macro against it and it correctly displayed that the list has 648 members. So, let’s try this. Instead of opening the list just select it. In other words, open Contacts and click on the list. Now run the macro. Let me know what happens.

    • This is getting more and more strange.

      I tried just selecting it; got the same msg. I tried it on a smaller list (5 members); same msg. But that was an import from Eurdora too, so I wondered if there was something odd about my lists. So I created a completely new list within Outlook and ran the macro against it, but still got the same msg.

      I’ve reconfirmed that macros are enabled in the “trust” centre; they are. And your test macro runs at startup.

      Really odd. Must be something weird at my end if it works for you and others but not me.

      Eudora had no macro ability, but its simplicity is what I loved. It was faster and more responsive than Outlook, had a tiny footprint, and just made more sense in the way it handled simple tasks. Attachments were stored in a separate directory, so could be managed outside the client (I loved that!). I had complete control of the mailboxes (couldn’t believe the hoops I had to jump through to remove some of the system mailboxes in Outlook!). Mailing lists were stored as plain text in a simple file, no dramas. Ah, the good ol’ days.

    • Ok, let’s see if we can figure out what’s going on. Add this code to what you already have. Select or open the list and then run this macro. It’ll display a dialog-box reporting the active window type and the class of the open/selected item. Give me those two pieces of information and I’ll try to figure out what’s happening.

      Sub SelectionInfo()
          Const MACRO_NAME = "Selection Info"
          Dim olkList As Object, strActWin As String
          Select Case TypeName(Application.ActiveWindow)
              Case "Explorer"
                  strActWin = "Explorer"
                  If Application.ActiveExplorer.Selection.Count > 0 Then
                      Set olkList = Application.ActiveExplorer.Selection(1)
                      Set olkList = Nothing
                  End If
              Case "Inspector"
                  strActWin = "Inspector"
                  Set olkList = Application.ActiveInspector.CurrentItem
              Case Else
                  strActWin = "Unknown"
                  Set olkList = Nothing
          End Select
          If TypeName(olkList) = "Nothing" Then
              MsgBox "The active window is of type '" & strActWin & "'.  There is no open/selected item.", vbInformation + vbOKOnly, MACRO_NAME
              MsgBox "The active window is of type '" & strActWin & "'.  There open/selected item has a class of " & olkList.Class & ".", vbInformation + vbOKOnly, MACRO_NAME
          End If
          Set olkList = Nothing
      End Sub
    • I added your code, opened the large list, and ran the macro:

      “The active window is of type ‘Inspector’. There open/selected item has a class of 69.”

      Interesting grammar there.

    • Apologies for being slow to respond. An Inspector window means the top-most window contained an open item. Item class 69 is a distribution list. In other words, everything looks right. So, let’s try this. Open a distribution list, any one, then run this code again. If you get the same results (i.e. window type is Inspector and item class is 69), then clear the dialog-box and try running the macro to get the member count. Let me know what happens. If you get the same error you’ve been getting, then I’m at a bit of a loss. I don’t know how it’s possible for one macro to see the item as a list while the other doesn’t.

    • Ok, did that. Same error happens.

      Then I figured I’d start from scratch, just in case I did something monumentally stupid when inserting the code the first time. I copied/pasted your original code again. Now here’s what happens:

      On my imported list: I get a message that there is 1 member on the list (actually there are 648).
      On a newly created test list: I get a message that there are 4 members (which there are!).

      So, I guess that means there’s something screwy about the imported list. I wonder if it’s because of the way I imported it. From memory (it was ages ago), I had a helluva time getting it in, and might have ended up pasting it as one big thing into any field that would ruddy-well take it.

      I’m ok with that. I’ll just have to throw it all into Word and do a line count every time my manager wants to know how many people are on our mail-out list. One day, when I have the time and energy, perhpas I should recreate it manually from scratch; bet it’d work then.

      Thanks for your efforts on this. It’s an interesting one.

  3. I’m using MS Office Outlook 2007 SP2
    It’s highlighting
    msgbox “There are ” & olkList.MemberCount & ” member(s) in the list.”, vbInformation + vbOKOnly, MACRO_NAME

    • When looking at the code in the VB editor do you see “&” (without the quotes) or do you see an actual ampersand (i.e. a &)? If the former, then change both instances to ampersands.

    • I got “&” when pasting in the code, but when replacing it with merely “&”, it worked fine. Excellent solution for a problem with a most ridiculous existance. Thanks David!

    • Haha, very funny. When trying to write “& a m p ;” (without spaces”, it turns out as “&” here in the blogg … makes it a little harder…

    • It’s a WordPress quirk. Doesn’t appear to be any way to change the behavior, or at least none that I’ve discovered.

    • Where (i.e. what line) is throwing the syntax error? What version of Outlook are you using?

      There are no changes necessary for this code. The instructions are a generic set I use for Outlook code posts.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s