I have a VBA script that that generates and email when a VBA button is pushed in a given worksheet.
The script currently generates the email in a relatively small font. I was wondering if there is a way to set the font to Calibri, and the text sive to exactly 11.
Here is the current VBA script:
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim strUser As String
Dim signature As String
Dim sTo As String
Dim sCC As String
'For To field
Set emailRng = Worksheets("Send Email").Range("D3:I6")
For Each cl In emailRng
sTo = sTo & ";" & cl.Value
Next
sTo = Mid(sTo, 2)
'For CC field
Set emailRngCC = Worksheets("Send Email").Range("D8:I11")
For Each cl In emailRngCC
sCC = sCC & ";" & cl.Value
Next
sCC = Mid(sCC, 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Display
End With
signature = OutMail.HTMLBody
strbody = "<FONT SIZE = 3>Good Morning;<p>We have completed our main aliasing process for today. All assigned firms are complete. Please feel free to respond with any questions.<p>Thank you."
With OutMail
.SentOnBehalfOfName = ""
.To = sTo
.CC = sCC
.BCC = ""
.Subject = "Data Morning Alias Process - COMPLETE"
.HTMLBody = strbody & signature
.Display
End With
End Sub
I know that this portion of the code:
strbody = "<FONT SIZE = 3.5>Good Morning;<p>We have completed our main aliasing
is the portion that relates to the email body text size. But a setting of 3 is to small, and a setting of 4 is too big. So I was just wondering if there is some way I can set the font to be exactly size 11, and the text to be formatted as Calibri?
Thank you!
I did a little research and was able to write this code:
strbody = "<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>We have completed our main aliasing process for today. All assigned firms are complete. Please feel free to respond with any questions.<p>Thank you.</BODY>"
apparently by setting the "font-size=11pt" instead of setting the font size <font size=5>,
It allows you to select a specific font size like you normally would in a text editor, as opposed to selecting a value from 1-7 like my code was originally.
This link from simpLE MAn gave me some good info.
Set texts with different sizes and styles, and size and style for texts from cells ( with Range)
Sub EmailManuellAbsenden()
Dim ghApp As Object
Dim ghOldBody As String
Dim ghNewBody As String
Set ghApp = CreateObject("Outlook.Application")
With ghApp.CreateItem(0)
.To = Range("B2")
.CC = Range("B3")
.Subject = Range("B4")
.GetInspector.Display
ghOldBody = .htmlBody
ghNewBody = "<font style=""font-family: Calibri; font-size: 11pt;""/font>" & _
"<font style=""font-family: Arial; font-size: 14pt;"">Arial Text 14</font>" & _
Range("B5") & "<br>" & _
Range("B6") & "<br>" & _
"<font style=""font-family: Chiller; font-size: 21pt;"">Ciller 21</font>" &
Range("B5")
.htmlBody = ghNewBody & ghOldBody
End With
End Sub
'Fill B2 to B6 with some letters for testing
'"<font style=""font-family: Calibri; font-size: 15pt;""/font>" = works for all Range Objekts
FYI I did a little research as well and if the name of the font-family you want to apply contains spaces (as an example I take Gill Alt One MT Light), you should write it this way :
strbody= "<BODY style=" & Chr(34) & "font-family:Gill Alt One MT Light" & Chr(34) & ">" & YOUR_TEXT & "</BODY>"
Source: Stackoverflow.com