Question about nested distribution lists

Status
Not open for further replies.

Moragtao

New Member
Outlook version
Outlook 2016 64 bit
Email Account
IMAP
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 just 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.


Code:
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 xlApp As Object
    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 = ("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
'Sets oMail
    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. It should add the other DLs, but it doesn't
    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
   
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
.Close olDiscardYou'll create a new message, add the names from the spreadsheet into the to field of the message, resolve the names and add them to the dl -

this snippet uses the address in a received message, so you'll need a little different code, but the rest is the same.

Set oRecipients = oMail.Recipients
With oDLGroup
.AddMembers oRecipients
' just save and close
'.Close olSave
oMail.Close olDiscard ' close the new message without saving

Send Email to Addresses in an Excel Workbook show how to send using addresses in excel.
Recipients.add is the recommended way to add the names, but you can try reading the columns into a semi-colon separated string and inserting them using

omail.to = strList

with recipients.add, you need to add a name one at a time, repeating for each column.

Set oMail= Application.CreateItem(olMailItem)
With oMail
' use a loop for the column too
.recipients.add xlWorksheet.Range("b" & rCount)
.recipients.Type = olTo

'after adding, do a resolve all
End With

Set oRecipients = oMail.Recipients
With oDLGroup
.AddMembers oRecipients
' just save and close
'.Close olSave
.display
end with
oMail.Close olDiscard ' close the new message without saving
 

Moragtao

New Member
Outlook version
Outlook 2016 64 bit
Email Account
IMAP
This is similar to what I was using before, and I unfortunately had no luck with it. If I'm adding just a standard contact it works like a charm, but if I'm trying to add a distlist to the distlist, it doesn't work. It'll create the distlist, but won't add the previous one to it.
 
Status
Not open for further replies.
Top