VBA Email placed below auto signature

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
Every week, I send out several emails with updated reports and hyperlinks. To save time, I created a macro to write and send these emails for me. The generated email is exactly what I want when I display the message. However, I noticed that after the email is sent, my automatic signature appears at the top of the message. How can I reposition the signature to appear at the bottom of the message after it is sent as it does when I create an email through normal methods?

Below is the code I use.

VBA Code:
Sub send_VPemails_complete()

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long, source_file, strbody As String
lr = Range("A20").End(xlDown).Row
Set Mail_Object = CreateObject("Outlook.Application")

For i = 20 To lr
    
    source_file = "W:\Accounting\Financial Reporting\2021 Financial Statements\01.2021\F - Cost Center Statements & Job Performance Reports\F402 Job Analysis\Portfolio\VP\" & Cells(i, 7) & "\" & Cells(i, 4)
    
    strbody = Cells(i, 2) & "," & "<br>" _
        & "<br>" _
        & "Attached is a PDF summarizing the weekly change (" & Cells(i, 5) & " vs " & Cells(i, 6) & ") for all " & Cells(i, 8) & " active projects." & "<br>" _
        & "<br>" _
        & "Please contact me with any questions." & "<br>" _
        & "<br>" _
        & "Thanks,"
        
    With Mail_Object.CreateItem(o)
        .Subject = Cells(i, 3)
        .To = Cells(i, 1)
        .HTMLBody = .HTMLBody & strbody
        .Attachments.Add source_file
        .Send
    End With
Next i
    MsgBox "E-mails successfully sent", 64
    Application.DisplayAlerts = False

End Sub


Thanks!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
try

VBA Code:
    With Mail_Object.CreateItem(o)
        .Subject = Cells(i, 3)
        .To = Cells(i, 1)
        .HTMLBody = strbody & .HTMLBody
        .Attachments.Add source_file
        .Send
    End With
 
Solution

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
try

VBA Code:
    With Mail_Object.CreateItem(o)
        .Subject = Cells(i, 3)
        .To = Cells(i, 1)
        .HTMLBody = strbody & .HTMLBody
        .Attachments.Add source_file
        .Send
    End With
This works! But why does switching strbody & .HTMLBody send the signature to the bottom?
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
.HTMLBody is a compete web page with the signature as its content before you then add strbody.

The best thing to do is send yourself an email using just .HTMLBody =.HTMLBody. Right click on your received email and select View Code
You will see a complete web page coded with the signature included as the content of the page.

Then do the same adding strbody in both positions and see where Outlook decides to add the strbody content to the mail in relation to the signature each time.

Hopefully that will clarify what is going on behind the scenes.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,088
Messages
5,628,599
Members
416,327
Latest member
Chimay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top