Working with Signature Lines

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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
 

Attachments

  • Electronic Signatures Cover Sheet .zip
    23.2 KB · Views: 281
Last edited:

Tony Jollans

New member
Joined
Mar 21, 2011
Messages
22
Reaction score
0
Points
0
Location
Norfolk, UK
Website
www.WordArticles.com
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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 ([URL="http://www.excelguru.ca/"]www.excelguru.ca[/URL])
'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 ([URL="http://www.excelguru.ca/"]www.excelguru.ca[/URL])
'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 ([URL="http://www.excelguru.ca/"]www.excelguru.ca[/URL])
'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 = "[EMAIL="To_someone@somedomain.com"]To_someone@somedomain.com[/EMAIL]"
                .AddToRecipient = "[EMAIL="To_someoneelse@somedomain.com"]To_someoneelse@somedomain.com[/EMAIL]"
                
                '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

New member
Joined
Mar 21, 2011
Messages
22
Reaction score
0
Points
0
Location
Norfolk, UK
Website
www.WordArticles.com
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

New member
Joined
Mar 21, 2011
Messages
22
Reaction score
0
Points
0
Location
Norfolk, UK
Website
www.WordArticles.com
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

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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.
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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.
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
This is interesting... basically what you're trying to do is emulate Sharepoint workflows...

So... if I've got this right, the system basically works except that not everyone always completes the task itself in Outlook, so the admin needs to follow up and check if the user just didn't do that part?

Wouldn't it make more sense to code an email basis on the part of the admin to follow up on all delegated tasks that are incomplete and overdue?

Or am I missing something?
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
I guess you can say that. Since I am searching for the best approach to this issue and if it makes more sense to use outlook email to manage this, then I am all for it. I am no expert in this area and I appreciate your insight.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Gary,

So what code do you have now? Can you detail for us what the current process is, and what code you already have in place?
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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.

I have the following code attached to the OnClick event of a button:

Code:
Private Sub TaskEng() 

Dim appOutLook As Outlook.Application
Dim taskOutLook As Outlook.TaskItem

Set appOutLook = CreateObject("Outlook.Application")
Set taskOutLook = appOutLook.CreateItem(olTaskItem)
taskOutLook.Assign

Dim Subject_Eng As String, Path2Staging As String, Assigned_Users As String, Subject As String, Body As String

Dim BuildInst As String, ECO As String, Release_To_CM_Directory As String, Path As String, LoadInst As String, Project as String

Release_To_CM_Directory = “C:\CM”
Project = “Project”
Path = “C:\Project”

LoadInst = Path & "\" & “Load Instructions.doc”
BuildInst = Path & "\" & “Build Instructions.doc”

Path2Staging = “C:\Staging”
Assigned_Users = “Eng1@xyz.com; Eng2 @xyz.com; Eng3@xyz,com”
Subject_Eng = "Review / Update " & [Project] & " Release Package"

Body = "1. Please review SCM release package located at: " & """" & Path2Staging & """" & vbLf & vbLf
Body = Body & "2. On completion, please digitally sign/initial the document cover page(s) and/or ECO located at the following folders if locations(s)." & vbLf & vbLf
Body = Body & " Load Inst: " & """" & LoadInst & """" & vbLf
Body = Body & " Build Inst: " & """" & BuildInst & """" & vbLf
Body = Body & " ECO: " & """" & ECO & """" & vbLf & vbLf
Body = Body & " A. For New Documents:" & vbLf
Body = Body & " a. Double-click on the appropriate signature line located on the bottom of the cover page." & vbLf
Body = Body & " b. Type your full name and select Sign" & vbLf & vbLf
Body = Body & " B. For all documents, the Apvd Box located at the upper right corner of the document cover page must also be digitally initialed, If you are the Approved designee." & vbLf
Body = Body & " a. Double-click on the Apvd signature line." & vbLf
Body = Body & " b. Type your Initials and select Sign" & vbLf & vbLf
Body = Body & " C. If you need to make changes to a document that has already been digitally signed you must make a copy of the document to remove all electronic signatures, make your changes and notify SCM. All reviewers will need to re-sign the document after reviewing the new changes." & vbLf & vbLf
Body = Body & "3. When finished, please disposition/status this task in Outlook. Doing so will automatically notify SCM that you have completed this task and trigger the next release event." & vbLf

Set myDelegate = taskOutLook.Recipients.Add(Assigned_Users)
On Error Resume Next
With taskOutLook
.Subject = Subject_Eng
.Body = Body
.ReminderSet = True
.ReminderTime = DateAdd("n", 5, Now) ' Set to remind us 2
' minutes from now.
.DueDate = DateAdd("n", 5, Date) ' Set the due date to
' 5 minutes from now.
.StartDate = Date
.Categories = "Release Package"
.Companies = ""
.Importance = olImportanceNormal
.ReminderPlaySound = True
'add the path to a .wav file on your computer.
.ReminderSoundFile = "C:\windows\media\ding.wav"
.Display
End With

