Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 28

Thread: Working with Signature Lines

  1. #1

    Working with Signature Lines



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have created a Word Document that contains multiple Signature lines embedded. This document is located in a central location for signatories to review and sign.

    I would like to programmatically sent an email to myself identifying the User that has just signed the document.

    Any recommendations on how to implement this?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Hi Gary,

    This thread is related to this one, correct?

    Can you upload a sample? I've never worked with signatures in a Word document, but as long as we can get the trigger, the email part is easy.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Yes, this is related. I have uploaded a sample Word Template that contains the signature fields.

    If you double-click on a signature field you may be prompted to create a digital signature, if you do not already have one. I have created a digital certificate on my local PC in stead of using third-party certificate authorities (CAs)

    Procedure to create a Digital Certificate (put this link in your browser <office.microsoft.com/en-us/word-help/get-or-create-your-own-digital-signature-HA010099764.aspx>)


    Thanks
    Attached Files Attached Files
    Last edited by GaryA; 2012-04-11 at 09:30 PM.

  4. #4
    This is a new one for me, too. I've just had a quick look and there is no event.

    It should be possible to check on opening what signatures already exist and, on closing, what ones then exist and if there is a new one do whatever you want.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Hey Tony, glad to see you here!

    I'm wondering if you might help me a bit with getting Gary an answer. I've tried to adapt what I would do for Excel, but Word works a little differently, of course.

    My thought was to create a custom document property to hold the opening signature count, then check against that count upon closing. If the count has increased (I'm not sure if you can have more than one signature, but I assume so) then email the file.

    Here's the code I mocked up:
    Code:
    Public Property Let SigCount(lng As Long)
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Sets the SigCount property to requested value
        Dim DocProps As DocumentProperties
        'Save Version value in a customdocument property
        On Error Resume Next
        ThisDocument.CustomDocumentProperties("SigCount") = lng
        'If customdocument property did not exist, create it
        If Err.Number <> 0 Then
            Set DocProps = ThisDocument.CustomDocumentProperties
            DocProps.Add Name:="SigCount", _
                    LinkToContent:=False, _
                    Type:=msoPropertyTypeNumber, _
                    Value:=lng
        End If
        On Error GoTo 0
    End Property
    
    Public Property Get SigCount() As Long
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Return Version from customdocument property
        Dim DocProps As DocumentProperties
        
        'Check the opening signature count
        On Error Resume Next
        SigCount = ActiveDocument.CustomDocumentProperties("SigCount")
        
        'If customdocument property did not exist, create it
        If Err.Number <> 0 Then
            Set DocProps = ThisDocument.CustomDocumentProperties
            DocProps.Add Name:="SigCount", _
                    LinkToContent:=False, _
                    Type:=msoPropertyTypeNumber, _
                    Value:=0
        End If
        On Error GoTo 0
    End Property
    
    Private Sub Document_Close()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Sets the SigCount property to requested value
       
        Dim oEmail As New clsOutlookEmail
        
        With ThisDocument
            If .SigCount < .Signatures.Count Then
            
                'Update signature count
                .SigCount = .Signatures.Count
                
                With oEmail
                    'Add a recipient
                    .AddToRecipient = "To_someone@somedomain.com"
                    .AddToRecipient = "To_someoneelse@somedomain.com"
                    
                    'Set the subject
                    .Subject = "File has been signed"
                    
                    'Set the body
                    .Body = "Hi there" & vbNewLine & vbNewLine & _
                    "It looks like this file was signed."
                    
                    'Attach the file
                    'Not sure if this will work as document has not yet been saved...
                    .AttachFile = ThisDocument.FullName
                    
                    'Preview the email (or use .Send to send it)
                    .Preview
                End With
            
            End If
        End With
        
        'Release the email object
        Set oEmail = Nothing
        
    End Sub
    It does also require pulling in my Easy Outlook Email Integration class module for the email part too.

    Now, the questions...
    • Word has such a lack of events! All I could find to trigger this would be a Document_Close event (really wanted a Document_BeforeSave)
    • How do you force the file created from the template to be a docm file so that it contains the code so that it will fire next time it's opened?
    • Excel has a SaveCopyAs method, which allows you to save a temp copy to attach to the email, then you can kill it later. Do you think just adding the current document will work, or... do we need to force a SaveAs to a temp path, then back to the original path, then clean up? Sounds a little hairy... especially if the document hasn't yet been saved.
    Gary, can you confirm, will a document ever get more than one signature?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Hi Ken,

    Glad to be here!

    I've taken a bit more of a look at this and, right now, don't know what can be done. It isn't as straightforward as one might like it to be. Code does not seem to be fired at all the right places and I'm still looking for a reliable trigger.

    I have other things to do, so just a quick few answers to your questions at the moment - but I will return.

    Yes, you can have lots of signatures. Even before signature the document is effectively locked, though, and as soon as you have any signatures, any change to the document invalidates them, so your options are limited. You can't add Properties, but you can add Document Variables - they do not flag the document as changed and they are not saved with the document, so they may be useful here.

    Now, those answers :-) ...
    • The Document_BeforeSaveEvent is an Application Event
    • You don't need the document to be macro-enabled, so long as the template is - of course this does rely on every user having the template (and there is no mechanism in Word to stop a document 'working' if macros aren't working or available). If the code has to be in the document then it will be a .docm before any signatures are collected so the issue of making it so is not a concern - you just have to ensure (via operational procedures and 'manual' control) that the macros run.
    • Oh, how I wish Word had Excel's SaveCopyAs!
    • Is there, actually, a requirement to send the document? Saving a copy - even in Excel, I suspect - will invalidate and/or remove existing signatures (the copy hasn't been signed). I would have thought a descriptive e-mail would be sufficient.

  7. #7
    Gary,

    This is all new stuff for me! Just wondering really - is it necessary to be doing anything in Word? Can you give a few more details of the document creation process using Outlook Tasks (from Access or anywhere else) is not something I have done.

  8. #8
    Wow, thanks for the excellent ongoing feedback and fast responses, this is very much appreciated.

    Yes, the document will be signed by more than one person. Unfortunately, once signed, the document is locked. If you save it as another file, it wll clear all signatures. However, If you copy it somewhere else, the signatures are retained.

  9. #9
    This is my experience with signatures too. No, there is no requirement to save a copy. All signatories will go to a specific location to sign. I am looking for a way to automatically be notified that the signatory has indeed signed the word document, without having to navigate and open the document to see who has signed. We will be handling hundreds of document like this so you can see why I would like to automate the process. Yes, Notification could be a descriptive email.

  10. #10
    Glad you asked! I am using MS Access to generate indvidual Outlook task assignments to the various signatories. The signatories respond to the outlook task by accepting the task request in outlook. Their acceptance of the task in outlook automatically generates a response email back to the Task Assignor indicating that they have accepted the task. Further, once they have reviewed and signed the documents, they would also revisit the task and complete it. Their completion of the task automatically triggers another response email back to the Task Assignor indicating that they have completed the task. The flaw in this scenario is that tasks are not always followed up to completion by the signatories in outlook, resulting in the Task Assignor having to go back to the individual documents to verify signatures, hence the need to see if it could automatically be done in Word.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •