Create a Distribution List from an Outlook Item


A few days ago my boss asked me to join a new project. I agreed and within a few hours the project’s PM sent the team, myself included, an email with project details. Knowing that I’ll need to send messages to the project team, I figured I’d get ahead of the game and create a distribution list for the project that includes all the team members. The message was addressed to all the project participants, so I knew just who I needed to add. That’s when I realized that Outlook doesn’t have a convenient way to create a distribution list from a list of addresses. While you can create a new contact by dragging and dropping an email to Contacts or by right-clicking an addressee and selecting “Add to Outlook Contacts” there is no corresponding method for creating a distribution list from a message’s addressees. Instead, you have to

  1. Copy the addressees from the To line of the message
  2. Click New and select Distribution List
  3. Click the Select Members button
  4. Paste the addressees into the Members field
  5. Click OK
  6. Repeat steps 1 and 4 if you also want to copy addressees from the CC line (if there is one)

I wanted something a lot simpler like clicking a button and having a new distribution list appear. My rule of thumb is that if it’s something I need to do more than once and requires more than a couple of actions, then automate it if at all possible. So, I wrote a macro. The macro does all the work for me. It creates a new distribution list, adds everyone on the currently selected message as a member, then displays it on screen so I can give it a name, add additional members (in case I need to add someone the message didn’t include), include notes, etc. Sometimes the first communication about a project is a meeting invitation, so I wrote the macro to work with appointments as well as messages. Oh, and I don’t want to include myself in the list, so I also made sure it checks for my name and leaves me out.

Adding the Code to Outlook.

  1. Start Outlook
  2. Press ALT+F11 to open the 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 placed a comment where changes can occur.
  8. Click the diskette icon on the toolbar to save the changes
  9. Close the VB Editor
Sub CreateDistListFromItem()
    Dim olkItm As Object, olkRcp As Outlook.Recipient, olkLst As Outlook.DistListItem
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set olkItm = Application.ActiveExplorer.Selection(1)
        Case "Inspector"
            Set olkItm = Application.ActiveInspector.CurrentItem
    End Select
    If TypeName(olkItm) <> "Nothing" Then
        Select Case olkItm.Class
            'If the source item is an email or appointment
            Case olMail, olAppointment
                'Create a new distribution list
                Set olkLst = Application.CreateItem(olDistributionListItem)
                With olkLst
                    'If the source item is an email, then add the sender
                    If olkItm.Class = olMail Then
                        Set olkRcp = Session.CreateRecipient(olkItm.SenderEmailAddress)
                        olkRcp.Resolve
                        .AddMember olkRcp
                    End If
                    'Loop through the recipients of the source item
                    For Each olkRcp In olkItm.Recipients
                        'If the recipient is not the current user
                        If olkRcp.Name <> Session.CurrentUser.Name Then
                            'If the source item an email or if it is an appointment and the recipient is not a resource
                            If (olkItm.Class = olMail) Or (olkItm.Class = olAppointment And olkRcp.Type <> olResource) Then
                                'Add the recipient to the list
                                .AddMember olkRcp
                            End If
                        End If
                        DoEvents
                    Next
                    .Display
                End With
        End Select
    End If
    Set olkItm = Nothing
    Set olkRcp = Nothing
    Set olkLst = Nothing
End Sub

Using the Code.

  1. Select or open an email or appointment
  2. Run the CreateDistListFromItem macro to create a new distribution list containing everyone the item was sent to

Running with a single click.

You can add toolbar buttons (Outlook 2003) or QAT buttons (Outlook 2007/2010) to run the macro with a single click. Here’s a link to a page that explaining how to do both.

Compatibility.

I wrote the code on a computer with Outlook 2010 and have tested it on one with Outlook 2007. It works fine on both of those versions. I don’t have a computer with older versions, so I’m not sure if it will work with them or not.

Revisions.

I put this revision together for Mike who asked for a version that adds to an existing distribution list instead of creating a new one. That’s the only change in this version.

