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!
 

Don’t Rely On Microsoft For Anything

I decided that waiting on Microsoft was not a smart business decision and that a solution was needed.

Assuming, and this is a big assumption, your e-mail is a Microsoft hosted e-mail, then I knew there existed a REST API for interacting with it.  So I set off to explore what could be done to utilize it via VBA.

REST APIs are a common thing to consume in the world of web development, but in MS Office it is a subject with little to no information.  Well that begins to change today.

In the simplest of terms, instead of using built-in VBA ‘commands’ (methods, properties, …) we now have to continuously send Requests to a server, Microsoft’s in this case.  Then, we get a response back and use it to ‘do things’ and react.

In this demo, I simply concentrated on sending an e-mail, but you can get into doing much more, much more: search & read mailbox content, work with calendars & contacts, notes, …

Thus, this demo sets up all the framework and you can then take it and build new requests in it to use any other API requests you wish to extend things even further.
 

Getting Setup – Application Account Configuration

Before being able to do any actual coding and interacting with things via the REST API, we must first create and entry for the application and configure things:

• Create credentials (Client ID)
• Setup the API permissions
• etc.

So the first step is to Register your app.
 

Getting Started – Authentication Overview

Since we are using a Web API, the first thing that is required is authentication, just like is required to log into any Microsoft website.

The basic idea is:

Authentication

1. Connect with you ‘Client Id’ and request a ‘Code’
2. Use the ‘Code’ to request a ‘Token’ which we than store

Do Work

1. Use the Token to make our ‘Requests’ (in this demo, send e-mails)

Now, for our ‘Requests’, we are able to simply employ the MSXML library to make XMLHTTP requests and this can all be done via pure VBA, but the initial authentication requires user interaction and as such is done via the Web Browser control in a form.

Note: For authentication, we are still able to do so using the Web Browser control and do not require use of the Modern Web Browser control!
 

Making ‘Requests’

To make any API Request we need a base URL, the Method to be used, the Content-Type for the call and our actual request being made.  So for sending an e-mail we’d need information like:

A URL:

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

The Method:

POST

The Content-Type:

application/json

The Request:

{
  "message": {
    "subject": "My Subject",
    "body": {
      "contentType": "HTML",
      "content": "My E-mail Message."
    },
    "toRecipients": [
      {
        "emailAddress": {
          "address": "someone@somewhere.com"
        }
      }
    ]
  }
}

So to be able to make such Requests and interact with the Microsoft REST API, as mentioned earlier, we need to use some means to communicate with Microsoft’s server, such as the one presented below:

Public Sub HTTP_SendRequest(sURL As String, _
                            Optional sMethod As String = "POST", _
                            Optional sContentType As String = "text/plain", _
                            Optional sHTTPRequest As String, _
                            Optional bAuthenticate As Boolean = False)
    Dim oHTTP                 As MSXML2.XMLHTTP60

    Set oHTTP = New MSXML2.XMLHTTP60
    With oHTTP
        'Make the Request
        Call .Open(sMethod, sURL)
        Call .setRequestHeader("Content-Type", sContentType)
        If bAuthenticate Then
            Call .setRequestHeader("Authorization", _
                                   OAuth2.token_type & " " & _
                                   OAuth2.access_token)
        End If
        Call .send(sHTTPRequest)
        'Process the returned Response
        lHTTP_Status = .Status
        sHTTP_StatusText = .StatusText
        sHTTP_ResponseText = .responseText
    End With

    On Error Resume Next
    Set oHTTP = Nothing
End Sub

 

JSON

Working with REST APIs is normally going to involve working with JSON strings.

In the current demo, I have created a simple routine for extracting information, but it is far from complete. It works fine in this context, but may not suffice for other needs. So you may wish to look at implementing a module that specializes in JSON manipulation, such as:

Also, JSON requires escaping certain characters and as such I created another simple little routine to manage this:

