CODEWITHSUNDEEP
vbaexcelemailoutlook
Emily
Emily

Reputation: 11

Insert text body and excel table body in email using VBA

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

Upvotes: 1

Views: 12876

Answers (1)

mmurrietta
mmurrietta

Reputation: 191

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

Upvotes: 2

Related Questions