Excel – Custom header with MSXML2.ServerXMLHTTP

excelserverxmlhttpvba

I am currently trying to use MSXML2.ServerXMLHTTP to send a POST http request.
I need to add a custom header "Auth" so that my requests get authorized but it doesn't seem to work.
Here is the code for my post function:

Function post(path As String, authToken As String, postData As String) As String
  Dim xhr As Object
  Dim message As String
On Error GoTo error:

  Set xhr = CreateObject("MSXML2.ServerXMLHTTP")
  xhr.Open "POST", path, False
  xhr.setRequestHeader "Content-Type", "application/json"
  xhr.setRequestHeader "Auth", authToken
  xhr.send postData

  If xhr.Status = 200 Then
    message = xhr.responseText
  Else
    message = xhr.Status & ": " & xhr.statusText
  End If

  Set xhr = Nothing
  post = message
error:
  Debug.Print "Error " & Err.Number; ":" & Err.Description
End Function

And I end up with "Error -2147012746 Requested Header was not found" (The message is actually translated since I use a different language on my computer).

However I didn't have this problem with the "Microsoft.XMLHTTP" Object.
Am I doing something wrong ?

Thank you for your time.

Best Answer

Try changing the call to SetRequestHeader to use a string literal. I duplicated the problem when authToken does not have a value set.

Change from this

xhr.setRequestHeader "Auth", authToken

To this

xhr.setRequestHeader "Auth", "testdatahere"

Related Solutions

Excel – Sending formatted Lotus Notes rich text email from Excel VBA

The short answer is Yes. The long answer is painful. There aren't any great classes exposed to manipulate rich text items in Notes. However a few that you can research are the NotesRichTextStyle, NotesRichTextParagraphStyle, and NotesRichTextTable to name a few. These classes help you define some rich text elements and add them programmatically to your rich text field.

Another approach, since you're sending email, is to use the NotesMIMEEntity classes and build the email using HTML (way way easier). Here's some sample code:

Set s = New NotesSession
Dim docMail As NotesDocument
Dim body As NotesMIMEEntity
Dim stream As NotesStream

Set db = s.CurrentDatabase
s.ConvertMIME = False ' Do not convert MIME to rich text

'Create email to be sent
Set docMail = db.CreateDocument

With docMail
        .SendTo = SEND TO ADDRESS
        .From = FROM ADDRESS
        .Principal = FROM ADDRESS
        .ReplyTo = REPLY TO ADDRESS
        .Subject = SUBJECT
        .Form = "Memo"
End With

  Set stream = s.CreateStream
Set body = docMail.CreateMIMEEntity
Call stream.WriteText ("YOUR HTML CODE GOES HERE")

'ENC_IDENTITY_8BIT used because of technote found on notes.net
'http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/aeedaf28e47546ad85256f6a000a4b48?OpenDocument
Call body.SetContentFromText (stream, "text/html;charset=iso-8859-1",ENC_IDENTITY_8BIT) 

Call docMail.Send(False)
Set docMail = Nothing
Set body = Nothing
Set stream = Nothing

s.ConvertMIME = True ' Restore conversion

Essentially you'll need to turn the ConvertMIME setting off in the NotesSession. Then create a new document, set the mail properties, etc. That part is already in your VBA code. Next, create the MIMEEntity and a stream to hold your HTML text. Finally call the SetContentFromText method on your MIMEEntity object. Then send your email. Note the last call is to turn the ConvertMIME feature back on for the NotesSession.

I'm not sure if this will all work via COM, but it does work well in LotusScript agents in Notes.

I hope this helps!

Differences between Msxml2.ServerXMLHTTP and WinHttp.WinHttpRequest

Msxml2.XMLHTTP and Msxml2.ServerXMLHTTP are two components share the similar interface for fetching XML files over HTTP protocal. The former is built upon URLMon, which relies on WinINet. The later is built upon WinHTTP, which is a server friendly replacement for WinINet. To put it simple - ServerXMLHTTP = XML + WinHTTP.

Related Topic