Nested distribution lists: how to count UNIQUE # of people...

[Mike Bannen]

Good evening!

I am new to Microsoft coding and to outlook in particular. I have 3 related challenges to solve.

For a given meeting (appointment?)
1) count the UNIQUE number of people invited (example...)
- required, 7
- optional, 2
- organizer, 1

2) count the UNIQUE # of people by meeting response status (example...)
- required, 4 accepted, 2 tentative, 1 declined
- optional, 1 declined
- organizer, 1

3) count the total # UNIQUE # of people by status
- 4 accepted, 2 tentative, 1 declined, 1 organizer

TWO challenges:
CHALLENGE 1) Nested distribution lists
- I have distribution lists inside of distribution lists (e.g., IT Managers is a distribution list instead of All Managers)
- I'm not sure how to to loop through these nested situations in VBA.

CHALLENGE 2) Nested distribution list and getting a truly UNIQUE count
- Often the organizer will be IN one of the distribution lists, or
- the organizer may include the same person multiple times on an invite, either by
a) adding the person individual, and also adding a distribution list of which that person is a member
b) adding themselves

It may be too much effort to handle challenge #2 (i.e., to get to a truly unique count), but it SEEMS like Challenge #1 should be very doable...I just don't know VBA well enough to know how.

Any advice or pointers are very much appreciated.

 

[Mike Bannen]

I found this snippet but it doesn't fully address the scenario, but maybe can help someone who knows what s/he's doing help me

Option Explicit

Public Sub ShowMemberCount()
Dim obj As Object
Dim DL As Outlook.DistListItem

If TypeOf Application.ActiveWindow Is Outlook.Explorer Then
If Application.ActiveExplorer.Selection.Count Then
Set obj = Application.ActiveExplorer.Selection(1)
End If
Else
Set obj = Application.ActiveInspector.CurrentItem
End If

If Not obj Is Nothing Then
If TypeOf obj Is Outlook.DistListItem Then
Set DL = obj
MsgBox "Name: " & DL.DLName & _
vbCrLf & "Member: " & DL.MemberCount, _
, "Member Count"
End If
End If
End Sub

 

[Forum Admin]

That's michael's code from http://www.vboffice.net/en/developers/show-distlist-member-count - I was going to suggest that as a starting point.

That will tell you a particular DL has 30 members. They *should* all be unique.

This will get the members of the DL:
http://www.slipstick.com/tutorial/create-individual-contacts-from-a-distribution-list/

This will create a report of the people who responded.
http://www.slipstick.com/developer/list-meeting-attendees-responses/

All you need is to cycle through recipient list and then get the DL members to get a list of who was invited...

 

[Forum Admin]

BTW, you can use Michael's code - use the member count to loop through each member:

For i = 1 To DL.MemberCount
MsgBox DL.GetMember(i)
Next i

 

[Mike Bannen]

Thanks a lot for your replies!
Quick question: Was thinking that the code above tells me the unique members of a single distribution list, but not the truly unique people on a meeting (e.g., invite includes two distribution list w/the same person on both).

To get the TRULY unique count, I was thinking I would have to
a) loop through all of the people on the meeting / appointment, including those in any and all distribution lists
b) get their meeting response status, if available (and have error handling when I status isn't available)
b) get their email addresses,
c) put them into into some type of collection, w/their 'type' (required, optional) and response status (none, tentative, etc.)
d) then run some kind of unique function against that collection

Only then could I get a truly unique count of people and of people and their response status.

Does that sound right?
I have no idea how to do it in VBA yet, but one thing at at time

 

[Mike Bannen]

Incidentally, I tested the code below. When *I* own the distribution list, Outlook appears to resolve to unique invitees. When a meeting has someone else's distribution list (e.g., from global address book), it only gets the name of the distribution list. Point is, it appears I have to code for both scenarios (distribution lists I own, and those I don't)

Sub GetAttendeeList()

Dim objApp As Outlook.Application
Dim objItem As Object
Dim objSelection As Selection
Dim objAttendees As Outlook.Recipients
Dim objAttendeeReq As String
Dim objAttendeeOpt As String
Dim objOrganizer As String
Dim dtStart As Date
Dim dtEnd As Date
Dim strSubject As String
Dim strLocation As String
Dim strNotes As String
Dim strMeetStatus As String
Dim strCopyData As String

On Error Resume Next

Set objApp = CreateObject("Outlook.Application")
Set objItem = objApp.ActiveInspector.CurrentItem
Set objSelection = objApp.ActiveExplorer.Selection
Set objAttendees = objItem.Recipients

On Error GoTo EndClean:

