VBA, Insert outlook signature in vba code

Question

I have a vba code which sends automatically emails when a due date is approaching at least 7 seven days from the current date.

The problem is they when the email is sent without my outlook signature.

The code is:

Sub email()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

Sheets(1).Select
lRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 2 To lRow
toDate = Cells(i, 3)
 If toDate - Date <= 7 Then
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)

        toList = Cells(i, 4)    'gets the recipient from col D
        eSubject = "Doukementacion per  " & Cells(i, 2) & " Targa " & Cells(i, 5)
        eBody = "Pershendetje Adjona" & vbCrLf & vbCrLf & "Perfundo dokumentacionin e nevojshem per " & Cells(i, 2) & " me targa " & Cells(i, 5)

        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        .bodyformat = 1
        '.Display   ' ********* Creates draft emails. Comment this out when you are ready
        .Send     '********** UN-comment this when you  are ready to go live
        End With
  On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Cells(i, 11) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i

ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

Possible duplicate of [How to add default signature in Outlook](https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook) — niton, Oct 13 '17 at 15:49

score 1 · Answer 1 · answered Oct 13 '17 at 14:38

1

What I found helpful was to make it a HTMLBody. so this part:

With OutMail
    .To = toList
    .CC = ""
    .BCC = ""
    .Subject = eSubject
    .Body = eBody
    .bodyformat = 1
    '.Display   ' ********* Creates draft emails. Comment this out when you are ready
    .Send     '********** UN-comment this when you  are ready to go live
End With

would look like

With OutMail
    .Display 'ads the signature
    .To = toList
    .Subject = eSubject
    .HTMLBody = eBody & .HTMLBody
    '.Display   ' ********* Creates draft emails. Comment this out when you are ready
    .Send     '********** UN-comment this when you  are ready to go live
    End With

You might need to toggle events, not sure since I haven't tested with events disabled

answered Oct 13 '17 at 14:38

krib

569
4
14

This is wrong - you cannot concatenate 2 HTML documents and produce a valid HTML document - your data needs to be inserted into the HTML body at the appropriate place. – Dmitry Streblechenko Oct 13 '17 at 20:48
It works, but might be that I struck luck using this for a year or so or am I missing something in the ‘body’. Found inspiration from https://www.rondebruin.nl/win/s1/outlook/signature.htm – krib Oct 13 '17 at 20:52
It works at that link because the data being appended is not a complete HTML document with the html and head tags. In your case, you are appending data before the valid HTML document begins. If Outlook can parse that out, you are lucky. Generally, that is not the case. – Dmitry Streblechenko Oct 13 '17 at 21:08
You’re correct. Hope it helped in finding the right solution – krib Oct 13 '17 at 21:11
The right solution is to merge the two. See my answer at https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook – Dmitry Streblechenko Oct 13 '17 at 21:17
I really tried to get the right answer from your anser at https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook, but couldn'y figure i out. The truth is I do not know how to properly use VBA. It may seem lazy to you, but I would appreciate if you could insert the part of adding signature to the previous vba code. Thanks! – Gerti Ballia Oct 14 '17 at 08:40
wouldn't it be the easiest way to make the body HTML? Like: `eBody = "
Pershendetje Adjona
" & vbCrLf & vbCrLf & "
Perfundo dokumentacionin e nevojshem per " & Cells(i, 2) & " me targa
" & Cells(i, 5)` – krib Oct 14 '17 at 13:41

score 0 · Answer 2 · answered Apr 21 '20 at 18:36

If you don't have picture in your signature and can use .body , then you can just use this simplest tool in my opinion.

Sub Mail_Workbook_1()
    Dim OutApp As Object
    Dim Outmail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set Outmail = OutApp.CreateItem(0)

    On Error Resume Next

    With OutMail
         .Display
    End With

         Signature = OutMail.body

    With OutMail

        .Subject = "This is the Subject line"
        .Body = strbody & Signature
        .Send    'or use .Display

    End with

    On Error GoTo 0

       Set Outmail = Nothing
       Set OutApp = Nothing

End Sub

Have a great day

VBA, Insert outlook signature in vba code

2 Answers2

Pershendetje Adjona