Attaching Signature to Email in Lotus Notes

[JHarlem]

So I stumbled across this thread yesterday while searching for ways to automatically send emails using Lotus Notes. The first solution posted by John Davis is almost perfect for my needs, however there is one thing that is still giving me a problem; my signature will not attach.

On Lotus Notes, I automatically have my signature to append to an email, however when I am running Johns script at the bottom it is replaced with the file path of my signature (i.e. C:\Program Files\lotus\notes\data\sig.htm), and when I comment out the part where strSignature is mentioned there is no signature.

While this isn't the biggest problem in the world, it still will be an issue to the users of my Macro, and if possible I want to reduce the likelihood of any bug/error/oversight occurring. My code is below and judging by the results of my Google search it should work, does anyone know why it isn't?

Sub mySub()
    Dim x As Integer
    Dim UserName As String
    Dim MailDbName As String
    Dim Recipient As Variant
    Dim Maildb As Object
    Dim MailDoc As Object
    Dim AttachME As Object
    Dim Session As Object
    Dim stSignature As String
    With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    ' Open and locate current LOTUS NOTES User
    For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
'        If Range("A" & x) = "Overdue" Then
        If Range("A1") = "Overdue" Then
            Set Session = CreateObject("Notes.NotesSession")
            UserName = Session.UserName
            MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
            Set Maildb = Session.GetDatabase("", MailDbName)
                If Maildb.IsOpen = True Then
                Else
                    Maildb.OPENMAIL
                End If
                ' Create New Mail and Address Title Handlers
                Set MailDoc = Maildb.CREATEDOCUMENT
                'Set MailDoc = Maildb.ComposeDocument
                MailDoc.Form = "Memo"
                'stSignature = Maildb.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)
                stSignature = "C:\Program Files\lotus\notes\data\sig.htm"
            ' Select range of e-mail addresses
            Recipient = Worksheets("Sheet1").Range("B" & x).Value
            'Recipient = Worksheets("Sheet1").Range("B1").Value
            MailDoc.SendTo = Recipient
            MailDoc.Subject = "test macro results"
            MailDoc.Body = "This is a test email." & vbCrLf & vbCrLf & stSignature
            MailDoc.SaveMessageOnSend = True
            MailDoc.PostedDate = Now()
            On Error GoTo errorhandler1
            MailDoc.SEND 0, Recipient
            Set Maildb = Nothing
            Set MailDoc = Nothing
            Set Session = Nothing
            .ScreenUpdating = True
            .DisplayAlerts = True
errorhandler1:
            Set Maildb = Nothing
            Set MailDoc = Nothing
            Set Session = Nothing
            End If
    Next x
    End With
End Sub

 

[John_w]

On Lotus Notes, I automatically have my signature to append to an email, however when I am running Johns script at the bottom it is replaced with the file path of my signature (i.e. C:\Program Files\lotus\notes\data\sig.htm), and when I comment out the part where strSignature is mentioned there is no signature.

I suspect the John Davis code only works for a text signature, so the line:

stSignature = Maildb.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)

reads the text of the signature.

However, as indicated by "C:\Program Files\lotus\notes\data\sig.htm", your signature is a HTML signature contained in a .html/.htm file. Therefore the above line simply reads this file path and name. To include the actual signature contained in sig.htm you would have to read and parse the contents of that file (using VBA file input statements, or better still HTMLDocument methods) and include the signature in the email. All this can get quite complicated.

An easier approach is to create the email using NotesUIWorkspace.ComposeDocument (instead of NotesDatabase.CreateDocument), which means the signature is automatically included, whether it is a text, HTML or image signature. Try adapting the code here - http://www.mrexcel.com/forum/excel-...-preparation-excel-worksheet.html#post3391183 - which uses this method.