Insert text body and excel table body in email using VBA

Question

The ideal format of the email I wish to automate. Certain parts blacked out for privacy.

I am trying to create an email template for business use that can be sent using VBA because the ultimate goal is that the user can only fill in the blanks via userform therefore the text remains unchanged. I already have the userform, and have coded in such a way that the proper blanks are filled in in the text portion and are included in the email body, however I have not figured out how to include the table as well.

Here is what I have so far as my attempt to add the table:

Sub SendEmail(what_address As String, subject_line As String, mail_body As String, claim_info As Range)

Dim olApp As Outlook.Application Set olApp = CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.HTMLBody = RangeToHtml.claim_info
olMail.Send

End Sub Sub SendClaimsEmail()

Dim mail_body_message As String
Dim tracking_number As String
Dim amount_paid As String
Dim date_paid As String
Dim payment_due As String
Dim claim As Range

Set claim = Nothing
On Error Resume Next
'Only send the visible cells in the selection.
Set claim = Selection.SpecialCells(xlCellTypeVisible)
Set claim = Sheets("Sheet1").RangeToHtml("B2:C9").SpecialCells(xlCellTypeVisible, xlTextValues)
On Error GoTo 0

mail_body_message = Sheet1.Range("A1")
tracking_number = Sheet1.Range("G2")
amount_paid = Sheet1.Range("G3")
date_paid = Sheet1.Range("G4")
payment_due = Sheet1.Range("G5")
mail_body_message = Replace(mail_body_message, "replace_tracking", tracking_number)
mail_body_message = Replace(mail_body_message, "replace_amountpaid", amount_paid)
mail_body_message = Replace(mail_body_message, "replace_datepaid", date_paid)
mail_body_message = Replace(mail_body_message, "replace_pmtdueto", payment_due)



Call SendEmail("[email protected]", "Subject Line", mail_body_message, claim)

MsgBox "Complete!"

End Sub

mmurrietta mmurrietta · Accepted Answer · 2017-07-07T15:46:39

I believe your problem lies in trying to combine olMail.Body and olMail.HTMLBody in the same message. From my experience you need to choose one and the table will not work with olMail.Body.

I suggest converting mail_body to valid HTML and appending to olMail.HTMLBody:

Sub SendEmail(what_address As String, subject_line As String, mail_body As String, claim_info As Range)

Dim olApp As Outlook.Application Set olApp = CreateObject("Outlook.Application")
Dim olApp As Outlook.Application Set olApp = 
CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = what_address
olMail.Subject = subject_line
'assuming your mail_body uses vbLf for linefeeds you can just do this
mail_body = "<p>" & replace(mail_body,vblf,"</br>") & "</p>"
olMail.HTMLBody = mail_body & "</br>" & RangeToHtml.claim_info
olMail.Send
End Sub

Insert text body and excel table body in email using VBA

1 Answers