Formatting a Text String in VBA

[RichardSabbara]

I have the following macros that work:

Sub AAA_test_MAR()
Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(o)
.Subject = "Monthly Action Report (MAR) "
.To = "richard.sabbara@xxx.com"
.CC = ""
.HTMLBody = MAR_Message_SECURE
.Attachments.Add ""
.Send
End With
End Sub

Function MAR_Message_SECURE() As String
MAR_Message_SECURE = "Please find attached your Monthly Action Report (MAR) for August. " & _
"<br/><p style='font-family:Arial;font-size:9'> </p>"
"We are going to sent out survey. " & _
"We want to hear from you! " & _
End Function

For the line "We are going to send out a survery. " I want this be bold, sky blue and 14 point font.
For the fline "We want to hear from you." I want to make this italics.

I am not sure how to do that.

Thank you,

Richard

 

[ZVI]

Hi and welcome to MrExcel forum!
Try this:


Function MAR_Message_SECURE() As String
  
  Const Font1 = "<" & "span style='font-family:Arial;color:skyblue;font-weight:bold;font-size:14pt'" & ">"
  Const Font2 = "<" & "span style='font-style:italic;'" & ">"
  Const EndTag = "<" & "/" & "span" & ">"
  Dim s As String

  s = "Please find attached your Monthly Action Report (MAR) for August." & vbLf _
    & Font1 & "We are going to sent out survey." & EndTag & vbLf _
    & Font2 & "We want to hear from you!" & EndTag

  s = Replace(s, vbLf, "<" & "br /" & ">" & vbLf)
  
  MAR_Message_SECURE = s
  
  ' The below is just for smart testing.
  ' Copy text from Immediate window and paste into Excel sheet to see the result
  Debug.Print "<" & "body" & ">" & s & "<" & "/" & "body" & ">"
    
End Function

Regards

 

[RichardSabbara]

This works, thank you

 

[ZVI]

You are welcome, thanks for the feedback