I have a macro that I use to generate email to be sent based on the date. Originally the macro would insert my outlook signature at the end of the email now however it does not I would like to avoid having to rewrite the entire process any help is greatly appreciated.
Sub cargo()
'generates an email to a recipient based on the date
Sheets("INSURANCE & AUTHORITY").Select
Dim rngCell As Range
Dim rngMyDataSet As Range
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim EmailRecipient As String
Dim Signature As String
Application.ScreenUpdating = False
With ActiveSheet
If .FilterMode Then .ShowAllData
Set rng = Range("Offset( A1, 0, 0, COUNTA(C:C), 1)")
End With
For Each rngCell In rng
If Cells(rngCell.Row, "U").Value > 0 Then
ElseIf Cells(rngCell.Row, "J") >= Evaluate("Today() ") And _
Cells(rngCell.Row, "J").Value <= Evaluate("Today() +30") And _
Cells(rngCell.Row, "E").Value = Evaluate("TRUE") Then
Cells(rngCell.Row, "U").Value = Date
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Dear " & Cells(rngCell.Row, "B").Value & vbNewLine & vbNewLine & "According to our records your " & Range("J1").Value & " is due for renewal on " & Cells(rngCell.Row, "J").Value & vbNewLine & _
"Could you please ensure you send us a copy of your renewal prior to this date."
Signature = "C:\Documents and Settings\" & Environ("rmm") & _
"\Application Data\Microsoft\Signatures\rm.htm"
EmailSendTo = Cells(rngCell.Row, "A").Value
EmailSubject = ActiveSheet.Range("J1").Value
EmailRecipient = Cells(rngCell.Row, "C").Value
On Error Resume Next
With OutMail
.To = EmailSendTo
.CC = ""
.BCC = ""
.Subject = EmailSubject
.body = strbody
'the following can be changed to .send to automatically send email
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End If
Next rngCell
Application.ScreenUpdating = True
End Sub
Bookmarks