Macro to pull members from distribution list.

[santhoshbes2729]

Hi All,

New to the Excel world. Need your help in providing me the excel macro to pull the member details from outlook distribution list. Can any one help me.

Thank You !!

Regards,
Santy

 

[santhoshbes2729]

It gives the Text "Contact Item" . Not My DL Name

 

[Domenic]

Can you post the exact code that you're using?

 

[santhoshbes2729]

Option Explicit
Sub PrintDistListDetails()

Dim olApplication As Object
Dim olNamespace As Object
Dim olContactFolder As Object
Dim olDistListItem As Object
Dim destWorksheet As Worksheet
Dim distListName As String
Dim memberCount As Long
Dim memberIndex As Long
Dim rowIndex As Long

Const olFolderContacts As Long = 10

distListName = "HCL-Brightstar-MIM" 'change the name accordingly

Set olApplication = CreateObject("Outlook.Application")
Set olNamespace = olApplication.GetNamespace("MAPI")
Set olContactFolder = olNamespace.GetDefaultFolder(olFolderContacts)
Set olDistListItem = olContactFolder.Items(distListName)

Set destWorksheet = Worksheets.Add
destWorksheet.Range("A1:B1").Value = Array("Name", "Address") 'column headers

memberCount = olDistListItem.memberCount

rowIndex = 2 'start the list at Row 2
For memberIndex = 1 To memberCount
With olDistListItem.GetMember(memberIndex)
destWorksheet.Cells(rowIndex, "a").Value = .Name
destWorksheet.Cells(rowIndex, "b").Value = .Address
End With
rowIndex = rowIndex + 1
Next memberIndex

destWorksheet.Columns.AutoFit

Set olApplication = Nothing
Set olNamespace = Nothing
Set olContactFolder = Nothing
Set olDistListItem = Nothing
Set destWorksheet = Nothing

End Sub

 

[Domenic]

The fact that it returned ContactItem means that "HCL-Brightstar-MIM" refers to a regular contact, not the name of your distribution list. Maybe the name of your distribution list is incorrect? Or, maybe you think you've created a distribution list, but you've actually created a contact?

 

[dhancy]

Is the distribution list located in the default contacts folder?

In my case, the distribution list resides in a global list (At least I think that's where it is. But it's not in my personal default contact list).

What changes do I need to make to read from a different folder? And how would I find out the name of the folder it is in?

Thank you.

Dennis