PDA

View Full Version : Working with Signature Lines



GaryA
2012-04-07, 01:07 AM
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
2012-04-10, 08:43 PM
Hi Gary,

This thread is related to this one (http://www.excelguru.ca/forums/showthread.php?828-Outlook-Task-Workflow), 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
2012-04-11, 10:23 PM
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
2012-04-12, 02:24 PM
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
2012-04-13, 05:39 AM
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 (http://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 (http://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 (http://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 (http://www.excelguru.ca/content.php?249-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
2012-04-13, 12:07 PM
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
2012-04-13, 12:14 PM
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
2012-04-13, 08:28 PM
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
2012-04-13, 08:48 PM
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
2012-04-13, 09:10 PM
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
2012-04-14, 09:51 PM
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
2012-04-14, 10:22 PM
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
2012-04-17, 06:06 PM
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
2012-04-17, 09:21 PM
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:


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
2012-04-18, 04:50 PM
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
2012-04-18, 05:04 PM
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.

Tony Jollans
2012-04-18, 09:17 PM
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 ...


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
2012-04-19, 07:38 PM
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
2012-04-19, 08:32 PM
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
2012-04-19, 09:10 PM
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?

Tony Jollans
2012-04-20, 10:28 AM
Hi Gary,

Sorry about the problems - let me try to give some of the horrible detail.

Just to explicitly rule out the situation (this can always be addressed later) the code does not deal with the case of a new document being created based on the template - and this may cause some problems in testing.

The Document_Open routine in the Template should run when either an existing document based on the template is opened, or when the template itself is opened for editing. All should be fine when it's a document being opened.

Often (as in this case - where running the open code would mean taking explicit action later to avoid the variables being saved) the code is not relevant when the template itself is being opened for editing and, so, I have explicitly bypassed it with this line


If ActiveDocument Is Me then Exit Sub

The Document_Close routine runs when a document based on the template, or the template itself is closed. Again, all should be well when it's a document (and the Document_Open code has run when it was opened).

When the template has been opened for editing and is then closed, the Close code runs and I would like to begin with the same escape line as above, BUT ...

What appears to be a bug with documents based on your template, and, I am guessing, with such locked documents in general is that, in the Close event (and maybe elsewhere) ActiveDocument is returning the template instead of the document. Having a check for the active document being the template, thus, means that the code doesn't ever run and so I have had to take that line out.

One effect of this is that when you have edited the template, the Close code tries to run, and fails. This is annoying but you can just cancel the code when this happens. Something better clearly needs to be found for a live situation!

One final point - as I said above I have explicitly excluded the creation of new documents. When a new document is created, the Open event code does not run (the Document_New event runs instead). This means that the same error can occur when the Close event runs but the Open hasn't run. Again, in testing, this an be skipped over.

Sorry it's rather a mess. I will try to produce something more solid later.

Ken Puls
2012-04-20, 05:31 PM
Guys, I'm still paying attention here, but not being a Word coder, I'm going to let Tony have run with this until we get to the email stage.

I still think, though, that if you actually get the Sharepoint workflows set up in your environment, they'll solve this issue for you. In fact, according to what I've been told, this is one of the major areas that they are supposed to really shine at. I don't actually have Sharepoint, and I'm sure it's not as easy as what I've been led to believe, but from what I've heard they work like this: Email is sent from whomever with "follow up" schedule. If reply not recieved in x hours/days/weeks etc, then follow up emails are automatically sent.

Again, probably not totally as easy at that, but at the end of the day it sounds like Sharepoint may be the right product for this situation. You're going to need to do a lot of work to hack together a custom solution.

At the same time, if you'd rather have custom, we'll do our best to help, I just want you to be aware of all the options at your disposal. :)

GaryA
2012-04-20, 08:02 PM
Tony, thanks for the clarification with the existing code, I need to mull it over some more to get the complete picture.

Ken, it turns out that we do use Sharpoint, but on a remote server. It may take a while to gain access, create a Sharepoint Site and setup workflows to manage the process. One drawback I see immediately is a lack of local control. For now, I would still like to persue a custom solution.

Thanks again for all your continued help and support. Once again, you guys offer such great feedback.

Ken Puls
2012-04-21, 12:24 AM
Fair enough. :)

Tony Jollans
2012-04-26, 05:40 PM
I haven't forgotten this, and do hope to post a working template soon, but it's causing me no end of headaches! Things that I expect to work, just don't and I can't pin down exactly why (I think there are probably several bugs in a little used part of Word). The more I find out, though, the more I think Ken is right and that SharePoint Workflows are the thing to use.

GaryA
2012-06-29, 08:01 PM
Just checking in, anymore thought on this issue? Many Thanks!

Ken Puls
2012-07-14, 06:55 AM
Sorry, Gary, without access to Sharepoint and lacking Tony's Word knowledge, I don't think I'm going to be much help to you on this one. :(

nabilarubaiya
2013-01-25, 01:08 PM
You can post many signature in same post.