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
 
.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
 
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.
 
i'll test it.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
G Question marks in messages Using Outlook 2
e_a_g_l_e_p_i Question about calendar Using Outlook 5
e_a_g_l_e_p_i Question about installing my Gmail account on my iPhone but still getting messages downloaded to my desktop Outlook. Using Outlook 3
e_a_g_l_e_p_i Question about reinstalling Outlook 2021 Using Outlook 5
e_a_g_l_e_p_i question about 2-Step Verification with my gmail Using Outlook 0
D Question on removing an alias Using Outlook 1
O Newbie question: how to sync two Outlook -Exchange and IMAP- calendars? Using Outlook 4
D a general question re how backup programs handle pst files ... I have no problems, just curious Using Outlook 1
e_a_g_l_e_p_i A few question before I decide to switch to Pop from imap Using Outlook 9
D.Moore SendAndReceive question Outlook VBA and Custom Forms 2
J Transport Rule to detect Keyword question.. Exchange Server Administration 2
N Question Using Outlook 8
D Shared Mailbox question Exchange Server Administration 1
CWM030 Another Quarantine question Exchange Server Administration 0
E Outlook 2010 Can somebody tell me , the question associated with OST format Using Outlook 1
CWM030 A quick question for Diane about Exchange Exchange Server Administration 2
CWM030 Email Catagorties question Using Outlook 1
MahdeeyaAbdulla Friend's outlook question Using Outlook 1
e_a_g_l_e_p_i Question about address book in Outlook 2010 Using Outlook 9
e_a_g_l_e_p_i A question about installing office 2013 Pro and using my .pst from office 2010 Using Outlook 12
rerun101 Question about message options Outlook VBA and Custom Forms 4
M Question: Is there a rule that will save email in Windows Explorer Outlook VBA and Custom Forms 3
M Question on address book Using Outlook 1
e_a_g_l_e_p_i question about saving my .pst so I can import it to my Outlook after I build a new system Using Outlook 10
V Question on pop email Using Outlook 2
D a general question regarding data files Using Outlook 3
R Outlook 2007 - Email Question - POP3 to IMAP Outlook VBA and Custom Forms 11
D Advanced Search Question Using Outlook 1
adaminaus Quick question if i may Using Outlook 4
T Business Projects question BCM (Business Contact Manager) 0
V question about personal and shared calendars Exchange Server Administration 1
A Basic BCM question about sync to Outlook 2013 BCM (Business Contact Manager) 1
Jeff Rott Diane Question on "Use in a Run a Script Rule" Outlook VBA and Custom Forms 1
C Custom Forms: Question about retaining form information throughout the entire conversation Outlook VBA and Custom Forms 2
D OST question Exchange Server Administration 5
Mr Mayor Another recurring meeting question Using Outlook 1
V iCloud question Using Outlook 3
J "Specific word in body" question Using Outlook 1
A Newb-ish question Using Outlook 2
A The hardest VBA/Outlook question yet Using Outlook 1
S DAG question Exchange Server Administration 0
P Outlook File Extension Question Using Outlook 3
J Outlook 2010 Calendaring Question Using Outlook 0
K Syncing outlook with iCloud question Using Outlook 5
T Outlook Prf/Automation question Using Outlook 2
T Settings question Using Outlook 6
D Question re: Grouping by Due Date vs. Sorting by Due Date Using Outlook 1
IGWright Outlook 2013 .pst Question Using Outlook 10
F To Do Bar question Using Outlook 1
T I recently changed my pop3 email to an imap - question on folders Using Outlook 3

Similar threads

Back
Top