Changing the font size in email message via VBA

[mcomp72]

I have an Excel file with VBA code that creates an Outlook email message. One thing it does is copy a range of cells from an Excel sheet into the body of the email. The font size of the cells in Excel is 14, but when pasted into the Outlook message, the font size is 11. I cannot figure out why this is happening. If I copy & paste the range manually, the font size is 14 in the Outlook message.

I am hoping to set the font size after the paste to 14 via VBA code, but have not been able to figure out how to do this. Here's the relevant code:

Sub CreateEmail()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim vInspector As Object
Dim wEditor As Object

LR = Sheet18.LastRow

Set rng = ThisWorkbook.Sheets("Birthday List").Range("A3:B" & LR)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set vInspector = OutMail.GetInspector
Set wEditor = vInspector.WordEditor

With OutMail
   
    .To = "emailaddr@mail.xyz"
   
    .Subject = "Birthdays"
   
    wEditor.Paragraphs(1).Range.Text = "We would like to wish all our members born this month a very Happy Birthday!" & vbNewLine & vbNewLine
   
    rng.Copy
   
    wEditor.Paragraphs(3).Range.Paste
   
    .Display
   
End With

Application.CutCopyMode = False
Set rng = Nothing
Set OutApp = Nothing
Set OutMail = Nothing
Set vInspector = Nothing
Set wEditor = Nothing

End Sub

Can anyone tell me what code to add to set the font to size 14? Preferably, I'd love to do a 'Select All' via code and then set the entire email body to the same font name and font size. But I'd be happy with how to set the font size in Paragraph 3.

By the way, I previously posted here a few days ago. No replies.
Changing the font of an email with VBA

 

[Anbuselvam]

In the modified code, I have added three lines of code that change the font size for each paragraph. The first line changes the font size of the first paragraph to 14, the second line changes the font size of the second paragraph to 12, and the third line uses a for loop to change the font size of all remaining paragraphs to 10. You can adjust the font size values to your preference.

Sub CreateEmail()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim vInspector As Object
Dim wEditor As Object
Dim i As Integer

LR = Sheet18.LastRow

Set rng = ThisWorkbook.Sheets("Birthday List").Range("A3:B" & LR)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set vInspector = OutMail.GetInspector
Set wEditor = vInspector.WordEditor

With OutMail
.To = "emailaddr@mail.xyz"

.Subject = "Birthdays"

wEditor.Paragraphs(1).Range.Text = "We would like to wish all our members born this month a very Happy Birthday!" & vbNewLine & vbNewLine
wEditor.Paragraphs(1).Range.Font.Size = 14 'Change the font size for the first paragraph

rng.Copy

wEditor.Paragraphs(3).Range.Paste
wEditor.Paragraphs(3).Range.Font.Size = 12 'Change the font size for the second paragraph

For i = 4 To wEditor.Paragraphs.Count
    wEditor.Paragraphs(i).Range.Font.Size = 10 'Change the font size for the remaining paragraphs
Next i

.Display
End With

Application.CutCopyMode = False
Set rng = Nothing
Set OutApp = Nothing
Set OutMail = Nothing
Set vInspector = Nothing
Set wEditor = Nothing

End Sub

 

[mcomp72]

The For loop was exactly what I needed. Many thanks!!