[VBA] Query an Outlook Distribution List from Excel

[Sunjinsak]

Hi,

I’m looking for a way to query a specific Outlook distribution list from Excel to see if a member of staff is included on it or not.

I’ve searched quite extensively for a solution and I’ve found plenty of examples of how to extract all the members of a DL, or how to add people to a DL etc. but I’ve not found quite what I’m looking for.

I’ve tried to use some of the example code as a base and figure out what I want from there but I’m just not getting it.

Can anyone help?

I was planning on writing a function I could call whenever I need to check. Something along the lines of the following…

Function isMemberOfDL (sPersonToLookUp As String, sDLToQuery As String) As Boolean

                ‘code to query DL here?
                ‘isMemberOfDL = True/False based on result of above

End Function

It’s the querying the DL bit I can’t get. I know I’ll need to include a reference to Microsoft Outlook 14.0 Object Library.

I’m using Excel 2013 and Outlook 2010 on Windows 7 Pro.

Thanks in advance for any help or suggestions.

 

[Sunjinsak]

Just wanted to give this one cheeky bump as I've still not been able to figure out how to do this.

Also, for full disclosure, I've cross-posted over at VBA Express. I know cross-posting is frowned upon but hopefully you can forgive me this once as I desperately need a solution to this. If I get a reply over there I'll post it here too (and vice-versa of course) so anyone looking for a similar solution in the future can find the answer easily.

I've not been idle either. I've been experimenting with code examples and reading relevant documentation on MSDN but I just can't for the life of me get anything working.

If you think it would be helpful to see the code I've been trying to adapt then let me know as I'll happily post it. The only reason I've not done so yet is because I'm not sure how relevant it is and I don't want to confuse the issue - after all I've been attempting to adapt code that does something else entirely.

Anyway, thanks for reading and for any help or suggestions offered.