Using this Excel table:
1.PNG
I'm trying to generate emails that look like this:
2.PNG
The emails I'm looking to create contain 1) .to 2) .cc 3) .subject 4) .body with personalized message (i.e. the body can't be HTML'd in) and 5) email signature. Below is the code that I'm using which successfully completes items 1-4 but not item 5.
Option Explicit
Sub Send_Multiple_Email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet2")
Dim OA As Object
Dim msg As Object
Set OA = CreateObject("Outlook.application")
Dim i As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
For i = 2 To last_row
Set msg = OA.CreateItem(0)
msg.To = sh.Range("A" & i).Value
msg.CC = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value & msg.body
msg.display
Next i
End Sub
I am hoping someone can help with a command or code restructuring that keeps the auto generated Outlook email signature. The line "msg.body = sh.Range("D" & i).Value & msg.body" wipes out the signature and I've tried several solutions with no success
Thanks so much in advance for any help!
Michael
Bookmarks