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
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