This is a variation of the macro produced for question ID 27974272.
In that macro (see below), you enter several distribution lists in a spreadsheet and the output on the spreadsheet page is Member Names with the distribution lists.
For this macro, the Outlook Object Library has to be chosen with VBA, Tools, References.
In this new macro, I would like to be able to enter Member Names and have their distribution lists with the original member names produced.
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Dim addrLists As AddressLists
Set addrLists = objNamespace.Session.AddressLists
Dim addrList As AddressList
For Each addrList In addrLists
If addrList.Name = "All Groups" Then
Set myAddrList = addrList
idxLine = 1
ActiveSheet.Range("A1") = "Chosen Dist List"
ActiveSheet.Range("C1") = "Member name"
ActiveSheet.Range("D1") = "Dist List"
For Each Entry In myAddrList.AddressEntries
If Entry.DisplayType = olDistList Then
For Each c In Range("Chosen_Dist_List")
If c.Value = Entry.Name Then
For Each Mem In Entry.Members
If Mem.DisplayType = olUser Or Mem.DisplayType = olRemoteUser Then
ActiveSheet.Range("C" & idxLine + 1) = Mem.Name
ActiveSheet.Range("D" & idxLine + 1) = Entry.Name
idxLine = idxLine + 1