Formatting an email in VBA‏

Lloydlobo

New Member
Joined
May 19, 2014
Messages
34
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
 
HTML:
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
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Aha!

HTML:
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
 
Upvote 0
Nope. Definitely not playing in Chrome. What are YOU using, Norie?
 
Upvote 0
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.
 
Upvote 0
HTML:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top