End Sub
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
Ken, I will look into Sharepoint workflows to manage the signature collection process of documents. Thanks for the suggestion. Do you know if Sharepoint can manage PDF files that contain signature lines as well?
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
I am soliciting ideas to implement a Word Addin (I have never done this before) that can manage existing Signature lines in the active Word document.

The Addin would do the following:
1. Delete all signed signatures in the active document, while retaing the signature lines.
2. Trust all invalid signatures in a signed Document.
3. List signatories that have or have not signed the document and the date it was signed. Information to be displayed a window and option to copy to clipboard and output to a text file.
4. Get count of signature lines signed in active document v/s unsigned.
5. Create a new copy of the active document. keeping all unsigned signature lines without destroying the originally signed document.

Any suggestions welcome.
 
Last edited:

Tony Jollans

New member
Joined
Mar 21, 2011
Messages
22
Reaction score
0
Points
0
Location
Norfolk, UK
Website
www.WordArticles.com
I have been having some trouble with this! Sometimes things work and sometimes they don't and I'm having problems working out why. Running a beta version of Office on a beta version of Windows may have something to do with it but there's more to it than that. Anyway ...

There seem to be some circumstances where the BeforeSave event isn't fired but I think using that event is over-complicating things anyway, and using Open and Close should be good enough.

This is just proof of concept code (in the template). If it works it can be extended ...

Code:
Private Sub Document_Open()

    Dim Sig As Office.Signature
    Dim ndx As Long
    
    If ActiveDocument Is Me Then Exit Sub
    
    With ActiveDocument
        For Each Sig In .Signatures
            ndx = ndx + 1
            .Variables.Add "Sig" & ndx & "Signer", Sig.Setup.SuggestedSignerLine2
            .Variables.Add "Sig" & ndx & "SignedBy", Sig.Signer
            .Variables.Add "Sig" & ndx & "Valid", Sig.IsValid
        Next
        .Variables.Add "SignatureCount", .Signatures.Count
        .Saved = True
    End With
    
End Sub

Private Sub Document_Close()
    
    Dim Var     As Word.Variable
    Dim Sig     As Office.Signature
    Dim Sigs    As Collection
    Dim ndx     As Long
    
    With ActiveDocument
        Set Sigs = New Collection
        For ndx = 1 To .Variables("SignatureCount")
            Sigs.Add ndx, .Variables("Sig" & ndx & "Signer")
        Next
        
        For Each Sig In .Signatures
            ndx = 0
            On Error Resume Next
                ndx = Sigs(Sig.Setup.SuggestedSignerLine2)
            On Error GoTo 0
            If ndx = 0 Then
                MsgBox "New Signature for " & Sig.Setup.SuggestedSignerLine2 & vbCr & _
                       " provided by " & Sig.Signer & vbCr & _
                       "It is " & IIf(Sig.IsValid, "", "NOT ") & "valid."
            Else
                If .Variables("Sig" & ndx & "Signedby") = Sig.Signer _
                And .Variables("Sig" & ndx & "Valid") = Sig.IsValid Then
                    ' no change
                Else
                    MsgBox "Signature for " & Sig.Setup.SuggestedSignerLine2 & " changed."
                End If
            End If
        Next
        
        For Each Var In .Variables ' there should always be at least the count
            If Right(Var.Name, 6) = "Signer" Then
                MsgBox "Signature for " & Var.Value & " deleted."
            End If
        Next
    End With

End Sub

What this code, in the template, should do is:

On document open, save a lot of document variables with details of the signatures (at the moment the code assumes here are no other document variables in the document). Then on document close compare the saved variables with the new state of affairs - note that I'm not sure I've caught every possibility here. I just throw out message boxes where you would want e-mails. The variables do not get saved with the document - I really can't say whether this is relying on a bug in Word but it has always been, and remains, this way as far as I can tell.

In the open routine, I check that it is a document, not the template, being opened. In the document close routine the same code seems to screw up - in Word 2007 as well and some more testing is needed.

I note you are now asking about an AddIn and I'll think about that.
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
Hi Tony, many thanks for your feedback.

I inserted the code in a new module of the Template and saved. The template opens a new instance of the document and closes without saving the template as stated.

How would I look at the document variables with the detail of the signatures, initially and after a change? Can we pipe them out to an external text file for viewing?
 

Tony Jollans

New member
Joined
Mar 21, 2011
Messages
22
Reaction score
0
Points
0
Location
Norfolk, UK
Website
www.WordArticles.com
Sorry, I should have said, the code for Open and Close events needs to go in the ThisDocument module.

The code is pretty basic and just grabs enough (I hope) to identify the affected signatures, but you could save whatever you wanted about the initial state of things and gather whatever you needed about the final state, if you were generating an e-mail, or otherwise wanted to write the data out.
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
Oh, I did do that initially but received a runtime error '5825' when closing the document, so I moved it to a module instead. It make sense that the events won't fire unless in the Thisdocument module.

Debug takes it to the following line in the code: For ndx = 1 To .Variables("SignatureCount"). Googling the error message: http://support.microsoft.com/kb/212572.

Any suggestions?
 
Top