(Solved) – How to use VBA code to call Microsoft Graph API?


I have discovered recently Microsoft Graph API and I am in a hurry to use it (I’ve got plenty of ideas to improve my productivity using O365).
But I am not sure to have understood everything, I am just a beginner in Advanced O365 functionnalities.

Usually, I use this forum to have all my answers but for this time, I have not found what I want and I don’t see a lot of topics speaking about what I want to do so maybe I have missed an important thing. Sorry if it is the case.

So my need in O365:
I would like from Outlook to create automatically some Microsoft Planner tasks.

Solution I have tried:
After discovering Microsoft Graph, I have tried in Graph Explorer this API

It works like a charm, I was able to create a task in planner !

Consequently, in my mind, it was possible to call this API from a VBA code executed directly in Outlook. And this is where I am completely lost !

I have created this simple macro in Outlook :

Sub TaskPlannerCreation()

    Dim PlannerService As New MSXML2.XMLHTTP60
    Dim sData As Variant

    sData = " { ""  ""planId"": ""K9Zv2QHm1U-GSAhd-PTGZfdFeOn"",""bucketId"": ""b6NVNiEIQkGZeBBzn7kWqJvAGvvs"",""title"": ""Outlook task"" } "

    With PlannerService
        .Open "POST", "https://graph.microsoft.com/v1.0/planner/tasks", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "User-Agent", "xx"
        .Send (sData)

After execution, I have an Authorization error with

error code 401

After spending hours on different forums to see how to handle O365 authorization, I have not found the solution and I have plenty of questions !

My first question is basic because I have not seen example codes using VBA : Is it possible to call Microsoft Graph API using VBA code ??? 🙂

If yes, how to handle O365 authorization in VBA code ? I have seen plenty of topics saying that we must create an application in Microsoft Azure to get a token but I am a bit surprise I must do that for a simple local use…

Leave a Reply

Your email address will not be published. Required fields are marked *