Use MS Access VBA to create http post request

Rick Rudolph
Rick Rudolph used Ask the Experts™
on
I am trying to send http requests to the API provided by HelloSign.com........To test what I was doing, I used Git BASH, and the following command completed correctly (I have changed the key and some of the user / document information, but the syntax is exactly as I sent it):

curl -u "9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a:" "https://api.hellosign.com/v3/signature_request/send" -F "title=rjr.docx" -F "subject=Rick's Test Document" -F "message=Please sign this contract" -F "signers[0][email_address]=rrudolph@test.com" -F "signers[0][name]=Rick Rudolph" -F "file[0]=@c:/docpath/rjr.docx" -F "test_mode=1"

I opened up a VBA module, and set a reference to the Microsoft XML 6.0 library. Then I tried the following:

Public Function TestAPI()

Dim StrAPI As String
Dim request As MSXML2.ServerXMLHTTP60
StrAPI = " -u '9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a:' 'https://api.hellosign.com/v3/signature_request/send' -F 'title=rjr.docx' -F 'subject=Rick's Test Document' -F 'message=Please sign this contract' -F 'signers[0][email_address]=rrudolph@test.com' -F 'signers[0][name]=Rick Rudolph' -F 'file[0]=@c:/docpath/rjr.docx' -F 'test_mode=1'"
Set request = New ServerXMLHTTP60
request.Open "Post", StrAPI, False
request.send
Debug.Print request.StatusText

End Function

This code created an error at :  request.Open "Post", StrAPI, False

The error was:
runtime error '-2147012890 (80072ee6)
System error: -2147012890

The only change I made to the string that worked in Curl was to change the double quotes to single quotes inside the string.

Thanks in advance as this is a stumbling block in a new process we are trying to develop.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
This should work if the URL was correct....i modified it due to wrong placement of API Key but still error...
Public Function Test()
Dim objHTTP As Object, ans As String
Dim responseText As String
Dim strAPI As String
Set objHTTP = New WinHttp.WinHttpRequest
strAPI = "https://9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a:@api.hellosign.com/v3/signature_request/send' -F 'title=rjr.docx' -F 'subject=Rick's Test Document' -F 'message=Please sign this contract' -F 'signers[0][email_address]=rrudolph@test.com' -F 'signers[0][name]=Rick Rudolph' -F 'file[0]=@c:/docpath/rjr.docx' -F 'test_mode=1'"
With objHTTP

    .Open "POST", strAPI, False
    .send
    .WaitForResponse
    responseText = .responseText
End With

msgbox responsetext
End Function

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can't POST a URL containing spaces and Curl syntax:

curl.1 the man page

So, resolve what the Curl command examples do, and then adjust the URL and parameters accordingly (if possible).

Author

Commented:
I ran the code suggested by John, and receive the following message in the Message Box:

{"error":{"error_msg":"Invalid URI. Make sure you have the correct URI and HTTP method.","error_name":"not_found"}}

I did a debug.print of strAPI, and this is the result:

https://9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a:@api.hellosign.com/v3/signature_request/send' -F 'title=rjr.docx' -F 'subject=Ricks Test Contract' -F 'message=Please sign this contract' -F 'signers[0][email_address]=rrudolph@test.com' -F 'signers[0][name]=Rick Rudolph' -F 'file[0]=@c:/docpath/rjr.docx' -F 'test_mode=1'

I am obviously lost here,

I did change the strAPI line to eliminate the apostrophe in my name......so I changed Rick's to Rick
The url makes no sense to me with the https:// followed by the api key.....:@api......
The apostrophe after the word send is also confusing to me
ste5anSenior Developer

Commented:
Do you follow Gustav's advice?

Cause the -F parameter means that you need to post form variables as it seems as multipart/form-data in opposite to application/x-www-form-urlencoded.

To get a better insight what you need to do: Install Fiddler to visualize the entire http traffic from curl as well as your tries.

Author

Commented:
I did not truly understand Gustav's advice, so the short answer is no.

I will install Fiddler and see what I can learn from that.

Clearly my biggest problem is a total lack of knowledge in what is going on behind the scenes. Is there any information from Fiddler that might give us information as to what is failing with the MS Access approach that John suggested?

Thank you,

Rick
ste5anSenior Developer

Commented:
The -u parameter means normally HTTP basic authentication. This is done by setting the appropriate header

objHttp.SetRequestHeader "Authorization", "Basic " + Base64Encode(authUser + ":" + authPass)

Open in new window


The -F parameters means that you need to add the data as parameter for the Send call. And according to the curl description it is a multipart schema instead of url encoding.

But you may test url encoding as:

Option Compare Database
Option Explicit

