How to change the font name and size on the email message?

Chris Williamson

Board Regular
Joined
Oct 16, 2010
Messages
83
Hi,

I have got here a very simple macro that copies three values to a worksheet from a userform and then it opens a new email message and it pastes these values into the email message.


The userform has 3 text boxes and 1 command button:
TextBox1
TextBox2
TextBox3
CommandButton1




What I am trying to do is to change the font name to "Arial" and the font size to "20" on the body of the email message, what do I need to add in my vba macro code to do this?


Thank you


Chris






Here is my macro:


Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data_Sheet")


'copy the data to the spreadsheet
ws.Range("A1").Value = Me.TextBox1.Value 'Line 1
ws.Range("A2").Value = Me.TextBox2.Value 'Line 2
ws.Range("A3").Value = Me.TextBox3.Value 'Line 3


'reset the data back to default
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox1.SetFocus


Unload Me




' open microsoft outlook for the new email message
Set olApp = CreateObject("Outlook.Application")
Set newmessage = olApp.CreateItem(0)

newmessage.To = "name@hotmail.co.uk"


'Message Body
newmessage.Body = "Line 1: " & Worksheets("Data_Sheet").Range("A1").Value & Chr(13) _
& "Line 2: " & Worksheets("Data_Sheet").Range("A2").Value & Chr(13) _
& "Line 3: " & Worksheets("Data_Sheet").Range("A3").Value & Chr(13) & Chr(13) _
& "THANK YOU." & Chr(13) & Chr(13) _
& "**** EMAIL SIGNATURE GOES HERE ****"

newmessage.Subject = "SUBJECT LINE GOES HERE"


newmessage.Display
Set olApp = Nothing
Set newmessage = Nothing


End Sub
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I'm getting this error message... I need help, please, thanks

Run-time error '424':


Object required


on this line: newmessage.Body.Font.Name = "Arial"






Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data_Sheet")


'copy the data to the spreadsheet
ws.Range("A1").Value = Me.TextBox1.Value 'Line 1
ws.Range("A2").Value = Me.TextBox2.Value 'Line 2
ws.Range("A3").Value = Me.TextBox3.Value 'Line 3


'reset the data back to default
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox1.SetFocus


Unload Me




' open microsoft outlook for the new email message
Set olApp = CreateObject("Outlook.Application")
Set newmessage = olApp.CreateItem(0)

newmessage.To = "name@hotmail.co.uk"

newmessage.Subject = "SUBJECT LINE GOES HERE"


'Message Body
newmessage.Body = "Line 1: " & Worksheets("Data_Sheet").Range("A1").Value & Chr(13) _
& "Line 2: " & Worksheets("Data_Sheet").Range("A2").Value & Chr(13) _
& "Line 3: " & Worksheets("Data_Sheet").Range("A3").Value & Chr(13) & Chr(13) _
& "THANK YOU." & Chr(13) & Chr(13) _
& "**** EMAIL SIGNATURE GOES HERE ****"


newmessage.Body.Font.Name = "Arial"

newmessage.Display
Set olApp = Nothing
Set newmessage = Nothing


End Sub
 
Upvote 0
I get the same run time error with this code:

newmessage.Body.Select
With Selection.Font
.Name = "Arial"
.Size = 20
End With


What am I doing wrong?

Is it even possible to change the font name and size in the email message with vba?
 
Upvote 0
To change the Font you can do it but using HTML:

Code:
Private Sub CommandButton1_Click()
  Dim ws As Worksheet, wBody As String, olApp As Object, newmessage As Object
  Set ws = Worksheets("Data_Sheet")
  'copy the data to the spreadsheet
  ws.Range("A1").Value = Me.TextBox1.Value 'Line 1
  ws.Range("A2").Value = Me.TextBox2.Value 'Line 2
  ws.Range("A3").Value = Me.TextBox3.Value 'Line 3
  'reset the data back to default
  Me.TextBox1.Value = ""
  Me.TextBox2.Value = ""
  Me.TextBox3.Value = ""
  Me.TextBox1.SetFocus
  Unload Me
  ' open microsoft outlook for the new email message
  Set olApp = CreateObject("Outlook.Application")
  Set newmessage = olApp.createitem(0)
  newmessage.To = "damor"
  newmessage.Subject = "SUBJECT LINE GOES HERE"
  'Message Body
  wBody = "Line 1: " & Worksheets("Data_Sheet").Range("A1").Value & "<br>" _
    & "Line 2: " & Worksheets("Data_Sheet").Range("A2").Value & "<br>" _
    & "Line 3: " & Worksheets("Data_Sheet").Range("A3").Value & "<br><br>" _
    & "THANK YOU." & "<br><br>" _
    & "**** EMAIL SIGNATURE GOES HERE ****"
  newmessage.HTMLBody = "<HTML>******><P>" & _
            "<font face=""[COLOR=#0000ff]Arial[/COLOR]"" size=[COLOR=#0000ff]20[/COLOR]>" & _
                wBody & _
            "</font></P></BODY></HTML>"
  newmessage.Display
  Set olApp = Nothing
  Set newmessage = Nothing
End Sub
 
Last edited:
Upvote 0
I keep fighting with the forum editor as it modifies the code when using html code :mad:.


This line appears like this:
Code:
newmessage.HTMLBody = " < HTML > ****** > < P > " & _

But it should be like this:
Code:
newmessage.HTMLBody = " < HTML > < BODY > < P > " & _
Please remove the spaces in the sentence " < HTML > < BODY > < P > "
 
Last edited:
Upvote 0
Perfect!
Thank you so much, this works really well. :)

Just one tiny little query, the size doesn't match up 100% for me, for example:

HTML:
<font face=""Arial"" size=2> on the email message this shows as Font = 10
<font face=""Arial"" size=3> on the email message this shows as Font = 12
<font face=""Arial"" size=4> on the email message this shows as Font = 13.5
<font face=""Arial"" size=5> on the email message this shows as Font = 18
<font face=""Arial"" size=6> on the email message this shows as Font = 24

<font face=""Arial"" size=20> on the email message this shows as Font = 36
Do you know why it does this?

Kind regards

Chris
 
Last edited:
Upvote 0
Not all sizes exist in HTML, another code such as Style CSS must be used.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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