locked
Easiest way to use docusign from withing an Access 2016 program RRS feed

  • Question

  • I have an Microsoft Access program (runs on machines with Access 2010 and others that have 2016) .  I currently have a report that runs and creates a pdf then submits it to outlook to email it to various addressed program determined.  I want to send the document to docusign.  The document needs to be signed by a few different people so how hard is it to have the VBA code send the pdf report to docusign and tell docusign to get the few (1 - 5 people) people to sign it.  Also every time my program is opened I would like it to check docusign to see what is still pending and what has bee signed.

    Cal you give me a basic sample of the code that might make this happen.

    Thursday, May 25, 2017 7:27 AM

All replies

  • >  how hard is it?

    It's hard. We did this once in our company from a .NET website and it took some serious work by a good programmer. It can be done from VBA, but their APIs (https://www.docusign.com/developer-center/api-overview) are either REST or SOAP based and thus much easier to use with .NET than with VBA. You could of course write a .NET component with a COM wrapper and then call it from VBA. All in all, clearly not for beginners.


    -Tom. Microsoft Access MVP

    Thursday, May 25, 2017 1:59 PM
  • You can use webs services from Access. The common ways are:

    Use MSXML library.

    I recommend this approach if the web service involved only requires 1 or 2 methods to use. You have to grab the API documentations and get your hands on the get/put headers (so if the API provides the headers, then this is not too hard).

     For example the SharePoint web service is “huge”, but you can “hit” the web service and it will spit out the get/put header for any method. So you can “copy” these headers into Access and use those xml items to interface with the web service without having to hand code 100% of the interface by hand. In fact I copy the XML chunks into a table in Access (that way no messy huge amounts of XML in VBA code). I don’t have any specific code for DocuSign – but I would check in their forums and user groups. Using MSXML in VBA looks like this:

    (in this example we hitting the yahoo web site for stock quotes – this is a few years old and I believe they dropped this service now)

    Public Sub GetQuote2()

       Dim objXML           As Object
       Dim strSymbol        As String
       Dim strURL           As String
       Dim strWFormat       As String

       Set objXML = CreateObject("MSXML2.XMLHTTP")
               
       strURL = "http://ca.finance.yahoo.com/d/quotes.csv?s="
       strWFormat = "&f=sl1d1t1c1ohgv&e=.csv"
      
       strSymbol = "MSFT"
      
       objXML.Open "GET", strURL & strSymbol & strWFormat, False
       objXML.Send
      
       Debug.Print "Symbol = " & Split(objXML.ResponseText, ",")(0)
       Debug.Print "Trade  = " & Split(objXML.ResponseText, ",")(1)
       Debug.Print "Date   = " & Split(objXML.ResponseText, ",")(2)

    End Sub

    So for a simple web services call, the above is workable. As things become complex, the MSXML does fall down. But as the above SharePoint tip shows, if you write a get/put method that consumes a header, then you can consume "most" methods of the web site with a "common" set of routines.

    Use .net.

    I quite much recommend this road. As suggested by Tom in this thread using .net is  good choice.  If the web service has a WSDL (web services definition) , then you just point Visual Studio to that web site and “boom” – all of the methods, properties etc. are generated for you. At that point it just like using say Word or Outlook from VBA, you now using that web service just like any other object in code. You even  get inteli-sense with all of the objects properties and methods – except they all coming from the web site as opposed to a local object reference.  So web services can be consumed just like “objects” in Visual Studio.

    So for any “complex” web service I recommend you write the code in .net, and then “consume” or “call” this .net code from Access/VBA. So that .net object when used in Access will provide inteli-sense and all of the objects and methods of that object will be seen + useable in VBA. This approach is the least amount of work for a given effort (provided you have .net skills and know how to create "COM" objects for consumption by VBA).

    I would think the DocuSign site, their developers forums etc. would have examples – check out their forums I sure some sample code or tips exist. However such code is un-likely to be “just” a few lines of code.

    I not used DocuSign, but I did write a (.net) object for using Adobe’s EchoSign from VBA/Access. So DocuSign is much like EchoSign from Adobe.

    I of course went the .net road because these signing web objects tend to be rather complex and I doubt it makes sense to just use the MSXML library.

    The exception to this rule is if you have the get/set XML headers for each web method. In that case I place all of the headers in a table with a memo column to contain the XML headers. You then grab the header for a given method and send it using MSXML. So you “can” in some cases use the MSXML library with Access/VBA even for relative complex web services if the headers are available.

    For example, SharePoint is a “huge” web service, but if you hit the web site “service”, then it spits out the get and put header for a given method. You can then cut + paste those headers into an Access table. So in this setup you not hand coding each method nor having to place tons of XML right in VBA. However, I still think the .net way is the least amount of work, and you far more likely to find example code on their site for .net.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, May 25, 2017 4:16 PM
  • You wrote "I not used DocuSign, but I did write a (.net) object for using Adobe’s EchoSign from VBA/Access. So DocuSign is much like EchoSign from Adobe."  I am not set on using Docusign EchoSign is perfectly acceptable.  I would prefer to keep it simple and do all from within Access and not have to call another .net program that I have to create.  Currently to have Outlook send the docs I need just 1 line plus the lines of code to assign the values to the variables.  See below:  Can I some type of similar code totally from within Access VBA code?

    DoCmd.SendObject acSendNoObject, vReportName, vOutPutFormat, vTo, vCc, vBCc, vSubject, vMessageText, vEditMessage, vTemplateFile

           
    Thursday, May 25, 2017 6:21 PM
  • Well, the “process” seems simple, but you have to lay out a workflow and set of steps.

    The way EchoSign works is you create some type of document in Access (say a PDF invoice). So in VBA you generate an invoice for a given customer. Now you want that customer to “sign” or “approve” that document.

    What this means then is your VBA code has to take the existing PDF you created and:

    Send the PDF to the EchoSign web site – this provides an interface for the customer to jump to the web site and view and ultimately sign + approve the document.

    So in effect you not sending an email to the customer anymore, but sending the PDF to the EchoSign web site with the correct user/email name. The web site THEN sends an email to the customer with a “link” to the PDF. The customer then clicks on the link, views the “several” PDF invoices, approves some of them, and they are done.

    At that point, you in Access would/could/should/can run some daily process in VBA that looks at the list of invoices you send to the EchoSign site and:

    Loop though all of your outstanding un-approved invoices and then HIT/CHECK/ASK/INQUIRE from the Echo sign site which were approved by the customer. So you can now automatic update the invoices in Access that were approved by the customer. At this point you perhaps print out a list or have the accounting people fire up your application and it can “display” the list of approved invoices that you now send/bill to that customer.

    You likely thus want some option for accounting people to “confirm” and "view" what invoices they are to send out to the customer. (so you likely fill out a column called CustomerApprovedDate in your Access database with this process).  Now you print this list or let accounting people or whoever view this information/report you have in Access. And I suppose as they send out the invoices out, they check off a "send" or "billed" column in Access.

    So in effect you not really “just” sending a PDF to a customer, but in fact sending a PDF to a web site which THEN sends the information to the customer. And as you “send” each document to the web site for signatures, then each document does generate a “document id” – a kind of “primary” key that you use in VBA later on to check/inquire/ask about the status of that given document.

    So as you “send” or perhaps better said “push” the documents to the web site, each document generates a unique and special ID as you do this. So this “id” is returned to your VBA code. You thus want to save/grab that number into your work flow and assign it to the given invoice you generated. So this information will result as you carrying out the set of “steps” you designed using VBA and your application.

    So the basic steps or “work flow is”

    VBA – create + generate the invoice document as PDF – save it on disk.

    VBA -> start interface to EchoSign object.
           Set Customer info
           Set customer email
           Attach PDF document from disk
           PushToEchoSite

           "done one invoice" - loop to next or quit.

    So the above set of steps will grab the pdf, send to the web site and also generate the email to the customer (so the web service and site generates that email – not you). That email to the customer will have a “link” to the document (now on the web site). In some cases they can even “view” all “pending” invoices from that web interface. So they can look at and check off for approval. (The approval process).

    So the customer can view and approve documents.

    At this point you can setup and run some code in Access/VBA to hit the web site and “inquire” as to which particular document been approved, or even process your outstanding list of un-approved invoices and “hit” the web site to test/check/ask/show which of your invoices have been approved.
     
    So you much have to break down how this process will work into a set of steps – but that’s quite much how any software works anyway.

    Once you written the interface in .net, then everything else can be done from Access. However as noted I would not really want to hand code the XML and interface to say EchoSign in VBA without some tools to “generate” the web interface for you. I mean, if you were coding XML interfaces every day in VBA, then sure – but even I can’t or could not justify such hand coding in VBA as compared to using Visual Studio to consume that web service. (It too much work and effort – and thus too much cost in VBA).

    A lot of systems often will provide some “COM” object that you can use. For example I written interfaces to QuickBooks accounting, and they do provide a “COM” object that you can use directly from VBA. However I still in this cased used their .net object since it was better documents (and syntax in vb.net vs VBA is near identical anyway).

    So while it is possible to do this in VBA and say MSXML library, it usually  too much effort and time unless you done a lot of such interfaces and code in the past (so as you get good at this, then you have a greater skill set to attempt this). However, you still faced with adopting the MSXML library, or dumping the MSXML library and build a .net object. You trade one for the other – in most cases the trade of using .net to generate the methods and interface far outweighs the value and efforts of using MSXML (which means you don't need to learn or use .net). What path you take will depend on your skill set. If you never used MSXML much, then I think going the .net road is better (but I have .net experience).


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Thursday, May 25, 2017 7:15 PM
  • To Albert D. Kallal can I buy your .net com so I can call it from my Access program and just pass my account info and other parameters?  And yes I am willing to use Adobe Sign if that is the solution you have that already works.  How can I contact you?
    Friday, May 26, 2017 2:19 AM
  • Ok, sure you can contact me.

    I want to use "caution" since this is a volunteer group and as a MVP my time here is 100% on a volunteer basis. So me soliciting work in this form is "out of bounds" in regards to my code of conduct here.

    So I am not affiliated with this board or Microsoft - the only exception being here that Microsoft has awarded me MVP status for me sharing my knowledge and efforts I share with the Access community in general.

    Code from VBA to use my .net objet looks like this:

      
       Dim echo    As New ERSEchoSign.Sign

       echo.apiKey = "xxxxxxxxxxxxxx"
       echo.SenderEmail = "nospam-kallal@kallal.ca"
       echo.SenderPassword = "xxxxxxxxx"
       

    ' useage:

    ' Public Function SendForSigning(strFile As String,
    '                                   strDocDisplayName As String,
    '                                   strRecipient As String,
    '                                   DaysToSignDeadline As Integer,
    '                                   SignatureFlow As Echosign.RecipientRole,
    '                                   UseWebIdent As Boolean,
    '                                   Optional strMessage As String = "") As String

       Debug.Print echo.SendForSigning("c:\Invoices\TestInvoice.pdf", _
                   "Doc for sign", _
                   "nospam-kallal@msn.com", _
                   30, _
                   RecipientRole_SIGNER, _
                   False, _
                   "Optional message")

    So in above we send one document to EchoSign (and print out the document ID EchoSign created).

    So not a "lot" of code is required in VBA to use this .net code. There also a few more functions such as "grab" list of documents outstanding etc.

    Feel free to contact me using either email above in the code snips (just remove the nospam- part to contact me.

    Regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Chenchen Li Monday, May 29, 2017 2:29 AM
    Friday, May 26, 2017 8:15 PM