I am trying to use Excel VBA to extract the members of a selected Distribution List from Outlook. I am using the following VBA code to test the data being obtained ....
Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.Namespace
Dim objFolder As Outlook.Folder
Dim objList As Outlook.DistListItem
Dim objMember As Outlook.Recipient
Dim y As Long
On Error GoTo ErrorLabel
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderContacts)
Set objList = objFolder.Items("Outlook Test Group 2")
MsgBox objList.DLName & " (" & objList.MemberCount & " members)"
For y = 1 To objList.MemberCount
Set objMember = objList.GetMember(y)
MsgBox "Name = " & objMember.Name & vbTab & "email = " & objMember.Address
MsgBox "Error " & Err.Number & " - " & Err.Description
(For information - I'm using Microsoft Office Professional Plus 2010. In Excel, I've activated the Microsoft Office 14.0 and Microsoft Outlook 14.0 Object Libraries.)
When the macro is run, I get a message box displaying "Outlook Test Group 2 (3 members)" - which is correct - so that suggests to me that the objList object has been set correctly.
However, when looping round the members of objList, I get "Error 287 - Application-defined or object-defined error" every time the macro attempts to set objMember.
I have tried using the code MsgBox objList.GetMember(y).Name - but that just generates the same error.
I have spent hours Googling for a solution and as far as I can see, the code I'm using is correct. If so, why am I getting this error and how do I resolve this problem?
Any help would be very much appreciated.