Creating Multiple Outlook Distribution Lists from ExcelPublished: Wed, 15 May 2013 23:17:06 GMT
Suppose you have a list of companies you deal with, and each company has more than one contact. You want to send a customised email to each company based on entries in an Excel spreadsheet. You want each of the recipients to be in the ‘To:’ field of the email. You’re out of luck because a mail merge from Excel will only accept a single email address, even if you separate them with semicolons.
My solution is to create a local distribution list in Outlook for each company and send to the distribution list name – this will resolve to the multiple email addresses and the recipients will be able to see who else received the email.
Unfortunately, I had over 200 companies with between 2 and 9 contact addresses each. It would have been a huge job to create these distribution lists manually, and keeping them up to date would have been a headache too.
After some research, I found an Excel macro which was a great starting point and I was able to customise it to create the distribution lists for me.
Firstly, my spreadsheet containing the distribution list data looks like this:
|2||Company Name||Contact 1||Contact 2||Contact 3||Contact 4|
The original macro was configured to generate a single distribution list and required the spreadsheet to be arranged vertically in two columns. I made a few changes and additions as follows:
' Create multiple Outlook distribution lists from Excel spreadsheet
' by Andy Younie http://www.planetmediocrity.com
' Heavily based on JP's code which can be found at
Const olDistributionListItem = 7
Const olFolderContacts = 10
Dim DNAME as String ' Distribution list name
Dim outlook As Object ' Outlook.Application
Dim contacts As Object ' Outlook.Items
Dim myDistList As Object ' Outlook.DistListItem
Dim newDistList As Object ' Outlook.DistListItem
Dim objRcpnt As Object ' Outlook.Recipient
Dim arrData() As Variant
Dim rng As Excel.Range
Dim numRows As Long
Dim numCols As Long
Dim i As Long
Dim x As Long ' Counter for groups
Set outlook = GetOutlookApp
Set contacts = GetItems(GetNS(outlook))
' Count how many groups there are in the list
numRows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
' Start loop to create distribution list for each group
For x = 3 To numRows 'First group is on line 3 of the spreadsheet
' Set DNAME to the group name in column A
DNAME = ActiveSheet.Cells(x, "A").Value
On Error Resume Next
Set myDistList = contacts.item(DNAME)
On Error GoTo 0
If Not myDistList Is Nothing Then
' delete it
' recreate it
Set newDistList = outlook.CreateItem(olDistributionListItem)
.DLName = DNAME
.body = DNAME
' loop through worksheet and add each member to dist list
' assume active sheet
numCols = Activesheet.Cells(x, "A").CurrentRegion.Columns.count - 1
ReDim arrData(1 To 1, 1 To numCols)
' take Group Names out of range
Set rng = Activesheet.Range("A1").CurrentRegion.Offset(x - 1, 1).resize(1, numCols)
' put range into array
arrData = rng.value
' assume 1 row with a variable number of columns
For i = 1 To numCols
Set objRcpnt = outlook.Session.CreateRecipient(arrData(1, i))
' End loop to create distribution list for each group
Function GetOutlookApp() As Object
On Error Resume Next
Set GetOutlookApp = CreateObject("Outlook.Application")
Function GetItems(olNS As Object) As Object
Set GetItems = olNS.GetDefaultFolder(olFolderContacts).items
Function GetNS(ByRef app As Object) As Object
Set GetNS = app.GetNamespace("MAPI")
So, where do you put the code to make it work? Open your spreadsheet in Excel, and go to View > Macros (or press Alt+F8) to bring up the Macros dialogue. Type a macro name (it doesn’t matter what name you use at this point) then click the ‘Create’ button. You are now in the VB editor. Delete the code which looks like:
Now, paste in the code from above and close the editor window to get back to your spreadsheet.
Save your spreadsheet, then press Alt+F8 again, you should see a macro called ‘MaintainDistList’ – highlight it and click the ‘Run’ button.
You will be prompted to allow access to Outlook, accept that and flick to your contacts list in Outlook – you should see all the new distribution lists being created.
You will now be able to do a mail merge from a spreadsheet which contains your company names and the data you want to merge into your template document. The company name will resolve to the addresses in the distribution list which has the same name.
An example spreadsheet containing company information:
|2||Company Name||Value||Number of Orders||Renewal due||CEO Name|
|3||Acme Inc.||$20,135||23||May 2013||John Smith|
|4||Beta Co.||$13,998||12||July 2013||Carrie Jones|
|5||Carrot Design||$18,268||58||January 2014||Simon Brown|
I hope this is useful to you, let me know in the comments how you get on!
Adam: Hi there, i wondered if someone can help. It seems in the new excel and outlook 2013 is has a debug error on : - Line 42 - myDistList.Delete do you have any idea what this is? it was working fine before. Thanks in advance. Adam
tye: Awesome Macro... Great for making a DL from contacts. How must the code be changed if I want a Distribution list that is made up of other Distribution lists? Example: DL_Dept_A Tom@comp.com; Dick@comp.com; Harry@comp.com DL_Dept_B Jill@comp.com; Jane@comp.com; Joy@comp.com ... DL_3rd_Floor DL_Dept_A; DL_Dept_B; DL_Dept_C;... ... DL_Denver_Office DL_1st_Floor; DL_2nd_Floor; DL_3rd_Floor
Andre Luiz Emidio Junior: Hi Thanks for you code and knowledge. I was find at last month and you help me a lot. Thank you !!