CODEWITHSUNDEEP
vbaapioutlookauthorizationmicrosoft-graph-api
Romain
Romain

Reputation: 81

How to call Microsoft Graph API using VBA?

Question

Is it possible to call Microsoft Graph API using VBA code?

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

What I tried

After discovering Microsoft Graph, I tried this API in Graph Explorer https://graph.microsoft.com/v1.0/planner/tasks

I was able to create a task in planner!

Consequently, in my mind, it was possible to call this API from VBA code executed directly in Outlook.

I created this 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)

I have an Authorization error with

error code 401

UPDATE on 12-03-2020 : Solution found to get a Graph Api token analysing URL when calling Graph Explorer (works perfectly for me) :

Function GetToken()

    Dim xml As New MSXML2.XMLHTTP60
    Dim doc As MSHTML.HTMLDocument
    Dim urltoken As String

'copy paste the URL you see when calling Microsoft Graph Explorer and add prompt + domain_hint parameters
    urltoken = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize?response_mode=form_post&nonce=graph_explorer&mkt=fr-FR&client_id={clientid}&response_type=token&scope=openid profile User.ReadWrite User.ReadBasic.All Sites.ReadWrite.All Contacts.ReadWrite People.Read Notes.ReadWrite.All Tasks.ReadWrite Mail.ReadWrite Files.ReadWrite.All Calendars.ReadWrite&prompt=none&domain_hint=organizations"

    xml.Open "GET", urltoken, False

    xml.Send

    If xml.readyState = 4 And xml.Status = 200 Then
        Set doc = New MSHTML.HTMLDocument
        doc.Body.innerHTML = xml.responseText

        GetToken = doc.getElementsByName("access_token")(0).Value

        sSuccess = True
    Else
         MsgBox "Error" & vbNewLine & "Ready state: " & xml.readyState & _
         vbNewLine & "HTTP request status: " & xml.Status
         sSuccess = False
    End If

    Set xml = Nothing

End Function

So using VBA for calling Graph API is possible :)

Upvotes: 8

Views: 12558

Answers (1)

Spenser Taylor Schafer
Spenser Taylor Schafer

Reputation: 53

So the code you show is only partially correct. Here is what I found to actually work. (This is with what you provided as I actually found a Json parser to work with the data better than the innerHTML methods, I also had to use a different version of MSXML since the one you reference wasnt working for me.)

Function GetToken()
    Dim xml As New MSXML2.XMLHTTP60
    Dim doc As MSHTML.HTMLDocument
    Dim urltoken As String

    'copy paste the URL you see when calling Microsoft Graph Explorer and add prompt + domain_hint parameters
    urltoken = "https://login.microsoftonline.com/{tenent id}/oauth2/v2.0/token"

    xml.Open "POST", urltoken, False

    xml.Send("client_id={clientid}&scope=https://graph.microsoft.com/.default&grant_type=client_credentials&client_secret=(cleint secret}")

    If xml.readyState = 4 And xml.Status = 200 Then
        Set doc = New MSHTML.HTMLDocument
        doc.Body.innerHTML = xml.responseText

        GetToken = doc.getElementsByName("access_token")(0).Value

        sSuccess = True
    Else
         MsgBox "Error" & vbNewLine & "Ready state: " & xml.readyState & _
         vbNewLine & "HTTP request status: " & xml.Status
         sSuccess = False
    End If

    Set xml = Nothing
End Function

Upvotes: 2

Related Questions