Sub UpdateDistListFromItem()
    'Create some constants
    'On the next line enter the name of the list the script is to update.
    Const LIST_NAME = "Testing"
    'Create some variables
    Dim olkItm As Object, olkRcp As Outlook.RECIPIENT, olkLst As Outlook.DistListItem
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set olkItm = Application.ActiveExplorer.Selection(1)
        Case "Inspector"
            Set olkItm = Application.ActiveInspector.CurrentItem
    End Select
    If TypeName(olkItm) <> "Nothing" Then
        Select Case olkItm.Class
            'If the source item is an email or appointment
            Case olMail, olAppointment
                'Find the distribution list to update
                Set olkLst = Session.GetDefaultFolder(olFolderContacts).Items.Find("[Name] = '" & LIST_NAME & "'")
                If TypeName(olkLst) = "Nothing" Then
                    MsgBox "I could not find a distribution list named '" & LIST_NAME & "'.", vbCritical + vbOKOnly, "Operation Cancelled"
                Else
                    With olkLst
                        'If the source item is an email, then add the sender
                        If olkItm.Class = olMail Then
                            Set olkRcp = Session.CreateRecipient(olkItm.SenderEmailAddress)
                            olkRcp.Resolve
                            .AddMember olkRcp
                        End If
                        'Loop through the recipients of the source item
                        For Each olkRcp In olkItm.Recipients
                            'If the recipient is not the current user
                            If olkRcp.Name <> Session.CurrentUser.Name Then
                                'If the source item an email or if it is an appointment and the recipient is not a resource
                                If (olkItm.Class = olMail) Or (olkItm.Class = olAppointment And olkRcp.Type <> olResource) Then
                                    'Add the recipient to the list
                                    .AddMember olkRcp
                                End If
                            End If
                            DoEvents
                        Next
                        .Display
                    End With
                End If
        End Select
    End If
    Set olkItm = Nothing
    Set olkRcp = Nothing
    Set olkLst = Nothing
End Sub
Advertisements

12 comments on “Create a Distribution List from an Outlook Item

  1. Hi,

    Was pleased to find this as I am not familiar with VBA in outlook and this was v close to what i was looking for (particularly the update a DL version). Can you advise on how I could change it so that it would work on multiple selected email files?

    Thanks

    • Hi, Tony.

      Are you saying you want to select multiple messages and create/update one DL from them all, or that you want to create/update a different DL from each message? I’m guessing it’s the former, but want to make sure.

  2. Works in Outlook 2003.

    Another approach (which would avoid a loop as well as solve Harry’s problem) would be to use .AddMembers to add all the recipients in one go, then remove yourself from the DL like this.

    Sub CreateDistListFromItem()
      Dim olkItm As Object, olkRcp As Outlook.Recipient, olkLst As Outlook.DistListItem
      Dim sender As Outlook.Recipient
      Dim myself As Outlook.Recipient
    
      Select Case TypeName(Application.ActiveWindow)
      Case "Explorer"
        Set olkItm = Application.ActiveExplorer.Selection(1)
      Case "Inspector"
        Set olkItm = Application.ActiveInspector.CurrentItem
      End Select
      If TypeName(olkItm)  "Nothing" Then
        Select Case olkItm.Class
        Case olMail, olAppointment
          Set olkLst = Application.CreateItem(olDistributionListItem)
          Set sender = session.CreateRecipient(olkItm.SenderName)
          Set myself = session.CreateRecipient(session.CurrentUser.Name)
          ' need to resolve both recips first
          sender.Resolve
          myself.Resolve
          With olkLst
            ' add sender to dl
            .AddMember sender
            ' add all recipients, then ...
            .AddMembers olkItm.Recipients
            ' ... remove self from dl
            .RemoveMember myself
            ' show DL so it can be edited/saved
            .Display
          End With
        End Select
      End If
    End Sub
    

    Sorry I do not see any way to properly format this code on your blog.

    • Hey, JP.

      I formatted it for you. On WordPress you can enclose code in “[“code”]” “[“/code”]” (without the quotes) tags to format it.

      I originally used .AddMembers without a loop, but changed to using a loop to remove myself and with the intent of adding a check to avoid resource accounts if the item is an appointment. I wrote this post pretty quickly and see that I forgot to add the test for resource accounts.

    • First. AWESOME.
      Second. Can someone help me with this code, I need to tweak it. Basically when I run the macro, I don’t want it to create a new list, I want it to add the sender (and only the sender) to a distribution list that already exists. I’m using Outlook 2007 if that matters.

    • Thanks, Mike. I’m glad you like it.

      I’ve just updated the post by adding a revision that adds names to an existing list instead of creating a new list.

    • Thanks David! it works great, or at least it works as intended.

      I was trying to create a distribution list so that I could then create a rule to move mail from anyone on that distribution list in to a separate folder. But I can’t use a distribution list in a rule! So, all that trouble for naught. But at least I learned a little more about how to call a distribution list. I’m familiar with VBA for CorelDraw, but the documentation for Office is a little bit lacking. Thanks for the help!

    • Hi, Harry.

      Good point. I wrote it to only add the folks it was addressed to and didn’t include the person it came from. I’ll make that change as soon as I can and let you know when I have.

    • I’ve updated the code by adding the sender and ensuring that resources (e.g. meeting rooms) don’t get added to the if the source item is an appointment.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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