vbaexchange-serverexchangewebservices

Connecting to EWS using VBA - Retrieve data using GET call via XML

I am trying to connect to EWS services using VBA - I believe the only way is using POST and GET API calls from EWS.

This code works perfectly fine for the POST case i.e. sending an email:

Sub SendMessage()
   Dim sReq As String
   Dim xmlMethod As String
   Dim XMLreq As New MSXML2.XMLHTTP60
   Dim EWSEndPoint As String
   EWSEndPoint = "server_address"
   sReq = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
   sReq = sReq & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:t=""http://schemas.microsoft.com/exchange/services/2006/types"">" & vbCrLf
   sReq = sReq & "<soap:Header>" & vbCrLf
   sReq = sReq & "<t:RequestServerVersion Version=""Exchange2010""/>" & vbCrLf
   sReq = sReq & "</soap:Header>" & vbCrLf
   sReq = sReq & "<soap:Body>" & vbCrLf
   sReq = sReq & "<CreateItem MessageDisposition=""SendAndSaveCopy"" xmlns=""http://schemas.microsoft.com/exchange/services/2006/messages"">" & vbCrLf
   sReq = sReq & "<SavedItemFolderId>" & vbCrLf
   sReq = sReq & "<t:DistinguishedFolderId Id=""sentitems"" />" & vbCrLf
   sReq = sReq & "</SavedItemFolderId>" & vbCrLf
   sReq = sReq & "<Items>" & vbCrLf
   sReq = sReq & "<t:Message>" & vbCrLf
   sReq = sReq & "<t:ItemClass>IPM.Note</t:ItemClass>" & vbCrLf
   sReq = sReq & "<t:Subject>" & "123" & "</t:Subject>" & vbCrLf
   sReq = sReq & "<t:Body BodyType=""Text"">" & "123" & "</t:Body>" & vbCrLf
   sReq = sReq & "<t:ToRecipients>" & vbCrLf
   sReq = sReq & "<t:Mailbox>" & vbCrLf
   sReq = sReq & "<t:EmailAddress>" & "EMAIL" & "</t:EmailAddress>" & vbCrLf
   sReq = sReq & "</t:Mailbox>" & vbCrLf
   sReq = sReq & "</t:ToRecipients>" & vbCrLf
   sReq = sReq & "</t:Message>" & vbCrLf
   sReq = sReq & "</Items>" & vbCrLf
   sReq = sReq & "</CreateItem>" & vbCrLf
   sReq = sReq & "</soap:Body>" & vbCrLf
   sReq = sReq & "</soap:Envelope>" & vbCrLf
   
   xmlMethod = "POST"
   XMLreq.Open xmlMethod, EWSEndPoint, False, "user","password" 
   XMLreq.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""

   XMLreq.Send sReq
   
   If XMLreq.Status = 200 Then
        ' Message Sent okay
    Else
        ' Something went Wrong
   End If
   
   
End Sub

But for receiving, it doesn't give an error but does not seem to be retrieving any data, this is my script, I am sure my folder parentID used is correct as I retrieved it from a C# run of EWS:

Sub getMessage()
   Dim sReq As String
   Dim xmlMethod As String
   Dim XMLreq As New MSXML2.XMLHTTP60
   Dim EWSEndPoint As String
   Dim xdoc As New MSXML2.DOMDocument
   EWSEndPoint = "serveraddress"
   
   xdoc.Load ("filepath")
   
   sReq = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
   sReq = sReq & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:t=""http://schemas.microsoft.com/exchange/services/2006/types"">" & vbCrLf
   sReq = sReq & "<soap:Header>" & vbCrLf
   sReq = sReq & "<t:RequestServerVersion Version=""Exchange2010_SP1"" />" & vbCrLf
   sReq = sReq & "</soap:Header>" & vbCrLf
   sReq = sReq & "<soap:Body>" & vbCrLf
   sReq = sReq & "<m:FindItem Traversal=""Shallow"">" & vbCrLf
   sReq = sReq & "<m:ItemShape>" & vbCrLf
   sReq = sReq & "<t:BaseShape>IdOnly</t:BaseShape>" & vbCrLf
   sReq = sReq & "<t:AdditionalProperties>" & vbCrLf
   sReq = sReq & "<t:FieldURI FieldURI=""item:Subject"" />" & vbCrLf
   sReq = sReq & "</t:AdditionalProperties>" & vbCrLf
   sReq = sReq & "</m:ItemShape>" & vbCrLf
   sReq = sReq & "<m:ParentFolderIds>" & vbCrLf
   sReq = sReq & " <t:FolderId Id=""parentID"" ChangeKey=""AQAAAA=="" />" & vbCrLf
   sReq = sReq & "</m:ParentFolderIds>" & vbCrLf
   sReq = sReq & "</m:FindItem>" & vbCrLf
   sReq = sReq & "</soap:Body>" & vbCrLf
   sReq = sReq & "</soap:Envelope>" & vbCrLf
   
   xmlMethod = "GET"
   XMLreq.Open xmlMethod, EWSEndPoint, False, "user", "pass"
   
   XMLreq.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
   XMLreq.Send sReq
   
   If XMLreq.Status = 200 Then
        ' Message Sent okay
    Else
        ' Something went Wrong
   End If
   
   
   VBA_to_append_existing_text_file (XMLreq.responseText)

   
End Sub
Solution

  • All EWS requests should be POST (eg your always POSTing the the SOAP XML regardless of the operation your performing) in your FindItem example you using

    xmlMethod = "GET"

    So this isn't correct.