VBA Excel Email Formatting Issue

Question

My data is set up like this:

-On Excel, I have a worksheet, "Test1", which has a list of people I want to send emails to. Like such;

No.    Version  Company  Name    Email
 1       R         x     Max     [email protected]
 2       E         y     Bill    [email protected]
 3       C         z     Scott   [email protected]

The emails I want to send differ slightly by type. My current code is as follows:

Sub mailTest()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

For i = 2 To 3

   Set olApp = New Outlook.Application
   Set olMail = olApp.CreateItem(olMailItem)

    mailBody = Worksheets("BODY").Cells(1, 1).Value & Worksheets("Test1").Cells(i, 4) _
 & Worksheets("BODY").Cells(2, 1).Value



   With olMail
       .To = Worksheets("Test1").Cells(i, 5).Value
       .Subject = Worksheets("Test1").Cells(i, 3).Value
       .HTMLBody = mailBody
       .Display

End With


   Set olMail = Nothing
   Set olApp = Nothing

Next



End Sub

The code above displays the emails as the main body is in Cell A2 of sheet "BODY". The "Hello" is stored in Cell A1, and also displays fine as I have HTML coding in both these cells to set the font to Calibri and the size to 3.

The issue is when I'm pulling the names of people from sheet Test1 in Column 4 using the code;

 & Worksheets("Test1").Cells(i, 4)

It is displaying with a different font, defaulting to Times New Roman. What I'm left with is everything displaying in Calibri, with just their names being pulled in Times New Roman.

Is there any way to fix this without adding HTML code in each of the name cells? The actual file I'm working on has about 500 names so manually doing it would be a pain...

Thanks everyone.

PaichengWu · Accepted Answer · 2018-08-01 14:48:21Z

1

Try replace

.HTMLBody = mailBody

with

.HTMLBody = "<!DOCTYPE html><html><head><style>"
.HTMLBody = .HTMLBody & "body{font-family: Calibri, ""Times New Roman"", sans-serif}"
.HTMLBody = .HTMLBody & "</style></head><body>"    
.HTMLBody = .HTMLBody & mailBody & "</body></html>

edited Aug 1, 2018 at 14:48

answered Aug 1, 2018 at 14:36

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tester_Y Over a year ago

That worked perfectly!! In terms of modifying the font/size/etc, would I insert it into the squiqly brackets? " { } "? I tried adding ", size: 2.5" at the end of "sans-serif" and it just invalidated the entire code.

PaichengWu Over a year ago

Try body{font-family: Calibri, ""Times New Roman"", sans-serif; font-size: 25px}. You could learn more CSS from https://www.w3schools.com/css/css_font.asp.

Collectives™ on Stack Overflow

VBA Excel Email Formatting Issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related