1. Here's a thread that needs an answer: Outlook Office 365 cached mode crashing
    Dismiss Notice

Question about nested distribution lists

Discussion in 'Outlook VBA and Custom Forms' started by Moragtao, Jun 14, 2017.

  1. Moragtao

    Moragtao

    New Member
    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 (Text):
    Copy Source
    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
     
  2. Diane Poremsky

    Diane Poremsky

    Senior Member
    .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
     
  3. Moragtao

    Moragtao

    New Member
    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.
     
  4. Diane Poremsky

    Diane Poremsky

    Senior Member
    i'll test it.
     
Loading...

Share This Page