I've found the solution for my question... so I hope this helps someone.
Answering my own question, yes we need to send a Token to be able to use the API. The approach to get this token is to retrieve the authentication cookie (SPOIDCRL cookie).
To do that I followed a PowerShell example and converted to VBA code. https://blogs.technet.microsoft.com/sharepointdevelopersupport/2018/02/07/sharepoint-online-active-authentication/
I did some changes as the PowerShell script was very generic, and I was aiming to use only Integrated authorization, which means that I don't need to provide any user name or password.
The following link was very helpful to understand the process to get the cookie, but it uses the username and password to get the cookie. http://www.sharepoint-insight.com/2016/03/21/step-by-step-sharepoint-online-authentiaction-process-via-http-protocol/
Bellow there is a function that I created to get the cookie as string. Bear in mind that the code is not perfect as doesn't have any error handling and could be refactored. The code is not been render correctly due to the comments. However if you copy to a VBA editor you might see it correctly.
Private Function Authorization() As String
' Set the STS URL
stsURL = "https://[get this URL using the GetUserRealm.srf endpoint]/adfs/services/trust/2005/windowstransport"
' Generates a new GUID using a Module (You can use any code to generate a GUID from the internet)
myGUID = GenerateGUID
' Set the Realm
realm = "urn:federation:MicrosoftOnline"
' Set the STS XML for Integrated requests
xmlSTSIntegrated = "<?xml version='1.0' encoding='UTF-8'?><s:Envelope xmlns:s='http://www.w3.org/2003/05/soap-envelope' xmlns:a='http://www.w3.org/2005/08/addressing'>" & _
"<s:Header><a:Action s:mustUnderstand='1'>http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</a:Action><a:MessageID>urn:uuid:{0}</a:MessageID>" & _
"<a:ReplyTo><a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address></a:ReplyTo><a:To s:mustUnderstand='1'>{1}</a:To></s:Header>" & _
"<s:Body><t:RequestSecurityToken xmlns:t='http://schemas.xmlsoap.org/ws/2005/02/trust'><wsp:AppliesTo xmlns:wsp='http://schemas.xmlsoap.org/ws/2004/09/policy'>" & _
"<wsa:EndpointReference xmlns:wsa='http://www.w3.org/2005/08/addressing'><wsa:Address>{2}</wsa:Address></wsa:EndpointReference></wsp:AppliesTo>" & _
"<t:KeyType>http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey</t:KeyType><t:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue</t:RequestType>" & _
"</t:RequestSecurityToken></s:Body></s:Envelope>"
xmlSTSIntegrated = Replace(xmlSTSIntegrated, "{0}", myGUID) ' Replace the first spot with the GUID ID
xmlSTSIntegrated = Replace(xmlSTSIntegrated, "{1}", stsURL) ' Replace the second with the STS URL
xmlSTSIntegrated = Replace(xmlSTSIntegrated, "{2}", realm) ' Replace the third with the real
xmlSTSIntegrated = Replace(xmlSTSIntegrated, "'", """") ' Replace single quote for double quotes
Dim h As New WinHttpRequest ' Create a new WinHttpRequest Object
' Send a POST request to the STS URL with the XML in body
With h
.Open "POST", stsURL, False
'.setRequestHeader "UserAgent", "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; Touch; rv:11.0) like Gecko"
.setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8" ' Required! - as the XML is encapsulated in SOAP
.SetAutoLogonPolicy (AutoLogonPolicy_Always) ' Required! - If this is omitted it will return 401 error - Unauthorized
.Send (xmlSTSIntegrated) ' Send the xml
response = .responseText ' Get the response
End With
Dim xml As New MSXML2.DOMDocument60
xml.LoadXML (response) ' Convert the response as XML
' Send a GET request to the endpoint with headers
With h
.Open "GET", False
.setRequestHeader "Authorization", BinaryToken ' Set the Authorization header with the Binary Token
.setRequestHeader "X-IDCRL_ACCEPTED", "t" ' Set the X-IDCRL_ACCEPTED
.SetAutoLogonPolicy (AutoLogonPolicy_Always) ' Required! - If this is omitted it will return 401 error - Unauthorized
.Send ' Send the request
cookie = .getResponseHeader("Set-Cookie") ' Get the cookie
.abort ' Abort the WinHTTP connection to avoid problems
End With
Authorization = cookie ' Return the cookie
End Function