Formatting an email in VBA‏

[Lloydlobo]

I have the following code which creates an email after selecting cells in Excel.
The Email defaults to Font Calibri 12.
If I want to make B17 bold and B28 in Calibiri 8, how do I incorporate within the code?


Many thanks,

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Sub Create_Individual_Email()
'Sending email to individual partners
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Sheets("Message").Visible = True
Sheets("Message").Select
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "" & Range("B2").Value & ""
.Subject = "" & Range("b4").Value & vbNewLine
.Body = "" & Range("b6").Value & vbNewLine & _
"" & vbNewLine & _
"" & Range("b8").Value & vbNewLine & _
"" & Range("b10").Value & vbNewLine & _
"" & vbNewLine & _
"" & Range("b12").Value & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"" & Range("b14").Value & vbNewLine & _
"" & Range("b15").Value & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"" & Range("b17").Value & "," & vbNewLine & _
"" & Range("b18").Value & "," & vbNewLine & _
"" & Range("B19").Value & vbNewLine & _
"" & Range("B20").Value & vbNewLine & _
"" & Range("B21").Value & vbNewLine & _
"" & Range("B22").Value & vbNewLine & _
"" & Range("B23").Value & vbNewLine & _
"" & Range("B24").Value & vbNewLine & _
"" & Range("B25").Value & vbNewLine & _
"" & Range("B26").Value & vbNewLine & _
"" & vbNewLine & _
"" & Range("B28").Value & vbNewLine


'You can add a file like this
.Attachments.Add "" & Range("b30").Value & ""
.Attachments.Add "" & Range("b31").Value & ""
'.Send 'or use .Display
.Display
End With
On Error GoTo 0

Sheets("Message").Visible = False
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[NewOrderFac33]

Public MsgBody As String
Sub AA_MsgBody()        MsgBody = ""    Variable1 = "Variable 1 contents"    Variable2 = "Variable 2 contents"        'Line 1 - this example illustrates how to make a value bold (<b> and </b>) and italic (<i> and </i>)    'and how to change the colors via colour names.    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'><b><i>" & "Line1" & "</span></b></i>"    MsgBody = MsgBody & "<span style='mso-tab-count:3'></span>"    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Red'>" & Variable2 & "</span>"    MsgBody = MsgBody & "<br><br><br><br>"        'Line 2 - this example illustrates how to make a value bold (<b> and </b>)and underlined (<u> and </u>)    'It also illustrates the use of Hex and RGB colour codes instead of colour names    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:#FF00FF'><b><u>" & "Line2" & "</span></b></u>"    MsgBody = MsgBody & "<span style='mso-tab-count:3'></span>"    MsgBody = MsgBody & "<span style='font-size12pt;font-family:Arial;color:rgb(100,100,100)'>" & Variable1 & "</span>"    MsgBody = MsgBody & "<br><br><br><br>"        SendEmail
End Sub
Private Sub SendEmail()        Dim OutApp, OutMail As Object    Set OutApp = CreateObject("Outlook.Application")    Set OutMail = OutApp.CreateItem(0)        'On Error Resume Next    With OutMail        .To = "Pete.Rooney@blahblah.com"        .CC = "Somebody.Else@blahblah.com"        .Subject = "Test Email"        .HTMLbody = MsgBody        .display        '.Send    End With
    'On Error GoTo 0    Set OutMail = Nothing    Set OutApp = Nothing
End Sub

 

[Norie]

Weird. What browser are you using?

 

[Lloydlobo]

Thanks Peter - will give it a go

 

[NewOrderFac33]

Chrome. Wonder what happens if I try IE? Hang on...

 

[Norie]

I'm using Chrome too.

 

[NewOrderFac33]

Aha!

Public MsgBody As String
Sub AA_MsgBody()
    
    MsgBody = ""
    Variable1 = "Variable 1 contents"
    Variable2 = "Variable 2 contents"
    
    'Line 1 - this example illustrates how to make a variable of label bold ( and ) and italic ( and )
    'and how to change the colors via colour names. It also shows how to add tabs between entries.
    'and finishes the line off with four carriage returns.
    MsgBody = MsgBody & "" & "Line1" & ""
    MsgBody = MsgBody & ""
    MsgBody = MsgBody & "" & Variable2 & ""
    MsgBody = MsgBody & "



"
    
    'Line 2 - this example illustrates how to make a variable or label bold ( and ) and underlined ( and )
    'It also illustrates the use of Hex and RGB colour codes instead of colour names and again adds tabs between entries,
    'finishing off with carriage returns.
    MsgBody = MsgBody & "" & "Line2" & ""
    MsgBody = MsgBody & ""
    MsgBody = MsgBody & "" & Variable1 & ""
    MsgBody = MsgBody & "



"
    
    SendEmail
End Sub

 

[NewOrderFac33]

Nope. Definitely not playing in Chrome. What are YOU using, Norie?

 

[Norie]

I've only recently started using Chrome but I used to use IE and didn't have a problem with that either.

One thing I did do with the code was to indent it using Smart Indenter, don't know if that would make a difference.

 

[NewOrderFac33]

Public MsgBody As String
Sub AA_MsgBody()        MsgBody = ""    Variable1 = "Variable 1 contents"    Variable2 = "Variable 2 contents"        'Line 1 - this example illustrates how to make a variable of label bold (<b> and </b>) and italic (<i> and </i>)    'and how to change the colors via colour names. It also shows how to add tabs between entries.    'and finishes the line off with four carriage returns.    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'><b><i>" & "Line1" & "</span></b></i>"    MsgBody = MsgBody & "<span style='mso-tab-count:3'></span>"    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Red'>" & Variable2 & "</span>"    MsgBody = MsgBody & "<br><br><br><br>"        'Line 2 - this example illustrates how to make a variable or label bold (<b> and </b>) and underlined (<u> and </u>)    'It also illustrates the use of Hex and RGB colour codes instead of colour names and again adds tabs between entries,    'finishing off with carriage returns.    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:#FF00FF'><b><u>" & "Line2" & "</span></b></u>"    MsgBody = MsgBody & "<span style='mso-tab-count:3'></span>"    MsgBody = MsgBody & "<span style='font-size12pt;font-family:Arial;color:rgb(100,100,100)'>" & Variable1 & "</span>"    MsgBody = MsgBody & "<br><br><br><br>"        SendEmail
End Sub

 

[NewOrderFac33]

Sorry, not familiar with Smart Indenter - I assume it's an external tool (i.e. not one of the tools on the toolbar here)?
I always indent my code manually!