Avatar of 4eyesgirl
4eyesgirl asked on

Extract Meeting Attendance data of meeting rooms from Exchange 2003/2007

We have a need to analyze meeting utilization, by comparing the number of attendee to the room capacity.  Is there a way to pull this data programmically from Exchange for all meeting rooms?

ExchangeEmail ServersOutlook

Avatar of undefined
Last Comment
David Lee

8/22/2022 - Mon
David Lee

Hi, 4eyesgirl.

I might be able to do this with a bot of scripting.  Is that an option?  

Sure.  scripting is fine.
what type of scripting? vb script?

David Lee

VBA not VBScript.  There's a slight difference in syntax and capability, but the big difference is that VBScript runs from outside Outlook while VBA runs from inside.  Here's the code for doing this.  Follow these instrcutions to use it.

1.  Start Outlook.
2.  Click Tools > Macro > Visual Basic Editor.
3.  If not already expanded, expand Modules and click on Module1.
4.  Copy the code below and paste it into the right-hand pane of the VB Editor.
5.  Edit the code as needed.  I placed comment lines where things need to change.
6.  Click the diskette icon on the toolbar to save the changes.
7.  Close the VB Editor.
8.  Click Tools->Macro->Security.
9.  Change the Security Level setting to Medium.
10.  Run the macro.  It produces an HTML file listing all meetings, the date the meeting occurs on, and the sum of attendees who have accepted or tentatively accepted.
Sub CountAttendees()
    Dim olkItems As Object, olkAppt As Object, olkAttendee As Object, objFSO Object, objFile As Object, intAttendees As Integer
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Change the file name and path on the next line as desired.'
    Set objFile = objFSO.CreateTextFile("C:\eeTesting\Appointments.html")
    objFile.WriteLine "<table>"
    Set olkItems = Outlook.Application.Session.GetDefaultFolder(olFolderCalendar).Items
    olkItems.Sort "[Start]"
    olkItems.IncludeRecurrences = True
    'Change the dates/times on the next line as desired.'
    Set olkItems = olkItems.Restrict("[Start] >= '" & Format("1/1/2008 12:01am", "ddddd h:nn AMPM") & "' AND [Start] <= '" & Format("12/31/2008 11:59pm", "ddddd h:nn AMPM") & "'")
    For Each olkAppt In olkItems
        Debug.Print olkAppt.Subject
        If olkAppt.MeetingStatus = olMeeting Then
            intAttendees = 0
            For Each olkAttendee In olkAppt.Recipients
                Select Case olkAttendee.MeetingResponseStatus
                    Case olResponseAccepted, olResponseTentative
                        intAttendees = intAttendees + 1
                End Select
            objFile.WriteLine "<tr><td>" & olkAppt.Subject & "</td><td>" & olkAppt.Start & "</td><td>" & intAttendees & "</td></tr>"
        End If
    objFile.WriteLine "</table>"
    Set objFile = Nothing '
    Set objFSO = Nothing
    Set olkAttendee = Nothing
    Set olkAppt = Nothing
    Set olkItems = Nothing
    MsgBox "Done"
End Sub

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

This cool! However, it seems only pull meetings I am access to.  How do I extract all meetings which includes meetings I did not get a invite for?

David Lee

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question