Reputation: 81
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 = "[email protected]"
.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
Reputation: 66306
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