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.
Follow these instructions to add the code to Outlook.
- Start Outlook
- Press + to open the Visual Basic Editor
- If not already expanded, expand Microsoft Office Outlook Objects
- If not already expanded, expand Modules
- 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.
- Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
- Click the diskette icon on the toolbar to save the changes
- 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.