Send ticket from VBA to Jira using Json

[Zain ul Abaideen]

I want to import a ticket to Jira using vba. The authentication works (jira-rest-api), but for some reason the "Send JSON" part doesn't.

I can get the info for a specific issue (ex: using "GET" instead of "POST" and using this URL https://www.satisfiedshoes.com/ but when I have to send a new issue, the code doesn't do anything (no error message). Can someone help me with this please? :)

Sub test1()
    Dim JSON As String
    JSON = getJSON("Danemark", "DK", "SAM-123", "Test API", "Test", "Test")
    createJIRATask "myusername", "mypass", JSON
End Sub

Private Sub createJIRATask(User As String, Password As String, JSON As String)
'Sending request to JIRA
    Dim JiraService As New MSXML2.XMLHTTP60
    With JiraService
    'with this object, we open a request and set the HTTP header
    .Open "POST", "https://myproject/rest/api/2/issue/", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "Authorization", "Basic " & UserPassBase64
    .Send JSON
    MsgBox JiraService.ResponseText
    MsgBox .Status
    If .Status = "401" Then
        MsgBox "Not authorized or invalid username/password"
    Else
        MsgBox "Correct credential!!"
    End If
    End With
End Sub

Private Function UserPassBase64() As String
    Dim objXML       As MSXML2.DOMDocument60
    Dim objNode      As MSXML2.IXMLDOMElement
    Dim arrData()    As Byte

    arrData = StrConv("myusername:mypass", vbFromUnicode)

    Set objXML = New MSXML2.DOMDocument60
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData

    UserPassBase64 = objNode.Text

End Function

Private Function getJSON(projectName As String, projectKey As String, issueKey As String, issueName As String, issueRep As String, issueSubject As String) As String
    Dim JSON(14) As String, JSONText As String
    
    JSON(0) = "{"
    JSON(1) = "  ""projects"": ["
    JSON(2) = "    {"
    JSON(3) = "      ""name"": ""@customprojectName"","
    JSON(4) = "      ""key"": ""@customprojectKey"","
    JSON(5) = "      ""issues"": ["
    JSON(6) = "        {"
    JSON(7) = "          ""key"": ""@customissuKey"","
    JSON(8) = "          ""status"": ""@customissueName"","
    JSON(9) = "          ""reporter"": ""@customRep"","
    JSON(10) = "          ""summary"": ""@customSubject"""
    JSON(11) = "        }"
    JSON(12) = "      ]"
    JSON(13) = "    }"
    JSON(14) = "  ]"

    JSONText = Join(JSON, vbNewLine)
    JSONText = Replace(JSONText, "@customprojectName", projectName)
    JSONText = Replace(JSONText, "@customprojectKey", projectKey)
    JSONText = Replace(JSONText, "@customissueKey", issueKey)
    JSONText = Replace(JSONText, "@customissueName", issueName)
    JSONText = Replace(JSONText, "@customRep", issueRep)
    JSONText = Replace(JSONText, "@customSubject", issueSubject)
    getJSON = JSONText

End Function

[Dirk Ronsmans]

Hey @Zain ul Abaideen and welcome to the community!

I'm not a VBA expert by far but just looking at the code I wonder if you are getting any error code back?

I don't see you adding any payload to your POST request so if the authentication is fine you should at least get a HTTP 400 bad request back.

Have you tried doing the same call with a REST API tool? You can try some in your browser (like a browser plugin) to make sure the url/context is correct