Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 28

Thread: Working with Signature Lines

  1. #11
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,317
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider


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

    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?
    Ken Puls, FCPA, FCMA, MS MVP

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

    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.

  2. #12
    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.

  3. #13
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,317
    Articles
    57
    Blog Entries
    14
    Excel Version
    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?
    Ken Puls, FCPA, FCMA, MS MVP

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

    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.

  4. #14
    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

  5. #15
    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?

  6. #16
    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 by GaryA; 2012-04-18 at 04:08 PM.

  7. #17
    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.

  8. #18
    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?

  9. #19
    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.

  10. #20
    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?

Page 2 of 3 FirstFirst 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
  •