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

Status
Not open for further replies.

Mike Bannen

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
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.
 
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
 
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...
 
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
 
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 :)
 
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
 
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?
 
I think you need to code for GAL and code for Outlook Contact's DL.
 
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.
 
Global Address List. It's used with Exchange server and usually contains everyone that is in your organization.
 
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!
 
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.
 
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!
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
M Question about nested distribution lists Outlook VBA and Custom Forms 3
Dan_W Nested email attachments Outlook VBA and Custom Forms 4
E Duplicate, nested account folders on ATT server Using Outlook 10
A From a Nested Contact Folder Structure to a Categorised Contact Structure Using Outlook 2
B Deleting Nested Attachments Using Outlook 2
2 Nested or Hierarchical tasks in Outlook 2010? Using Outlook 1
M How can we find the list of users who are members of a deleted distribution list? Exchange Server Administration 2
Healy Consultants Macro to remove inside organization distribution list email address when reply to all recepients Outlook VBA and Custom Forms 0
M Auto expand Distribution List Before Sending Email Outlook VBA and Custom Forms 1
E Distribution lists Using Outlook 1
G Bcc help - Preventing multiple forwards from a bcc'd distribution group Using Outlook 1
A Are categories still recommended for creating local distribution lists? Using Outlook 3
Diane Poremsky Using Categories for Dynamic Distribution Lists Using Outlook 0
N Recurring invite sent w/distribution list adding/removing attendees Using Outlook 0
D Macro to scan email distribution list when replying Using Outlook 2
Diane Poremsky Find the Distribution Lists a Contact Belongs to Using Outlook 0
T Missing sub-contact folders (distribution lists) Using Outlook 3
J Maintain Distribution List Using Outlook 3
S Finding Distribution List Name Outlook VBA and Custom Forms 3
J Outlook 2013 - Exchange 2007 manage distribution group Exchange Server Administration 1
K Rule for Distribution List Using Outlook 0
M add contacts to shared contacts distribution list Using Outlook 1
M VBA script to allow mail merges of distribution groups? Using Outlook 7
E Add sender to distribution list? Using Outlook 3
B Restore deleted contact distribution list Using Outlook 1
L Convert Distribution List of Contacts to Single Contacts Using Outlook 6
C Office 2007 Distribution Lists Using Outlook 1
W Combine Mail Merge to Distribution List with Auto Attachments - Outlook 2007 Using Outlook 1
Jennifer Murphy Distribution list fails after conversion to Unicode PST Using Outlook 2
C Can't import distribution list - help, please Using Outlook 1
M Distribution List with hundreds of emails Using Outlook 1
L Individual E-mails from Distribution List Using Outlook 5
M Import distribution list into MS Exchange Sharepoint Exchange Server Administration 3
G Distribution Group Owners Cannot Add/Delete Members Using Outlook 1
G Distribution Group administration Using Outlook 1
A Why doesn't distribution lists update automatically Using Outlook 3
L Send E-mail with VBA code from [E-mail Distribution Group] if I have “Send as” Using Outlook 6
T Distribution List - Export/Import Using Outlook 4
K Creating a New Distribution List from an Existing one Using Outlook 0
C How do I hide distribution list from global contacts for certain users? Exchange Server Administration 1
A Lock the distribution list Using Outlook 1
L Sending from a distribution list email Using Outlook 6
K outlook 2007 Export/Import Distribution lists Using Outlook 1
J Automatically CC to another user when sending from Distribution List Using Outlook 2
K Autocomplete list not saving distribution list Using Outlook 3
N Personal Distribution Lists Using Outlook 0
T Unable to edit Distribution Group membership via Outlook (works via ECP). Exchange Server Administration 7
R How do I expand all distribution lists in the To: section? Using Outlook 4
K loop through distribution group (and potentially, embedded DGs) by Outlook VBA and Custom Forms 1
G Posting of Email Messages Using a Distribution List BCM (Business Contact Manager) 1

Similar threads

Back
Top