' check for user problems with none or too many items open'
Select Case objSelection.Count
Case 0
MsgBox "No meeting was opened. Please open the meeting to print."
GoTo EndClean:
Case Is > 1
MsgBox "Too many items. Just select one!"
GoTo EndClean:
End Select

' Is it an appointment'
If objItem.Class <> 26 Then
MsgBox "You need to open the meeting."
GoTo EndClean:
End If

' Get the data'
dtStart = objItem.Start
dtEnd = objItem.End
strSubject = objItem.Subject
strLocation = objItem.Location
strNotes = objItem.Body
objOrganizer = objItem.Organizer
objAttendeeReq = ""
objAttendeeOpt = ""

' Get The Attendee List'
For x = 1 To objAttendees.Count
strMeetStatus = ""
Select Case objAttendees(x).MeetingResponseStatus
Case 0
strMeetStatus = "No Response (or Organizer)"
Case 1
strMeetStatus = "Organizer"
Case 2
strMeetStatus = "Tentative"
Case 3
strMeetStatus = "Accepted"
Case 4
strMeetStatus = "Declined"
End Select

If objAttendees(x).Type = olRequired Then
objAttendeeReq = objAttendeeReq & objAttendees(x).Name & vbTab & strMeetStatus & vbCrLf
Else
objAttendeeOpt = objAttendeeOpt & objAttendees(x).Name & vbTab & strMeetStatus & vbCrLf
End If
Next

strCopyData = "Organizer: " & objOrganizer & vbCrLf & "Subject: " & strSubject & vbCrLf & _
"Location: " & strLocation & vbCrLf & "Start: " & dtStart & vbCrLf & "End: " & dtEnd & _
vbCrLf & vbCrLf & "Required: " & vbCrLf & objAttendeeReq & vbCrLf & "Optional: " & _
vbCrLf & objAttendeeOpt & vbCrLf & "NOTES " & vbCrLf & strNotes


Set ListAttendees = Application.CreateItem(olMailItem)
ListAttendees.Body = strCopyData
ListAttendees.Display

EndClean:
Set objApp = Nothing
Set objItem = Nothing
Set objSelection = Nothing
Set objAttendees = Nothing
End Sub

 

[Diane Poremsky]

To get the TRULY unique count, I was thinking I would have to
a) loop through all of the people on the meeting / appointment, including those in any and all distribution lists
b) get their meeting response status, if available (and have error handling when I status isn't available)
b) get their email addresses,
c) put them into into some type of collection, w/their 'type' (required, optional) and response status (none, tentative, etc.)
d) then run some kind of unique function against that collection

Only then could I get a truly unique count of people and of people and their response status.

Does that sound right?

That is correct. Do you need to know how many DLs a person was on or which dLs they were?

 

[Diane Poremsky]

I think you need to code for GAL and code for Outlook Contact's DL.

 

[Mike Bannen]

That is correct. Do you need to know how many DLs a person was on or which dLs they were?

Hi Diane -- Basically trying to figure out how many people are likely to come to a future meeting, or did come to a meeting (based on their response status). The distribution list their on doesn't matter.

 

[Mike Bannen]

I think you need to code for GAL and code for Outlook Contact's DL.

What's GAL? Thanks again!

 

[rst75]

Global Address List. It's used with Exchange server and usually contains everyone that is in your organization.

 

[Mike Bannen]

GAL. Got it! Thx again!

I'm kind of surprised I can't find any case online where someone else had the same requirement (i.e., to get a truly unique count of physical people planning to attend a meeting)

Also, I'm assuming that in all scenarios I will be able to programmatically get email addresses for all meeting invitees via VBA (or an add-in)? Are there any scenarios you know of where I wouldn't be able to, and then have to have error handling for it?

Also also, I'm struggling to find a really good book to learn Outlook-specific VBA. I have Sue M's book. It's usually, but doesn't really provide a coding reference. Any suggestions are appreciated!

 

[Diane Poremsky]

Programming: MSDN's Outlook developer files, plus general web searches for code. StackExchange plus outlook developer websites for code samples - VBOffice and Davide Lee's site have the most samples, as does Slipstick of course. Outlookcode has some good references but it is no longer being maintained (Sue retired and the new owner isn't keeping it up). That's not necessarily a problem, but it is lacking information on the newer versions of Outlook.

VBOffice.net samples
OutlookCode.com
SlovakTech.com
Outlook MVP David Lee

Yes, you should be able to get the addresses using VBA. I think the only situation that might give you problems is getting the addresses from the GAL, but worse case scenario, you need to use Redemption.

 

[Mike Bannen]

Thanks very much Diane! If you happen to know any very good Outlook developers in San Diego, I might have some (contract) work for them. All your feedback is appreciated!