'---------------------------------------------------------------------------------------
' Procedure : JS_JSON_EscapeString
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Escape certain characters that JSON treats as special otherwise!
' 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: Late Binding  -> None required
'             Early Binding -> Microsoft Script Control 1.0
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput    : String to escape
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-04-25              Initial Release
'---------------------------------------------------------------------------------------
Public Function JS_JSON_EscapeString(ByVal sInput As Variant) As String
    On Error GoTo Error_Handler
    #Const ScriptControl_EarlyBind = False    'Should normally be in the Module header
    #If ScriptControl_EarlyBind = True Then
        Dim oSC               As MSScriptControl.ScriptControl

        Set oSC = New ScriptControl
    #Else
        Static oSC            As Object

        Set oSC = CreateObject("ScriptControl")
    #End If
    Dim sOutput

    If Not oSC Is Nothing Then
        With oSC
            .Language = "JScript"
            .AddCode "function regEx_Pattern_Test(str) {" & _
                     "  return str" & _
                     "    .replace(/[\\]/g, '\\\\')" & _
                     "    .replace(/[\/]/g, '\\/')" & _
                     "    .replace(/[\b]/g, '\\b')" & _
                     "    .replace(/[\f]/g, '\\f')" & _
                     "    .replace(/[\n]/g, '\\n')" & _
                     "    .replace(/[\r]/g, '\\r')" & _
                     "    .replace(/[\t]/g, '\\t')" & _
                     "    .replace(/[\""]/g, '\\\""');" & _
                     "};"
            sOutput = .Run("regEx_Pattern_Test", sInput)
        End With
    End If
    JS_JSON_EscapeString = sOutput

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

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: JS_JSON_EscapeString" & 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

 

Problems Emerge

Trying to prepare a YouTube video I have encountered a number of issues with Microsoft’s implementation.

I originally used my E5 account to do my initial implementation and it worked fine.

For the video, I wanted to try doing the same but using a personal Outlook Account. I was surprised to find that the authentication would not work using the standard web browser control. Yet it works fine for the E5 account?!

So I decided to move over to my VM running Office 365 to test using the Modern Web Browser control only to find that Access automatically authenticates via the currently logged in user and there is no means to use a different account as I could do on Access 2013! Thus, leaving me stranded!!! Why in God’s name would Microsoft bind all authentication in this manner?

Yet, using Google, I have none of these issues. I can understand defaulting to the logged in account, but not offering to use another account is plain DUMB!

Since the option to use alternate accounts is available in Access 2013 using the ‘original’ Web Browser control, I’m assuming it is yet one more bug with the Modern Web Browser control.

All this to say, that it seems that if you are using MS365 you will be bound to the MS365 Account for authentication and there is no way to switch the Graph REST API to use an alternate account. This could be a major deal breaker for some.
 

My Impressions

Well, I started by developing this method for Gmail and then decided to do the same for Microsoft Outlook because of the changes being forced upon us by the ‘New Outlook’. Although VERY similar, I found Google’s REST API and coding to be slightly easier and more complete.

Google offers the ability to check a token, to revoke a token and I have been unable to find this in Microsoft’s documentation.

I also found Google’s initial account setup much easier than Microsoft’s! Per the usual, with Microsoft it requires all sorts of setup/configuration to make it work. For instance, I selected the APIs I wanted my App to have access to, but it still wouldn’t work. I eventually discovered I then need to add Administrator Consent to the APIs. Isn’t that what I did by explicitly selecting them in the first place?! Several things like that which confuse and slow down progress and make what should have taken a day, take 2 or 3! Okay, I won’t lie, it took longer than that, even with a fully functional Gmail version to work off of.

Many examples are lacking and only provided in select languages making extrapolation to HTTP/VBA a bit of a guessing game at times.
 

Demo Database

Feel free to download a 100% unlocked copy of a sample database I have put together to illustrate some of what is discussed above by using the link provided below:

Download “Microsoft REST API Demo” Microsoft-REST-API.zip – Downloaded 5382 times – 101.45 KB To make it work, after setting up the application in the Microsoft Entra Admin Center, copy your Client ID and insert it into the variable for that purpose in the mod_Microsoft standard module. This setup can obviously be changed to pull the value from wherever you like (table, variable, registry, text file, database property, …), but for simplicity this was the setup I put in place.
 

Notice About Content/Downloads/Demos

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

 

Further Resources To Explore

 

Page History

Date	Summary of Changes
2024-04-29	Initial Release