CODEWITHSUNDEEP
excelvbaoutlook
Tomas Joe Gemine
Tomas Joe Gemine

Reputation: 81

How to include signature on email?

I have a code that sends an email too individuals on a list.

I need the user's signature to appear at the bottom of the email. I cannot get it to display.

Below is my code. 

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo Cleanup
For Each cell In Columns("M").Cells.SpecialCells(xlCellTypeConstants)
    If LCase(Cells(cell.Row, "M").Value) = "no" Then

        Set OutMail = OutApp.CreateItem(0)
        strbody = "Dear " & Cells(cell.Row, "A").Value _
          & "<br>" & "<br>" & _
          "You still have outstanding work on the Rescan Spreadsheet " & _
          " Title number:  " & Cells(cell.Row, "E").Value _
          & "<br>" & "<br>" _
          & "<A href=""\\cv-vfl-d01\dlr_office\Operational Teams\RR Scanning Team\" & _
          "Back file QA Xerox\Document Rescans\Rescans 2019"">Click here to open file location</A>"

        On Error Resume Next
        With OutMail
            .To = Cells(cell.Row, "B").Value
            .CC = "Bethany.Turner@Landregistry.Gov.uk"
            .Subject = "Re-Scan Reminder"
            .HTMLBody = strbody & .HTMLBody
            .Display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

Cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    MsgBox "Reminder Sent", vbOKOnly

End Sub

Upvotes: 0

Views: 77

Answers (1)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66316

Your code needs to call Display first - that is when Outlook inserts the default signature into an empty email.

Secondly, do not concatenate two HTML strings - they must be merged, not concatenated: in the simplest case, search for the position of the "<body" substring, find the next occurrence of the ">" character (that takes care of the "<body>" HTML elements with attributes), then insert your HTML text.

If you want to send a message without displaying it first or if you want to insert an arbitrary signature, you can use Redemption (I am its author) and its RDOSignature.ApplyTo method.

Upvotes: 1

Related Questions