The following function will create or append members (or existing distribution lists) to a named distribution List. You can call it as many times as you have entries in your worksheet.
Using the format "Name (e-mail address)" for individual members or just the name for the list.
CreateDistributionList "ListName", "Name (e-mail address)" 'member
CreateDistributionList "ListName", "ListToAppend" 'list
Private Sub CreateDistributionList(strListName As String, strMember As String)
'Graham Mayor - http://www.gmayor.com - 28/09/2016
'strMember should be in the format "Name (e-mail address)"
'or Distribution list name
Dim olNS As Outlook.NameSpace
Dim olFolder As Outlook.Folder
Dim olDistList As Outlook.DistListItem
Dim olFolderItems As Outlook.Items
Dim objRcpnt As Outlook.Recipient
Dim x As Integer
Dim y As Integer
Dim iCount As Integer
Dim bList As Boolean
Dim bMember As Boolean
Set olNS = GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderContacts)
Set olFolderItems = olFolder.Items
bList = False
bMember = False
iCount = olFolderItems.Count
For x = 1 To iCount
If TypeName(olFolderItems.Item(x)) = "DistListItem" Then
Set olDistList = olFolderItems.Item(x)
'Check if the distribution list exists
If olDistList.DLName = strListName Then
bList = True
For y = 1 To olDistList.MemberCount
'Check if the member exists
If InStr(1, olDistList.GetMember(y).Name, strMember) Then
bMember = True
Exit For
End If
Next y
End If
End If
Next x
'If the distribution list doesn't exist - add it
If Not bList Then
Set olDistList = CreateItem(olDistributionListItem)
olDistList.DLName = strListName
End If
'If the member doesn't exist add it
If Not bMember Then
Set objRcpnt = olNS.CreateRecipient(strMember)
objRcpnt.Resolve
olDistList.AddMember objRcpnt
End If
'Save the change to the list
olDistList.Save
lbl_Exit:
Set olNS = Nothing
Set olFolder = Nothing
Set olDistList = Nothing
Set olFolderItems = Nothing
Set objRcpnt = Nothing
Exit Sub
End Sub