VBA - email body to allow font type, size and colour

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
251
Office Version
  1. 2016
I'd like to be able to customise my automated email with font type, size and colour. I understand .Body needs to be .HTMLBody but not sure how to do the rest. Any assistance would be fantastic.

VBA Code:
Sub EmailAll()

    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Range("")
        .CC = Range("")
        .BCC = Join(Application.Transpose(Range("Aq6:Aq203").Value), ";") & ";"
        .Subject = Range("")
        .Body = "Dear families," & vbLf & _
                " " & vbLf & _
                " " & vbLf & _
                " " & vbLf & _
                "[who]" & vbLf & _
                "[role]" & vbLf & _
                "Local Sport Association" & vbLf & _
                "111 Sydney Street | Melbourne VIC 0000" & vbLf & _
                "PO Box 00 | Melbourne VIC 0000"
        .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
380
The easiest way I have found to do this is to write the email and get it formatted the way you want it in outlook and then file SaveAs .html

Use a text editor to open the html file and extract this. Insert this as your "body", note you will need to clean it up meaning replacing the double quotes with single and adding the & _ to the end etc.

Its basically using HTML but with outlook there seems to be something special added by MS for the formatting, never quite figured it out but this seems to work for me.
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
251
Office Version
  1. 2016
I've tried this and opened the HTML in Notepad. The code is huge, so I don't think this is going to be a workable solution unfortunately.
 

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
380
Yeah it is a lot of code, like I said they add a lot of random stuff in on top for formatting. Not an easy solution per say but the easiest I found and could make work with Excel. My attempts to just use standard HTML resulted in my html tags just appearing in the message as plain text etc. Good luck
 

Watch MrExcel Video

Forum statistics

Threads
1,132,980
Messages
5,656,203
Members
418,289
Latest member
Kjohno

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
Top