Searching Calendar Events With The Microsoft Graph API

Working off of my existing demo and previous posts:

How-to Send E-mails Via Outlook Using The Microsoft Graph API
Well, unless you’ve been living under a rock, by now, you are aware of some fundamental changes coming to Outlook in the not too distant future, mainly the fact that it will cease to support any form of VBA! Now, many have tried to make Microsoft reverse course, understand the millions of solutions (Access, Word, Excel, …) that will instantaneously break with such a switch, but there has been no change in course.  Actually, per the usual, there has been little to no response whatsoever. This brings us to today’s post!  
Use The Microsoft Graph REST API To Create An Event In The User's Calendar
So after my recent YouTube video on sending an e-mail via an Outlook.com account using the Microsoft Graph REST API: I thought I’d provide a function which can be used to create an event in the user’s calendar. (it is still a work in progress though)

 

I wanted to share 2 routines I’ve created for searching/listing events in a calendar.

Events Between 2 Dates

My first curiosity was to be able to retrieve a listing of events for a day, week, …

It didn’t take long for me to discover Microsoft had planned for this need with a URL just for that need

https://graph.microsoft.com/v1.0/me/calendar/calendarView

With that we needed only to apply the necessary filter and it resulted in:

'---------------------------------------------------------------------------------------
' Procedure : Outlook_Calendar_ListEvents
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Find events between 2 date/times
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' dtStart
' dtEnd
'
' Usage:
' ~~~~~~
' ? Outlook_Calendar_ListEvents(#2023-05-28#, #2023-05-28#)
'
' ? Outlook_Calendar_ListEvents(#2024-05-28 8:00:00#, #2024-05-31 11:59:59#)
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-05-30
'---------------------------------------------------------------------------------------
Public Function Outlook_Calendar_ListEvents(ByVal dtStart As Date, _
                                            ByVal dtEnd As Date)
    On Error GoTo Error_Handler
    Dim sContentType          As String
    Dim sURL                  As String
    Dim Parsed                As Dictionary
    Dim Values                As Variant
    Dim Value                 As Dictionary
    Dim varKey                As Variant
    Dim i                     As Long
    Dim sFilter               As String

    If OAuth2.access_token = "" Then OAuth2_StoredCredentials_Load    'Make sure we have credential before proceeding
    If OAuth2_CheckToken = False Then DoCmd.OpenForm sAuthenticationForm, , , , , acDialog    'Force authentication if req'd
    sContentType = "application/json"
    sURL = "https://graph.microsoft.com/v1.0/me/calendar/calendarView"    '?startDateTime={start_datetime}&endDateTime={end_datetime}

    If sFilter <> "" Then sFilter = sFilter & " and "
    sURL = sURL & "?startDateTime="
    sURL = sURL & Format(SC_GetUTCComponent(dtStart), "yyyy-mm-ddThh:nn:ssZ")    '"2024-04-28T23:02:33.922Z"
    sURL = sURL & "&endDateTime="
    sURL = sURL & Format(SC_GetUTCComponent(dtEnd), "yyyy-mm-ddThh:nn:ssZ")    '"2024-06-04T23:02:33.922Z"

    'Debug.Print sURL
    'Call HTTP_SendRequest(sURL, "GET", sContentType, , True)
    Call HTTPServer_SendRequest(sURL, "GET", sContentType, , True)
    'Call WinHttp_SendRequest(sURL, "GET", sContentType, , True)
    If lHTTP_Status = 200 Then
        Set Parsed = JsonConverter.ParseJson(sHTTP_ResponseText)
        If Parsed("value").Count = 0 Then
            MsgBox "No matching events found."
            GoTo Error_Handler_Exit
        End If
        ReDim Values(Parsed("value").Count, Parsed("value")(1).Count)
        For Each Value In Parsed("value")
            i = i + 1
            Debug.Print "Event " & i, Parsed("value")(i)("subject"), ParseIso(Parsed("value")(i)("start")("dateTime")), ParseIso(Parsed("value")(i)("end")("dateTime")), Parsed("value")(i)("id")
        Next Value
        'Debug.Print sHTTP_ResponseText
    Else
        Debug.Print lHTTP_Status
        Debug.Print "--------"
        Debug.Print sHTTP_ResponseText
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set Parsed = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Outlook_Calendar_ListEvents" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Calling by doing something like:

