VBA and json objects-CodePudding

I want to create meail merge letters to members of a community, reminding them of the new year and to pay their fees. There is a nice API on a server that can create QR-codes that the a phone pay app can read. The server responds to HTTP POST rewuests. This API provides the possibility to pre-fill in the payment parameters like the payment receiver, the amount an a message to the payment receiver.

I am starting from an Excel-sheet that keeps the list of members amongst other items. I can create mail merge letters from this member list, but fails to get the creation of the prefilled QR-codes work correctly.

Since the base is Excel (and Word for the mail merge) the language used is VBA. With the code below i can get response from the server with a prefilled QR-code that contains the recieiver number of the payment:

Sub DownloadQRCode(ByVal myURL As String, ByVal LocalFileName As String)
     
Dim msXML As New MSXML2.ServerXMLHTTP60
Dim myStream As New ADODB.Stream

    msXML.Open "POST", myURL, False
    msXML.setRequestHeader "Content-Type", "application/json"
    msXML.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"
    msXML.send "{""payee"":""987654321""}"
    If msXML.Status = 200 Then
        myStream.Open
        myStream.Type = adTypeBinary
        myStream.Write (msXML.responseBody)
        myStream.SaveToFile LocalFileName, adSaveCreateOverWrite
        myStream.Close
    End If
    Set msXML = Nothing
    Set myStream = Nothing
End Sub

If I change the msXML.send statement to

msXML.send "{""message"":{""value"":""Sven_Svensson"",""editable"":""false""}}"

I get the response code 400 (Bad request)

The speicification for the message object looks like this

My questions are: Do I use the wrong value in "Content-Type" header or is the json representation of the message object faulty?

CodePudding user response：

You used an array ([]) in your message 'msXML.send "{""message"":[{""value"":""Sven_Svensson"",""editable"":""false""}]}"' while message's specification doesn't state it. I guess it should look like 'msXML.send "{""message"":{""value"":""Sven_Svensson"",""editable"":""false""}}"'

CodePudding user response：

This works for me:

msXML.send "{""payee"":""987654321"",""message"":{""value"":""Sven_Svensson"",""editable"":true}}"