Excel VBA API POST JSON conversion

[smithc2005]

I have a spreadsheet with tracking information on it that I am trying to send through a API to create a tracking event. The issue I have is making sure my body is in JSON format. I have managed to parse my excel cells to JSON but now I need to know how to actually save this as a string instead of out to a file so I can send it.
The API is https://docs.aftership.com/api/4/overview

Sub PostTrack()    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    With xmlHttp
    .Open "POST", "https://api.aftership.com/v4/trackings/", False
    .setRequestHeader "aftership-api-key", "MYAPIKEY"
    .setRequestHeader "Content-Type", "application/json"


        'Convert Excel to JSON


            Dim excelRange As Range
            Dim jsonItems As New Collection
            Dim jsonDictionary As New Dictionary
            Dim i As Long
            Dim cell As Variant
    
    
            Set excelRange = Cells(1, 1).CurrentRegion


            For i = 1 To excelRange.Rows.Count
            jsonDictionary("title") = Cells(i, 1)
            jsonDictionary("tracking_number") = Cells(i, 2)
    


            jsonItems.Add jsonDictionary
            Set jsonDictionary = Nothing
            Next i


            MsgBox JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)
    
            Dim jsonFileObject As New FileSystemObject
            Dim jsonFileExport As TextStream
    
            Set jsonFileExport = jsonFileObject.CreateTextFile("C:\Users\csmit\Desktop\jsonExample.json", True)
            jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))
    
'Send JSON Body to API
'????????
    
End With


End Sub

 

[John_w]

The documentation is a little poor. Try changing the Open to specify a tracking number like this:

Dim TrackingNumber As String
TrackingNumber = "12345"
.Open "POST", "https://api.aftership.com/v4/trackings&tracking_number=" & TrackingNumber, False

and send the request using:

Dim JSONstring As String
JSONstring =  JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)
.Send (JSONstring)