? Outlook_Calendar_ListEvents(#2024-05-28 8:00:00#, #2024-05-31 23:59:59#)

will result in an output in the immediate window like:

Event 1       Test 01       5/29/2024 8:00:00 AM        5/29/2024 8:30:00 AM        AQMkADhmYTcwNWQ3LTVmYjYtNGZiYS1iMTA4LTYzNWRmOGY5YmZkZgBGAAADfxvXFvzp9UCXYhIfD_cZSAcALSyIhh6NNE2r2i-iLd5VAwAAAgEOAAAALSyIhh6NNE2r2i-iLd57VAwAJKpvtgAAAA==
Event 2       Test 02       5/31/2024 10:00:00 AM       5/31/2024 12:00:00 PM       AQMkADhmYTcwNWQ3LTVmYjYtNGZiYS1iMTA4LTYzNWRmOGY5YmZkZgBGAAADfxvXFvzp9UCXYhIfD_cZSAcALSyIhh6NNE2r2i-iLd5VAwAAAgEOAAAALSyIhh6NNE2r2i-iLd57VAwAJKpvtQAAAA==

Searching Beyond Just Dates

But what if I wanted to search for something else that just the date/time? What if I wanted to search the Subjects for a Term and retrieve those events?

Sadly, the above does not provide for that, but we do still have options! We can revert back to the default events URL:

https://graph.microsoft.com/v1.0/me/events

and apply a filter on it. Thus, resulting in a procedure like:

'---------------------------------------------------------------------------------------
' Procedure : Outlook_Calendar_FindAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve event(s) matching the search criteria
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSubject          :
' dtStart           :
' dtEnd             :
' sReturnedColumns  :
'
' Usage:
' ~~~~~~
' ? Outlook_Calendar_FindAll( , #5/31/2024 10:00:00 AM#, #5/31/2024 12:00:00 PM#)
'
' ? Outlook_Calendar_FindAll("Test")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-05-30
'---------------------------------------------------------------------------------------
Public Function Outlook_Calendar_FindAll(Optional ByVal sSubject As String, _
                                         Optional ByVal dtStart As Date, _
                                         Optional ByVal dtEnd As Date, _
                                         Optional sReturnedColumns As String)
    '$search is not supported!
    On Error GoTo Error_Handler
    Dim sContentType          As String
    Dim sURL                  As String
    Dim Parsed                As Dictionary
    Dim Values                As Variant
    Dim Value                 As Dictionary
    Dim varKey                As Variant
    Dim i                     As Long
    Dim sFilter               As String

    If OAuth2.access_token = "" Then OAuth2_StoredCredentials_Load    'Make sure we have credential before proceeding
    If OAuth2_CheckToken = False Then DoCmd.OpenForm sAuthenticationForm, , , , , acDialog    'Force authentication if req'd
    sContentType = "application/json"
    sURL = "https://graph.microsoft.com/v1.0/me/events"

    If sSubject <> "" Then
        If sFilter <> "" Then sFilter = sFilter & " and "
        'sFilter = sFilter & "subject eq '" & sSubject & "'" 'Exact match
        sFilter = sFilter & "contains(subject,'" & sSubject & "')" 'includes the specified term
    End If
'    '*********************** NOT SUPPORTED!!! *******************************
'    If sBody <> "" Then
'        If sFilter <> "" Then sFilter = sFilter & " and "
'        sFilter = sFilter & "contains(body/content,'" & sBody & "')"
'    End If
    If dtStart <> #12:00:00 AM# Then
        If sFilter <> "" Then sFilter = sFilter & " and "
        sFilter = sFilter & "start/dateTime ge '" & Format(SC_GetUTCComponent(dtStart), "yyyy-mm-ddThh:nn:ssZ") & "'"
    End If
    If dtEnd <> #12:00:00 AM# Then
        If sFilter <> "" Then sFilter = sFilter & " and "
        sFilter = sFilter & "end/dateTime le '" & Format(SC_GetUTCComponent(dtEnd), "yyyy-mm-ddThh:nn:ssZ") & "'"
    End If
    sURL = sURL & "?$filter=" & sFilter
    If sReturnedColumns = "" Then
        sURL = sURL & "&$select=id,subject,start,end"
    Else
        sURL = sURL & "&$select=" & sReturnedColumns
    End If

    'Debug.Print sURL
    'Call HTTP_SendRequest(sURL, "GET", sContentType, , True)
    Call HTTPServer_SendRequest(sURL, "GET", sContentType, , True)
    'Call WinHttp_SendRequest(sURL, "GET", sContentType, , True)
    If lHTTP_Status = 200 Then
        Set Parsed = JsonConverter.ParseJson(sHTTP_ResponseText)
        If Parsed("value").Count = 0 Then
            MsgBox "No matching events found."
            GoTo Error_Handler_Exit
        End If
        ReDim Values(Parsed("value").Count, Parsed("value")(1).Count)
        For Each Value In Parsed("value")
            i = i + 1
            Debug.Print "Event " & i, Parsed("value")(i)("subject"), ParseIso(Parsed("value")(i)("start")("dateTime")), ParseIso(Parsed("value")(i)("end")("dateTime")), Parsed("value")(i)("id")
        Next Value
        'Debug.Print sHTTP_ResponseText
    Else
        Debug.Print lHTTP_Status
        Debug.Print "--------"
        Debug.Print sHTTP_ResponseText
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set Parsed = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Outlook_Calendar_FindAll" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

With the above, you can:

  • Search for events within a date/time range
  • Search for events with a term in the Subject
  • Both

I also wanted to provide the means to search for terms in the event body, but sadly this is not currently possible with the Graph API at this time.

This procedure can easily be further expanded to permit searching other columns (location, attendees, …) something I will explore further, time permitting, but I did want to share with you the basic principle.