Extracting an Outlook Distribution List using Excel

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

Sub Test()
    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
    Next

    GoTo ExitLabel
   
ErrorLabel:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume Next

ExitLabel:


End Sub


(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.
Michael AbendsternAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karl Timmermans (Outlook MVP)PrincipalCommented:
Set objMember = objList.GetMember(y)

Open in new window


This returns an AddressEntry object - not "Recipient" which would generate an error
Michael AbendsternAuthor Commented:
Thanks for your reply Karl.

I have tried ....

         Dim objMember As Object

and ....

         Dim objMember As Outlook.AddressEntry


and in both cases, whenever the code attempts to set objMember, the "Error 287 - Application-defined or object-defined error" is generated.

I'm at a complete loss to understand why.
Karl Timmermans (Outlook MVP)PrincipalCommented:
First, need to apologize for my previous response which was incorrect - was thinking of/looking at something else

The correct object type in the sample you provided is <Recipient>.

In terms of the code itself, ran a test on a DL using the code sample provided changing only the DL Name to conform to the one I was using. It ran without issues which points to a possible problem with DL itself.

#1 - Is the member of the DL an existing contact, a one-off member, GAL member?
#2 - When you open the DL in Outlook, can you open the individual members normally or does an error occur?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Michael AbendsternAuthor Commented:
Hi Karl - that's no problem. I appreciate the time you're taking to look into this.

In order to set up test data to use to test this code, I did the following in Outlook ....

1.   I created 5 new Contacts called "Test Name 1" thru' "Test Name 5".
2.   I created 2 new Contact Groups called "Outlook Test Group 1" and "Outlook Test Group 2"
3.   I opened "Outlook Test Group 1", clicked the "Add Members" button and added "Test Name 1" and "Test Name 2".
4.   I opened "Outlook Test Group 2", clicked the "Add Members" button and added "Test Name 3", "Test Name 4" and "Test Name 5".

That's it.

If I open one of the Contact Groups and double click one of the members, the details for the selected member open up in a new window correctly.

Given that there are 3 members in "OutlookTest Group 2" and the code correctly displays that there are 3 members, I'm absolutely happy that objList in the code has been set correctly.

The problem is, trying to get at the members within objList which is driving me nuts!
Karl Timmermans (Outlook MVP)PrincipalCommented:
The problem is, trying to get at the members within objList which is driving me nuts!

If the inidividual DL members are opening up correctly from within Outlook when you click on them from within the DL member dialog, don't know what to tell you.

This isn't going to help you in any way but as mentioned, ran the sample code as you provided and was able to cycle thru the member list without encountering any problem using both Office 2010 and 2016 against different DL's.

For lack of anything else to suggest, do you have a different machine to test this on to eliminate the environment as a possibility?
Michael AbendsternAuthor Commented:
Hi Karl,

I did as you suggested and tried my code on a different machine which was running Microsoft Office 2013. In Excel, I had Microsoft Office 15.0 and Microsoft Outlook 15.0 Object Libraries activated.

After creating a Test Group and adding email members (in exactly the same way as I did on the other PC), after pasting the VBA code in my original post, it worked as I expected - first time - so there's nothing wrong with my code!

All I can think of is that it's something to do with the older version of Outlook I'm using. It's bizarre as, according to Outlook help, the code is correct for the version of Office I'm using.

Ah well - you can't win 'em all.

Thanks.
Karl Timmermans (Outlook MVP)PrincipalCommented:
All I can think of is that it's something to do with the older version of Outlook I'm using

FWIW - your original post indicated that you were using Office/Outlook '2010 where the error was being triggered, I ran your code using '2010 without any issues so you might want to try the following on that machine

#1 - make sure the latest service pack is installed for Office/Outlook
#2 - repair your Office 2010 install
#3 - run ScanPST on the PST file where the DL's exist (assuming that to be the case)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael AbendsternAuthor Commented:
Thanks for your suggestions. I'll have to take it up with the appropriate people to see if this can be checked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.