Public Function Test()

  Const URL As String = "https://api.hellosign.com/v3/signature_request/send"

  Dim WebClient As MSXML2.XMLHTTP60

  Dim Password As String
  Dim UrlEncodedData As String
  Dim Username As String

  Password = "..."
  UrlEncodedData = UrlEncode("title=rjr.docx&subject=Rick's Test Document&message=Please sign this contract&signers[0][email_address]=rrudolph@test.com&signers[0][name]=Rick Rudolph&file[0]=@c:/docpath/rjr.docx&test_mode=1")
  Username = "9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a"

  Set WebClient = New MSXML2.XMLHTTP60
  WebClient.Open "POST", URL, False, "", ""
  WebClient.SetRequestHeader "Content-Type", "application/x-www-form-UrlEncoded"
  WebClient.SetRequestHeader "Authorization", "Basic " + EncodeBase64(Username + ":" + Password)
  WebClient.Send UrlEncodedData
  Debug.Print WebClient.Status, WebClient.StatusText
  Set WebClient = Nothing

End Function

Public Function EncodeBase64(ByVal CData As String) As String

  Dim Document As MSXML2.DOMDocument
  Dim Element As MSXML2.IXMLDOMElement

  Dim Data() As Byte

  Set Document = New MSXML2.DOMDocument
  Set Element = Document.createElement("b64")
  Data = StrConv(CData, vbFromUnicode)
  Element.dataType = "bin.base64"
  Element.nodeTypedValue = Data
  EncodeBase64 = Element.Text
  Set Element = Nothing
  Set Document = Nothing

End Function

Public Function UrlEncode( _
   ByVal StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String
  Dim bytes() As Byte, b As Byte, i As Integer, space As String

  If SpaceAsPlus Then space = "+" Else space = "%20"

  If Len(StringVal) > 0 Then
    With New ADODB.Stream
      .Mode = adModeReadWrite
      .Type = adTypeText
      .Charset = "UTF-8"
      .Open
      .WriteText StringVal
      .Position = 0
      .Type = adTypeBinary
      .Position = 3 ' skip BOM
      bytes = .Read
    End With

    ReDim result(UBound(bytes)) As String

    For i = UBound(bytes) To 0 Step -1
      b = bytes(i)
      Select Case b
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Chr(b)
        Case 32
          result(i) = space
        Case 0 To 15
          result(i) = "%0" & Hex(b)
        Case Else
          result(i) = "%" & Hex(b)
      End Select
    Next i

    UrlEncode = Join(result, "")
  End If
End Function

Open in new window

Author

Commented:
ste5an..........I think we are making progress. I did make some very minor changes to your encodebase64 function. i changed the msxml2.domdocument to msmxl2.domdocument60

Ran the code, and received a 400 bad request message in debug.print.

The code, did bring up a box labeled Windows Security
 MSAcess.exe
The server api.hellosign.com is asking for our user name and password
I pasted the key into the user name, left the password blank, and I believe that may have generated the bad request

subsequent running of the code do not bring up the box, but still have the 400 Bad Request message

Author

Commented:
I omitted that their was a message in the security box "That server also reports. "API".

I think I need to delete the windows security username / password, but don't know where it is stored. I don't see it in the credentials manager.
API_Security_Box.JPG
ste5anSenior Developer

Commented:
It's necessary that you use Fiddler in parallel to compare what curl has sent and what you send. This makes getting things correct much simpler.

Did you use the correct credentials in my sample?

Author

Commented:
Yes, I changed the credentials back to the correct ones. Did you happen to look at the jpg i posted?
ste5anSenior Developer

Commented:
That dialog should only pop open, when there is either no credentials in the code for basic authentication. Test the authentication alone:

Public Function Test()

  Const URL As String = "https://api.hellosign.com/v3/signature_request/send"

  Dim WebClient As MSXML2.XMLHTTP60

  Dim Password As String
  Dim Username As String

  Password = "..."
  Username = "9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a"

  Set WebClient = New MSXML2.XMLHTTP60
  WebClient.Open "POST", URL, False, "", ""
  WebClient.SetRequestHeader "Authorization", "Basic " + EncodeBase64(Username + ":" + Password)
  WebClient.SetRequestHeader "Content-Type", "application/x-www-form-UrlEncoded"
  WebClient.Send
  Debug.Print WebClient.Status, WebClient.StatusText
  Set WebClient = Nothing

End Function

Open in new window

Check the used credentials. Not that you accidentally have still a colon in there.

Author

Commented:
This brings up the Windows Security box.........also, do not understand "Not that you accidentally have still a colon in there"

the only colon I see is the colon in your code. if you mean did I accidentally include the colon in my paste, the answer is no.

However, this did give some interesting results. I received a message 402 Payment Required.............this tells me that we are being successful in communicating with the api.

In the original string, the very end of the string........test_mode=1 turns the request into a free request. Now when I authenticate only, it wants me to pay and I don't have a pay account. So the rest of my problem is now in the string, not the authentication. I will look closer at that and see if I see any clues.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
FYI, besides fiddler, I've found Postman to be a very nice tool to debug with when trying to wade through this stuff.

Jim.
I ultimately ended up setting up Apache on XAMPP , and connecting to the API via PHP. thank you for everyone's contributions on this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial