Import members of a DL located in the GAL in Outlook

Romain D

New member
Joined
Aug 3, 2022
Messages
2
Reaction score
0
Points
1
Excel Version(s)
365
Hello all,

I am trying to import in Excel the list of members of a distribution list that is located in the Global Address List.

I found a macro to run in Outlook that lists the members in the body of an email.

I am not able to "translate" this macro in excel so:
- I run it from excel
- the list of members shows in an excel spreadsheet.

here is the code for outlook that works fine for me in terms of data extract.:

Option Explicit

Sub GetDGMembers()

Dim olAL As AddressList
Dim olEntry As AddressEntry
Dim olMember As AddressEntry

Dim strName As String

Dim i As Long
Dim lMemberCount As Long

Dim objMail As MailItem

Set olAL = Session.AddressLists("Global Address List")

Set objMail = CreateItem(olMailItem)

' enter the list name
Set olEntry = olAL.AddressEntries("
DL NAME")

' get count of dist list members
lMemberCount = olEntry.Members.Count

' loop through dist list and extract members
For i = 1 To lMemberCount
Set olMember = olEntry.Members.Item(i)
strName = olMember.Name

objMail.Body = objMail.Body & strName & vbCrLf
Next

objMail.Display

End Sub

Many thanks!

Romain
 

Romain D

New member
Joined
Aug 3, 2022
Messages
2
Reaction score
0
Points
1
Excel Version(s)
365
Apologies, i forgot to say that i did some research on this forum and others and could only find a macro that give the email address of the DL itself and this DL had to be in the "Default Contact List". This is not working for me.
 
Top