Is there any way I can, with a macro, create a nested distribution list with the info in an excel sheet? Basically, I have a sheet where column A is the name for the nested distlist, and then columns B-F have the names of the distlists that should go into the nested one. Currently, the macro I have written can create and save the distlist, but can't seem to resolve when I'm trying to add the other distlists to it.
Don't judge this code too harshly, I've never done something like this before so a lot of it is copy/pasted from various articles and tweeked as necessary.
Option Explicit
Public Sub ImportNestedContactGroups()
'***IMPORTANT***
'***Workbook should be saved as Nested Contact Groups***
'***Worksheet should be saved as Sheet1***
'***COLUMN A SHOULD HAVE 'Nested Contact Group Name' AS THE NAME IN A1 WITHOUT QUOTES***
'***COLUMN B SHOULD HAVE 'Contact 1' AS THE NAME IN B1 WITHOUT QUOTES***
'***COLUMN C SHOULD HAVE 'Contact 2' AS THE NAME IN C1 WITHOUT QUOTES***
'***COLUMN D SHOULD HAVE 'Contact 3' AS THE NAME IN D1 WITHOUT QUOTES***
'***COLUMN E SHOULD HAVE 'Contact 4' AS THE NAME IN E1 WITHOUT QUOTES***
'***COLUMN F SHOULD HAVE 'Contact 5' AS THE NAME IN F1 WITHOUT QUOTES***
'Excel specific variables
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
'Outlook specific variables
Dim CurrentFolder As Outlook.Folder
Dim oDL As Outlook.DistListItem
Dim objDLFromFile As Outlook.DistListItem
Dim oMail As Outlook.MailItem
'Other variables
Dim strFile As String
Dim strDLName As String
Dim strDLFromFile As String
'Sets the file path to the Nested Contact Groups file
'***WHAT'S IN QUOTES MUST BE UPDATED WITH THE FILE PATH FOR THE GIVEN COMPUTER***
strFile = ("FILE PATH GOES HERE")
'Sets xlWorkbook to the workbook at the path above
Set xlWorkbook = Workbooks.Open(strFile, False, True)
'Sets and activates the worksheet, which should be called Sheet1
Set xlWorksheet = xlWorkbook.Worksheets("Sheet1")
xlWorkbook.Activate
'Sets whatever folder you're currently in to CurrentFolder
Set CurrentFolder = Outlook.ActiveExplorer.CurrentFolder
Set oMail = Outlook.CreateItem(olMailItem)
'Idiot proofs
If CurrentFolder.DefaultItemType <> olContactItem Then
MsgBox "Please make your selection in a Contacts folder.", vbCritical, "Add Contacts to Contact Group"
Exit Sub
End If
'Pulls the DL name from the worksheet, creates it and names it
strDLName = xlWorksheet.Range("A2")
strDLFromFile = xlWorksheet.Range("B2")
Set objDLFromFile = CurrentFolder.Items(strDLFromFile)
oMail.Recipients.Add objDLFromFile.DLName
If oMail.Recipients.ResolveAll Then
Set oDL = Outlook.CreateItem(olDistributionListItem)
oDL.DLName = strDLName
oDL.AddMembers oMail.Recipients
oDL.Save
End If
'It creates the DL, names it correctly and saves it. That's it so far