Working with Signature Lines

[Ken Puls]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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?