Exporting an Outlook Distribution List to Excel


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

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.

Instructions.

Follow these instructions to add 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 InsertModule.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
  8. Close the VB Editor
Option Explicit

Sub ExportDistributionListToExcel()

    '--> Create some constants
    Const SCRIPT_NAME = "Export Distribution List to Excel"
    
    '--> Create some variables
    Dim olkLst As Object, _
        olkRcp As Outlook.RECIPIENT, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intCount As Integer, _
        lngRow As Long, _
        strFilename As String
        
    '--> Initialize variables
    lngRow = 2
    
    '--> Main routine
    'Turn error handling off
    On Error Resume Next
    'What type of window is open?
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set olkLst = Application.ActiveExplorer.Selection(1)
        Case "Inspector"
            Set olkLst = Application.ActiveInspector.CurrentItem
        Case Else
            Set olkLst = Nothing
    End Select
    'Was a list open or selected?
    If TypeName(olkLst) = "Nothing" Then
        'No
        MsgBox "You must select or open an item for this macro to work.", vbCritical + vbOKOnly, SCRIPT_NAME
    Else
        'Yes
        'Is the open/selected item a dist list?
        If olkLst.Class = olDistributionList Then
            'Yes
            'Connect to Excel
            Set excApp = CreateObject("Excel.Application")
            Set excWkb = excApp.Workbooks.Add
            Set excWks = excWkb.Worksheets(1)
            'Write headers
            With excWks
                .Cells(1, 1) = "Name"
                .Cells(1, 2) = "Address"
            End With
            'Read the list members and write them to the spreadsheet
            For intCount = 1 To olkLst.MemberCount
                Set olkRcp = olkLst.GetMember(intCount)
                excWks.Cells(lngRow, 1) = olkRcp.Name
                excWks.Cells(lngRow, 2) = olkRcp.Address
                lngRow = lngRow + 1
            Next
            'Autofit the columns
            excWks.Columns("A:B").AutoFit
            'Get a file path/name to save the spreadsheet to
            strFilename = InputBox("Enter a path and file name for this export", SCRIPT_NAME, Environ("UserProfile") & "\My Documents\" & olkLst.Subject & ".xlsx")
            'Did we get a file path/name?
            If strFilename = "" Then
                'No
                'Set the file path to your Documents folder and the file name to the name of the list.
                strFilename = Environ("UserProfile") & "\My Documents\" & olkLst.Subject & ".xlsx"
            Else
                'Yes
                'If the file extension isn't .xlsx
                If Right(LCase(strFilename), 5) <> ".xlsx" Then
                    'Set the extention so .xlsx
                    strFilename = strFilename & ".xlsx"
                End If
            End If
            'Close and save the spreadsheet
            excWkb.Close True, strFilename
            'Did the file save okay?
            If Err.Number = 0 Then
                'Yes
                MsgBox "Export complete.", vbInformation + vbOKOnly, SCRIPT_NAME
            Else
                'No
                'Make Excel visible so the user cansave the file
                excApp.Visible = True
            End If
        Else
            'No
            MsgBox "The item you selected is not a distribution list.  Export cancelled.", vbCritical + vbOKOnly, SCRIPT_NAME
        End If
    End If
    
    '--> Clean-up
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    Set olkRcp = Nothing
    Set olkLst = Nothing
    'Turn error handling back on
    On Error GoTo 0
End Sub 

Adding Buttons to Run the Macro with a Single Click.

If Caity wants to run the macro with a single click, then she’ll need to add a toolbar button in Outlook 2007 or a button on the Quick Access Toolbar (QAT) for Outlook 2010. Here’s how.

  • Outlook 2007. Follow these instructions to add a toolbar button that runs the macro.
  • Outlook 2010. Follow these instructions to add the macro to the QAT.
Advertisements

8 comments on “Exporting an Outlook Distribution List to Excel

  1. when i highlight a distribution list either from a new email or when I make like I am going to reply to a mail and click the macro button I get message “The item you selected is not a distribution list “, but it clearly is!

    Lee

    • Hi, Lee.

      Yes, it is a distribution list but it’s not a distribution list object which is what the script is looking for. Switch to Contacts. Distribution lists you see there are distribution list objects. Once added to an email a distribution list becomes a recipient object. I can see where that might be confusing, but they are different object types even though both are distribution lists. That brings up another issue. From a programming perspective there’s no way to detect which recipient is selected in a message. In other words, there’s no way to know that you have selected a particular addressee on the To, CC, or BCC lines. That precludes modifying the code to behave the way you’d like it to.

  2. Hi David! Wow, it would be so great to have this! But I also got an error. I’ve included the Macro button on QAT, but no success… I type a list, I click on the shortcut for the module, but then the VBA opens and says: “compile error: user-defined type not defined”. Any clues? Thank you so much!!

    • Hi, marquesjuliana.

      I see I forgot to remove some references before I published this post. I’ve just corrected that. Please delete the code you have now, then download this code again. Sorry for the error.

    • Hi David, thank you so much for your quick reply! No problem at all. I’ve included and saved the code again, but maybe I’m doing something wrong. Where exactly should I run the macro? I’ve included the shortcut button on the ‘new message’ window and also on the People/contacts window, but when I select a list and click on the button nothing happens. Thank you again!
      🙂

    • Hi, marquesjuliana.

      The code cannot export a distribution list in a message. It can only export a distribution list object, i.e. a distribution list you see in your Contacts folder. To use this solution, switch to Contacts, then find and select a distribution list. Now, run the macro. It will export the selected distribution list.

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