Posting Document via VBA in Excel

Question

I have attempted to post a document in VBA by mimicking the syntax in the six programming languages listed in docusign documentation but no luck. I can successfully GET login information but using this information to POST a document proves fatal: here is the error I receive:

INVALID_MULTI_PART_REQUEST An error was found while parsing the multipart request. Boundary terminator '--BOUNDARY--' was not found in the request.

Any assistance would be appreciated. Here is the code I have so far:

Private Sub APILoginCallTest()

' Step 1 - get initial login information

' defined request and result variables
Set httpRequest = New XMLHTTP60
Set httpResult = New DOMDocument60

' clear out login information response fields
With ThisWorkbook.Sheets("Dashboard")
    .Range("rngResponseLoginStatus").Value = vbNullString
    .Range("rngResponseLoginFull").Value = vbNullString
End With

'open login information url
httpRequest.Open "GET", "https://demo.docusign.net/restapi/v2/login_information", False

' set headers (listed below)
With ThisWorkbook.Sheets("Dashboard")
    For h = 1 To 3
        httpRequest.setRequestHeader .Range("rngAPIHeaderLI0" & CStr(h)).Offset(0, -1).Value, _
            .Range("rngAPIHeaderLI0" & CStr(h)).Value
    Next h
End With
' currently headers are defined as follows (there are three) filling in the method above :
'   X-DocuSign-Authentication : <DocuSignCredentials>
'                                    <Username>[username]</Username>
'                                    <Password>[password]</Password>
'                                    <IntegratorKey>[ikey]</IntegratorKey>
'                                </DocuSignCredentials>
'   Accept : application/xml
'   Content-Type : application/xml

' send login information request
httpRequest.send

' record response in Dashboard worksheet for later review
With ThisWorkbook.Sheets("Dashboard")
    .Range("rngResponseLoginStatus").Value = httpRequest.Status & "-" & httpRequest.statusText
    .Range("rngResponseLoginFull").Value = httpRequest.responseText
End With

' display response in immediate window
Debug.Print "Login Status and Text=" & httpRequest.Status, httpRequest.statusText
Debug.Print "Login Response=" & httpRequest.responseText

' Step 2 signature request

' clear out sig request responses
With ThisWorkbook.Sheets("Dashboard")
    .Range("rngResponseSigRequestStatus").Value = vbNullString
    .Range("rngResponseSigRequestFull").Value = vbNullString
End With


' define the new URL for the signature request using the Base URL returned aboved appended with '/envelopes'
Dim sigURL As String
    sigURL = ThisWorkbook.Sheets("Dashboard").Range("rngResponseLoginBaseUrl").Value & "/envelopes"
' currently sig URL is 'https://demo.docusign.net/restapi/v2/accounts/[account number]'

Dim sigBody As String
    ' start the body of the request
    sigBody = "<envelopeDefinition xmlns=\" & Chr(34) & "http://www.docusign.com/restapi\" & Chr(34) & ">"

    ' add email info to the body
    sigBody = sigBody & "<emailSubject>API Call for adding signature request to document and sending</emailSubject>" & _
                        "<status>sent</status>"

    ' add documents to the body
    sigBody = sigBody & "<documents>" & _
                            "<document>" & _
                                "<documentId>1</documentId>" & _
                                "<name>" & ThisWorkbook.Sheets("Dashboard").Range("rngDocumentName").Value & "</name>" & _
                            "</document>" & _
                        "</documents>"

    ' add email recipients
    sigBody = sigBody & "<recipients>" & _
                            "<signers>" & _
                                "<signer>" & _
                                    "<recipientId>1</recipientId>" & _
                                    "<name>" & ThisWorkbook.Sheets("Dashboard").Range("rngRecipientName").Value & "</name>" & _
                                    "<email>" & ThisWorkbook.Sheets("Dashboard").Range("rngRecipientEmail").Value & "</email>" & _
                                    "<tabs>" & _
                                        "<signHereTabs>" & _
                                            "<signHere>" & _
                                                "<xPosition>100</xPosition>" & _
                                                "<yPosition>100</yPosition>" & _
                                                "<documentId>1</documentId>" & _
                                                "<pageNumber>1</pageNumber>" & _
                                            "</signHere>" & _
                                        "</signHereTabs>" & _
                                    "</tabs>" & _
                                "</signer>" & _
                            "</signers>" & _
                        "</recipients>"

    ' close the envelope definition
    sigBody = sigBody & "</envelopeDefinition>"


