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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Difficulty in posting my answer which contains HTML tags...
 
Upvote 0
I've sent you a PM. See if that has worked. The problem is that if you post code on here that contains HTML tags, it's converted into the result that that code would produce, and not the code itself!
 
Upvote 0
Make sure you use .HTMLBody instead of .Body with your email code (as in my example).
 
Upvote 0
HTML:
Public MsgBody As String
Sub AA_MsgBody()        MsgBody = ""    Variable1 = "Variable-1"    Variable2 = "Variable-2"        'Line 1    'This example includes use of the RGB function instead of a colour name    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:magenta'><b><u>" & "Line1" & "</span></b></u>"    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'>" & ":" & "</span>"    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>"        'Line 2    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'><b><i>" & "Line2" & "</span></b></i>"    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'>" & ":" & "</span>"    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>"        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
 
Last edited:
Upvote 0
Pete

Seems to post fine for me.:)

HTML:
Public MsgBody As String
Sub AA_MsgBody()
    MsgBody = ""
    Variable1 = "Variable-1"
    Variable2 = "Variable-2"
    'Line 1    'This example includes use of the RGB function instead of a colour name
    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:magenta'><b><u>" & "Line1" & "</span></b></u>"
    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'>" & ":" & "</span>"
    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 & ""        'Line 2
    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'><b><i>" & "Line2" & "</span></b></i>"
    MsgBody = MsgBody & "<span style='font-size:12pt;font-family:Arial;color:Green'>" & ":" & "</span>"
    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 & ""
    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
Hmm. Let me have another go:

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

This is what happened when I added the tags manually.</div>
 
Upvote 0
... and this is what I got when I highlighted the code and used the button:

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
Oh, and now the button has appeared on my toolbar! :)
 
Upvote 0
This was just copying and pasting directly from the VBA Editor, too.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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