Friday, November 19, 2004

Gathering Exchange Distribution Lists Programmatically

Continuing in the vein of the previous post, I'm now moving on to distribution lists. Distribution Lists in MS Exchange are sort of like list-servs, but different. As far as I can tell, when you send to a DL, it appears to come from you, and there is no (correct me if I am wrong) clear indication that it was "amplified" through the DL. There are two types of DLs in Exchange, server-hosted and local. (I'm not sure if I am using Microsoft terminology here, but you will get the general idea.) Server-hosted DLs are stored on the server, with an "owner(s)" who can add and delete people, and require admin support to create. In addition, anyone on the Exchange server cluster can use the DL to send messages. On the other hand, local DLs are stored in the user's address book, and are not shared among all users.

That being said, how might one get the membership of a distribution list programmatically? (and, the better question, once it's gotten, what would you do with it?) Once more, Windows Scripting and Outlook come to the rescue:

Sub scanDL (dlName)
'First, create / get a reference to Outlook.
Set olApp = CreateObject("Outlook.Application")

'I need to get a namespace in Outlook. "MAPI" is the only
'supported namespace.
Set olNS = olApp.GetNamespace("MAPI")

'I create and try to resolve the user. The user.Resolve call is what
'fires off the Outlook Security stuff

Set user = olNS.CreateRecipient(dlName)

If Not user.resolved Then
WScript.Echo "# Didn't Resolve " & dlName
Exit Sub
End If

Set olMemberList = user.AddressEntry.Members

WScript.StdOut.Write dlName & "|"
WScript.StdOut.Write olMemberList.Count & "|"

For i = 1 To olMemberList.Count()
WScript.StdOut.Write olMemberList(i).Name & "|"


End Sub

Call it like scanDL "DL Mark List" and it will output the name of the DL, the number of members, and then each member name. DLs can have DLs as members; the task of expansion is left as an exercise to the reader.

There are a few caveats about using this code in a real world setting. First, this code will fail if the name of the DL is a strict substring of the name of another DL. For instance, if I have DL called "DL-Fuselage" and one called "DL-Fuselage Management", the call scanDL "DL-Fuselage" will fail. (In particular, the user.Resolve(dlName) call won't resolve a user, so it will exit through the If not user.resolved Then check) This can be fixed by prefixing the short DL name with an "=" (equals). This tells Outlook (or Exchange) to do an exact match, rather than a "best-effort" match. So, to get the members of the "DL-Fuselage" list, scanDL "=DL-Fuselage" will do the trick.

The next caveat(s) is that DL names are not as regular as you might hope. In my organization, there are a few DLs that have trailing spaces in their names (e.g., "DL-Fuselage Engineers ") For this situation, I don't use the leading "=" on the DL name, and user.Resolve(dlName) seems to work. I'm not sure what one can do in the situation where there is a trailing space and a sub-string. As well, there are sometimes DLs with characters that look like spaces, but aren't. I'm not sure what they are, but I ended up having to cut-and-paste them from an Outlook "To:" field to get the right characters.

Next entry: what one would actually do with this data...

Monday, November 15, 2004

Gathering Exchange Appointments Programatically

I'm starting to look at one more set of social network data: meeting invitations. One of the nice things about my organization is that there is a culture of open calendars. Generally speaking, calendars are not private unless there is a compelling reason. This is at the lower levels of the organization. I think this habit may be very different among the top executives. (And, just checking, it is -- there is nothing on the CEO's calendar, nor on the program manager's calendar)

In any case, the problem quickly became finding a way to programatically grab appointment information from Exchange. Luckily, I was at a gathering of a bunch of collaborative computing types, and especially, MSR types. So I asked, figuring this would be a no-brainer. As it turns out, it's a little harder than I expected. I was first cautioned by Gina to avoid MAPI ("with a ten foot pole"), and pushed towards WebDAV. THe only problem with WebDAV is that it appears that the local install of Exchange doesn't support WebDAV. Or I'm not smart enough to implement a client to be able to do it properly. Or I can't figure out what my Domain\Username is formally. (Mark.j.handel? hh292c? hh292ca?) Collaboration Data Objects (CDOs) don't work, since they have to run on the Exchange server. Which brings me back to MAPI. Of course, the Perl MAPI module doesn't do what I want it to do (it sends e-mail, and that's about it.)

Then, a sudden insight. You can use Visual Basic for Applications to script Outlook. (As the many Outlook viruses have shown.) In fact, it's pretty easy. Here's the code to do it:

Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")

Set user = olNS.CreateRecipient("Handel, Mark J")
Set fol = olNS.GetSharedDefaultFolder(user, 9)
Set itms = fol.Items

For i = 1 To itms.Count()
WScript.StdOut.write itms(i).Start & "|" & _
itms(i).End & "|" & _
itms(i).Subject & "|"

Set recips = itms(i).Recipients
For j = 1 to recips.Count()
WScript.StdOut.write recips(j).Name & ";" & _
recips(j).Type & "|"


(Of course, this code does not include error checking, nor does it dump the full range of appointment information available. But it's good enough for a short blog entry. If you want the full code, as well as "filters" to get it into neato or dot format, drop me an e-mail.)