Question about nested distribution lists

[Moragtao]

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