Using Microsoft Graph in VBA

As a response to recent Microsoft policy changes, namely moving away from providing all traditional Office applications in different variants for desktop and online, I chose to explore how to access Office cloud data from an Access desktop application. The immediate need for this data stems from the increasing seemingly forced migration to their new web app for Outlook, see Karl Donaubauer's summary, New Outlook - The Situation in December 2024.

The Windows desktop versions of Office applications provided access to the COM object model and to the data in those Office applications. In web applications, we need an alternative method to get to Office data - APIs are the standard method for accessing cloud data. The Microsoft Graph API offers a single endpoint, https://graph.microsoft.com, to provide this access.

What steps are necessary?

Using the Microsoft Graph API requires 3 steps:

1. Add your application to Microsoft Entra

2. Authenticate to get a security token

3. Make a call to the desired Graph API endpoint using the token

How do I know which resource to use?

As you can see in the diagram below, there are many types of objects available in the Microsoft Graph. For each of the objects below, there are multiple REST resources and methods available. For example, to retrieve email messages you can use GET /me/messages. To create a draft email message you can use POST /me/messages. And to send an email message you can use POST /me/sendMail. For a complete reference see Microsoft Graph REST API v1.0 endpoint reference.

The many points of data accessible through Microsoft Graph

Although this may look daunting, using the APIs themselves from VBA is fairly straightforward. Authenticating with Microsoft Graph is tricky, however. To start with, there are different ways for an application to interface with the Graph API. The main two are:

1. Delegated access - an app acts on behalf of a signed-in user

2. App-only access - an app acts with its own identity

From an application perspective, the above two ways differ slightly in the endpoints used. They also differ in the way they need to authenticate with the Graph to use those endpoints. App-only access is a bit more complicated to add to Microsoft Entra but is simpler from a UI perspective because the user does not have to authenticate each time a new token is requested.

App-only access allows actions to be taken on others in the company, for example, send an email about an order from the sales rep instead of from the person requesting the action from the application. This type of access can also allow the application to use shared mailboxes or folders.

Tools available for VBA developers

Even after you educate yourself on which endpoint and method to use, interfacing directly to Microsoft Graph API from VBA is not for the faint of heart! Fortunately, there are tools available for you to use. I published a VBA-MicrosoftGraph project on GitHub at https://github.com/mbarnesatbbs/VBA-MicrosoftGraph. This project uses Tim Hall's VBA-Web project as its base and provides a class and a module with functions to interface with the typical Microsoft Graph objects that you would want to use in an application. 5 forms are provided with examples of interfacing with the module functions.

These cover interfaces for:

1. List mail

2. Send mail

3. Create draft message

4. Create (calendar) event

5. List contacts

6. List folders

7. Create contact

The download also includes a PowerPoint slide deck which steps you through the details both of how to use the tool and more about Graph basics. You can make use of both as examples for your own application and if needed writing your own API interface functions.

Do be aware, however, that the Graph API currently has limitations. For example, interacting with email signatures are currently not something supported by the API. There are also some interactions that were COM specific, that are impossible with this approach. For example, when creating a draft email, you can no longer open the just created email in Outlook from Access. The user must manually go to their email application to open that draft for further editing and sending.

Want to see using Graph API from VBA in action?

Check out this Access User Group YouTube video Working with Microsoft Graph API to see an example of setup of delegated access and authentication and learn more about using these functions. Keep in mind that this recording is based on an older version of the tool. With the recent 2.0 Release you can now use the second method of access, App-only if more appropriate or desired. It provides a built-in ability for including multiple attachments, and a new form and function for listing emails.

We also still have hope that perhaps someday, the Access team will provide a means of natively connecting to Microsoft Graph and/or other native ways to interface with Microsoft Outlook data on the web to replace functionality like SendMail. Until then, this tool should make VBA development using Microsoft Graph a bit easier.