CODEWITHSUNDEEP
excelvbaemailoutlookformatting
Aubrey DeVine
Aubrey DeVine

Reputation: 5

How Can I Format this VBA Email

How can I add bold, underline, text color, and other text formatting to this email?

Sub Send_CPR_Expiration_Sites()
    
    Dim iCounter As Integer
    Dim Dest As Variant
    Dim SDest As String
    Dim OutApp As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    
    ' Create a new Outlook object
    For iCounter = 4 To WorksheetFunction.CountA(Columns(1))
    
    Set OutApp = CreateObject("Outlook.Application")
    Set objOutlookMsg = OutApp.CreateItem(olMailItem)
    
    
    ' Subject
    strSubj = "Immediate Action Required: Out of Compliance for "
    
    On Error GoTo dbg
    ' Create a new item (email) in Outlook
    
    strbody = ""
    SiteLead = Cells(iCounter, 41).Value
    SafetyR = Cells(iCounter, 42).Value
    SafetySR = Cells(iCounter, 43).Value
    SafetySS = Cells(iCounter, 44).Value
    SiteCode = Cells(iCounter, 6).Value
    
    'Make the body of an email
    strbody = "Dear " & SiteCode & " Team," & vbCrLf
    strbody = strbody & vbCrLf
    strbody = strbody & "blah blah blah" & vbCrLf
    strbody = strbody & vbCrLf
    strbody = strbody & "Let us know if you have any questions. Thank you!"
    strbody = strbody & vbCrLf
    
    objOutlookMsg.To = SiteLead
    objOutlookMsg.CC = SafetyR & ";" & SafetySR & ";" & SafetySS
    objOutlookMsg.Importance = olImportanceHigh
    objOutlookMsg.Subject = strSubj & SiteCode
    
    objOutlookMsg.BodyFormat = 1 ' 1–text format of an email, 2-HTML format
    
    objOutlookMsg.Attachments.Add "C:\Users"
    objOutlookMsg.Attachments.Add "C:\Users"
    
    objOutlookMsg.Body = strbody
    objOutlookMsg.Display
    
    Next iCounter
    
    dbg:
    
    'Display errors, if any
    If Err.Description <> "" Then MsgBox Err.Description
    
    Set objOutlookMsg = Nothing
    Set OutApp = Nothing
    
End Sub

I updated the code to this, but it is still only pulling the text inside the quotations instead of formatting the text. I'm not sure what is wrong! I appreciate the help!

They want me to add more detail to my post because it is mostly code so I am typing to fill in space. Not sure what else to fix in my code. Let me know what you think

Sub Send_CPR_Expiration_Sites()
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem, html As String

' Create a new Outlook object
For iCounter = 4 To Cells(Rows.Count, 1).End(xlUp).Row
Set OutApp = CreateObject("Outlook.Application")
Set objOutlookMsg = OutApp.CreateItem(olMailItem)

' Subject
strSubj = "Immediate Action Required: Out of Compliance for "
On Error GoTo dbg

' Create a new item (email) in Outlook
strbody = ""
SiteLead = Cells(iCounter, 41).Value
SafetyReg = Cells(iCounter, 42).Value
SafetySubReg = Cells(iCounter, 43).Value
SafetySpec = Cells(iCounter, 44).Value
SiteCode = Cells(iCounter, 6).Value

'Make the body of an email
strbody = "<b> Dear </b>" & SiteCode & " Team," & vbCrLf
strbody = strbody & vbCrLf
strbody = strbody & "<p><b> Your site blah blah </b>  blah blah" & vbCrLf
strbody = strbody & vbCrLf
strbody = strbody & "<u> blah blah </u>" & vbCrLf
strbody = strbody & vbCrLf
strbody = strbody & "blah blah blah" & vbCrLf
strbody = strbody & vbCrLf
strbody = strbody & "Let us know if you have any questions. Thank you!"
strbody = strbody & vbCrLf
objOutlookMsg.To = SiteLead
objOutlookMsg.CC = SafetyReg & ";" & SafetySubReg & ";" & SafetySpec
objOutlookMsg.Importance = olImportanceHigh
objOutlookMsg.Subject = strSubj & SiteCode
objOutlookMsg.Body = strbody
objOutlookMsg.BodyFormat = 2
'objOutlookMsg.HTMLBody = "<html><head></head><body>" & mailbody & "</body></html>"
objOutlookMsg.Attachments.Add "C:\Users
objOutlookMsg.Attachments.Add "C:\Users"
objOutlookMsg.Display
Next iCounter

dbg:
'Display errors, if any
If Err.Description <> "" Then MsgBox Err.Description


Set objOutlookMsg = Nothing
Set OutApp = Nothing

End Sub

Upvotes: 0

Views: 3128

Answers (2)

Courtney Foster
Courtney Foster

Reputation: 383

You must use either olFormatRichText or olFormatHTML to format the body of your message. I would recommend using HTML. You can then construct an HTML string and reference that as your message.

This post gives an excellent example for you: VBA Excel Outlook Email Body Formatting

P.S. Sorry. I don't usually read comments because they are typically unhelpful. I only just now saw the comments before. Did you set this line as in the example?:

.HTMLBody = "<html><head></head><body>" & mailbody & "</body></html>"

You must set the HTMLbody property for html formatting, NOT the .body property. You can also put the <html></html> tags in the message body itself instead of wrapping it like the example. The <head></head> tags are optional.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166835

Here's a basic example:

Sub Example()
    
    Dim OutApp As Outlook.Application
    Dim msg As Outlook.MailItem, html As String
    
    Set OutApp = CreateObject("Outlook.Application")
    Set msg = OutApp.CreateItem(olMailItem)
    msg.BodyFormat = olFormatHTML
    
    msg.Subject = "Hello"
    
    html = "<p>Dear person,</p>"
    html = html & "<p><b>Please</b> <u>read</u> <i>this</i> "
    html = html & "<span style='background-color:#F00'>important</span> mail.</p>"
    html = html & "<p><span style='font-size:24pt'>right away</span></p>"
    
    msg.HTMLBody = html
    
    msg.Display
    
End Sub

Upvotes: 1

Related Questions