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

[Chris Williamson]

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

 

[Chris Williamson]

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

 

[Chris Williamson]

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?

 

[DanteAmor]

To change the Font you can do it but using HTML:

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

 

[DanteAmor]

I keep fighting with the forum editor as it modifies the code when using html code .


This line appears like this:

newmessage.HTMLBody = " < HTML > ****** > < P > " & _

But it should be like this:

newmessage.HTMLBody = " < HTML > < BODY > < P > " & _

Please remove the spaces in the sentence " < HTML > < BODY > < P > "

 

[Chris Williamson]

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:

<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

 

[DanteAmor]

Not all sizes exist in HTML, another code such as Style CSS must be used.