Search code examples
excelvbaoutlookcalendar

Determine if an Outlook event is a meeting and what the response was

I'm trying to write a macro in Excel that will look through my Outlook calendar, grab all the events, and display the following information: subject, start time, whether this event was from an meeting invite, and my response.

I've the first two piece of information down. How do I figure out the last two?

Dim olApp As Object
Dim olNS As Object
Dim olFolder As Object
Dim olApt As Object
Dim NextRow As Long
 
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(9) 'olFolderCalendar

Range("A1:C1").Value = Array("Subject", "Start", "Meeting?", "Response")
 
NextRow = 2

For Each olApt In olFolder.Items
    Cells(NextRow, "A").Value = olApt.Subject
    Cells(NextRow, "B").Value = Format(olApt.Start, "MM-dd-yyyy")
    Cells(NextRow, "C").Value = olApt.MeetingOrNot
    Cells(NextRow, "C").Value = olApt.MeetingResponse
    NextRow = NextRow + 1
Next olApt

Obviously MeetingOrNot and MeetingResponse are not valid attributes.

Solution

  • Use MeetingStatus property (olMeetingReceived, etc.) and ResponseStatus property (olResponseAccepted etc.).

    To see all properties exposed by the AppointmentItem object, see its documentation on MSDN or take a look at an appointment with OutlookSpy (I am its author) - select an appointment and click Item button to see the live AppointmentItem object. Or click IMessage button to see the Extended MAPI properties.