Hi,
I'm trying to write a macro that would expand an outlook distribution list so I can see the names. Then copy the names in a one sheet.
Any help please?
Thanks,
Jiro
Hi,
I'm trying to write a macro that would expand an outlook distribution list so I can see the names. Then copy the names in a one sheet.
Any help please?
Thanks,
Jiro
Re: Expand Outlook Distribution List using Excel VBA
try this
Const olFolderContacts = 10
Sub DistList()
Dim objApp As Object, objNS As Object
Dim objFolder As Object, objDist As Object
Dim objAddrEntry As Object
Dim MyList As String
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets.Add
MyList = "mylist" ' change your list name here and it should be in your contacts
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objFolder = objNS.GetDefaultFolder(olFolderContacts)
On Error Resume Next
Set objDist = objFolder.Items(MyList)
On Error GoTo 0
If objDist Is Nothing Then
MsgBox "Distribution List doesn't exist", vbCritical
GoTo fastexit
End If
For i = 1 To objDist.MemberCount
Set objAddrEntry = objDist.GetMember(i).AddressEntry
Cells(i, 1) = objAddrEntry.Name
Cells(i, 2) = objAddrEntry.Address
Next
fastexit:
Set objFolder = Nothing
Set objApp = Nothing
End Sub
Display More
Re: Expand Outlook Distribution List using Excel VBA
Thanks ashu1990 - but it says "Distribution List doesn't exist" yet I'm sure that the Distribution list was typed correctly. Is there any other settings or variable value that I need to change, like GetDefaultFolder? I am using our Global Address list.
Don’t have an account yet? Register yourself now and be a part of our community!