' read in document in byte array
Dim intFileNum%, bytTemp As Byte, intCellRow%
Dim bytTempAll As String
intFileNum = FreeFile
intCellRow = 0
Open (ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Dashboard").Range("rngDocumentName").Value) For Binary Access Read As intFileNum
Do While Not EOF(intFileNum)
   ' intCellRow = intCellRow + 1
    Get intFileNum, , bytTemp
    'Cells(intCellRow, 1) = bytTemp
    bytTempAll = bytTempAll & bytTemp
Loop
Close intFileNum
Dim lenFile As Long
    lenFile = Len(bytTempAll)   ' just curious

Set AdobeStream = CreateObject("ADODB.STREAM")
With AdobeStream
    .Type = 1 ' binary
    .Open   ' open the file
    .LoadFromFile (ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Dashboard").Range("rngDocumentName").Value)
End With

' create multipart/form-data request into a 'Pre' and a 'Post' with the document bytes eventually going in between
Dim requestBodyPre As String
    requestBodyPre = "\r\n\r\n--BOUNDARY\r\n" & _
            "Content-Type: multipart/form-data, boundary =BOUNDARY" & vbCrLf & _
            "Content-Length: " & lenFile & "\r\n" & _
            sigBody & _
            "\r\n\r\n--BOUNDARY\r\n" & _
            "Content-Type: " & ThisWorkbook.Sheets("Dashboard").Range("rngContentType").Value & "\r\n" & _
            "Content-Disposition: file; filename:\" & Chr(34) & ThisWorkbook.Sheets("Dashboard").Range("rngDocumentName").Value & Chr(34) & "\; " & _
            "documentid=1\r\n\r\n"

' break up the requestBody into 'Pre' and 'Post' because document bytes need to be passed in the middle

Dim requestBodyPost As String
    requestBodyPost = "\r\n" & "--BOUNDARY--\r\n\r\n"


' reset response variables for use in signature response
Set httpRequest = Nothing
Set httpResult = Nothing

Set httpRequest = New XMLHTTP60
Set httpResult = New DOMDocument60

' open url for signature POST
 Debug.Print "POST to url " & sigURL
httpRequest.Open "POST", sigURL, False

' attach headers
For h = 1 To 3
    httpRequest.setRequestHeader ThisWorkbook.Sheets("Dashboard").Range("rngAPIHeaderSR0" & CStr(h)).Offset(0, -1).Value, _
        ThisWorkbook.Sheets("Dashboard").Range("rngAPIHeaderSR0" & CStr(h)).Value
Next h
' currently headers are defined as follows (there are three) filling in the method above :
'   X-DocuSign-Authentication : <DocuSignCredentials>
'                                    <Username>[username]</Username>
'                                    <Password>[password]</Password>
'                                    <IntegratorKey>[ikey]</IntegratorKey>
'                                </DocuSignCredentials>
'   Accept : application/xml
'   Content-Type    multipart/form-data;  boundary=BOUNDARY

' send request
Dim streamAll As String
    'streamAll = StrConv(requestBodyPre, vbUnicode) & StrConv(requestBodyPost, vbUnicode)
    'streamAll = AdobeStream.readtext
 '
 Debug.Print "sending " & requestBodyPre
 Debug.Print "sending " & requestBodyPost

' post request
httpRequest.send requestBodyPre & AdobeStream.read & requestBodyPost

' record sig request response in worksheet
With ThisWorkbook.Sheets("Dashboard")
    .Range("rngResponseSigRequestStatus").Value = httpRequest.Status & "-" & httpRequest.statusText
    .Range("rngResponseSigRequestFull").Value = httpRequest.responseText
End With


Set httpRequest = Nothing
Set httpResult = Nothing
Set AdobeStream = Nothing

MsgBox "Done!"

End Sub

I'd switch out all of the "\r\n" parts for vbCrLf. The CRLF sequence for HTTP is ASCII 13 10 which vbCrLf is a shorthand for. Using "\r\n" will almost certainly generate ASCII 92 114 92 110 because it will be interpreted as text rather than an escape sequence. You might try sending some experimental POST requests to http://httpbin.org/post as that will display what was actually received in the request — barrowc
– barrowc, Commented Jul 2, 2015 at 23:32
Excellent suggestion. This got me further and was definitely a concern with VBA. — Brian Moriarty
– Brian Moriarty, Commented Jul 3, 2015 at 21:09

Tim Williams · Accepted Answer · 2015-07-02 23:04:31Z

1

Really more of a question/comment, but this looks wrong:

sigBody = "<envelopeDefinition xmlns=\" & Chr(34) & _
          "http://www.docusign.com/restapi\" & Chr(34) & ">"

You don't escape " in VBA with \ , but by doubling them up:

sigBody = "<envelopeDefinition xmlns=""http://www.docusign.com/restapi"">"

answered Jul 2, 2015 at 23:04

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Moriarty Over a year ago

thanks Tim. I noticed also that this seemed off, but I am using the Chr(34) as quotes instead of trying to type in the quotes in the code. Perhaps it does not require the quotes? Either way I will give it a shot with the new line of code you put out for 'sigBody='

Tim Williams Over a year ago

One alternative is to use single-quotes for your attribute values: that is valid in XML, and you don't have to worry about escaping them.

3-14159265358979323846264 · Accepted Answer · 2015-07-02 22:45:26Z

0

Have you assigned requestBodyPre & AdobeStream.read & requestBodyPost to a string variable to check that the value is what you expect? Maybe even write it to a text file. You should also check that the newline sequence is correct (it might be just \r or \n)

answered Jul 2, 2015 at 22:45

3-14159265358979323846264

3,2703 gold badges18 silver badges41 bronze badges

3 Comments

Brian Moriarty Over a year ago

you focus correctly on perhaps the crux of the issue. Should this all be in text or binary? It seems the 'pre' and the 'post' are xml with \r and \n but according to some of the sample code the 'stream' of the pdf should be in binary? can it all be in text?

3-14159265358979323846264 Over a year ago

I hope it helps you find an answer .. gonna vote your question up so it hopefully gets some extra attention as it's bedtime for me! Good luck.

Brian Moriarty Over a year ago

I think my issue lies in 'streaming' the document in the middle of the XML posting. should it all be 'bytes' or can I send an entire pdf as a string?

Brian Moriarty · Accepted Answer · 2015-07-08 21:39:36Z

0

I resolved this with more code but have discovered an issue which I posted in another section. The resolution was not to use the adobestream rather read it in as bytes.

answered Jul 8, 2015 at 21:39

Brian Moriarty

662 bronze badges

Collectives™ on Stack Overflow

Posting Document via VBA in Excel

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related