Working with Signature Lines

[GaryA]

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?

[Ken Puls]

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.

[GaryA]

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

[Tony Jollans]

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.

[Ken Puls]

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:

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?

[Tony Jollans]

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.

[Tony Jollans]

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.

[GaryA]

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.

[nabilarubaiya]

You can post many signature in same post.