VBA Excel Outlook Email Body Formatting

vba excel email outlook

29,368

You need to apply HTML format to the body of the email:

Dim body_ As String
    body_= "<p> Hello </p>" & _
           "<p> This is a line </p>" & _
           "<p> This is another line </p>" & _
           "<p> This is yet another line. </p>"

.BodyFormat = olFormatHTML
.HTMLBody = "<html><head></head><body>" & body_ & "</body></html>"

Update

Option Explicit

Public Sub sendMail(ByVal mail As String, name As String, Msht As Worksheet, CCmail As Integer, CCperson As String)
    On Error GoTo ErrorTrap

    Dim applOL As Outlook.Application
    Set applOL = New Outlook.Application

    Dim miOL As Outlook.MailItem
    Set miOL = applOL.CreateItem(olMailItem)

    Dim recptOL As Outlook.Recipient
    Set recptOL = miOL.Recipients.Add(mail)
        recptOL.Type = olTo

    Dim mailSub As String
        mailSub = Msht.Range("J2")

    Dim mailbody As String
        mailbody = "<p><u>" & Msht.Range("L2").Value & "</u></p>" & _
                   "<p><b>" & Msht.Range("M2").Value & "</b></p>" & _
                   "<p>" & Msht.Range("N2").Value & "</p>"

    If CCmail = 1 Then
        Set recptOL = miOL.Recipients.Add(CCperson)
        recptOL.Type = olCC
    End If

    Dim tempPath As String
        tempPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.name

    With miOL
        .Subject = mailSub
        .BodyFormat = olFormatHTML
        .HTMLBody = "<html><head></head><body>" & mailbody & "</body></html>"
        .Attachments.Add tempPath
        .send
    End With

    ActiveWorkbook.Close Savechanges:=True

Leave:
   On Error GoTo 0
   Exit Sub

ErrorTrap:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

29,368

Author by

Rafael Osipov

Updated on November 01, 2020

Comments

Rafael Osipov over 3 years

I have useform that sends emails automatically. I want to change the body of the email - some of it will be based on a cell with text so it could be dynamic and some will be fixed in the code. for now - in runs me an error of object required, i'll be thankful for help. I want every line in the body of the email to be separate.

Sub sendMail(ByVal mail As String, name As String, Msht As Worksheet, CCmail As Integer, CCperson As String)
    Dim applOL As Outlook.Application
    Dim miOL As Outlook.MailItem
    Dim recptOL As Outlook.Recipient
    mailSub = Msht.Range("J2")
    mailbody = Msht.Range("L2")
    Set applOL = New Outlook.Application
    Set miOL = applOL.CreateItem(olMailItem)
    Set recptOL = miOL.Recipients.add(mail)
    recptOL.Type = olTo
    If CCmail = 1 Then
        Set recptOL = miOL.Recipients.add(CCperson)
        recptOL.Type = olCC
    End If
    tempPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.name
    With miOL
        .Subject = mailSub
        .Body = "bla bla" & "bla bla bla" & mailbody.Font.Underline & Msht.Range("M2").Font.Bold & Body = Msht.Range("N2")
        .Attachments.add (tempPath)
        .send

    End With
    ActiveWorkbook.Close Savechanges:=True
    Set applOL = Nothing
    Set miOL = Nothing
    Set recptOL = Nothing
End Sub

Rafael Osipov over 6 years

wow thanks, but if i want some will be bold and some refer to specific text in cells.
Kostas K. over 6 years

For bold font you need to use the HTML bold tag <b>text</b>. As for the cell value use a simple string concatenation e.g. "This is " & .Cells(1,1).Value.
Rafael Osipov over 6 years

its not working for me when I change it to what I need, can you please write it for me as it should be?
Rafael Osipov over 6 years

in L2 something that i want to be a dynamic string with under line, M2 is a string that i want to do bold and N2 is just another line with no specific format and before that a regualar 2 strings for opening
Kostas K. over 6 years

See updated answer. You should check the recptOL field - it's set twice if CCmail = 1. FYI, asking for "ready made" code will not help you learn. :)