VBA to change font and size in email

[manc]

Hi Demigods

I have a macro that creates a .pdf of the active sheet and adds it as an attachment to an email. I would like to change the font and size of the email message via the same macro, so the whole process is automated

I want the font to be "Calibri" and size 11

What lines of code can I add?

Code is pasted below...

Your help, as always, is appreciated.

Regards
manc


Sub Create_and_SendPDF()
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\PDF Files\Sample.ps"
PDFFileName = "c:\PDF Files\Booking Confirmation.pdf"
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm

.Subject = "Booking Confirmation: " & Range("D20")
.To = Range("AB17")
.Body = "Dear " & Range("AB18") & vbCrLf & vbCrLf
.Body = .Body & Range("E24") & " " & Range("K24") & vbCrLf
.Body = .Body & Range("E25") & " " & Range("K25") & vbCrLf & vbCrLf
.Body = .Body & "Please find attached your booking confirmation." & vbCrLf & vbCrLf

.Attachments.Add "C:\PDF Files\Booking Confirmation.pdf"
.Attachments(1).Position = Len(.Body)
.Body = .Body & vbCrLf & vbCrLf & vbCrLf
.Display

End With

End Sub

 

[John_w]

Use HTMLBody instead of Body and put style attributes in each line/paragraph, something like this:

.HTMLBody = "<p style='font-family:calibri;font-size:11'>" & "Your text here" & "</p>"

 

[RoryA]

Or use a template which already has the font you want.

 

[manc]

Thanks John w

I've changed the code to this

To = Range("AB17")
.HTMLBody = "<p style='font-family:calibri;font-size:11'>" & "Dear " & Range("AB18") & vbCrLf & vbCrLf & "</p>"
.HTMLBody = "<p style='font-family:calibri;font-size:11'>" & Range("E24") & " " & Range("K24") & vbCrLf & "</p>"
.HTMLBody = "<p style='font-family:calibri;font-size:11'>" & Range("E25") & " " & Range("K25") & vbCrLf & vbCrLf & "</p>"
.HTMLBody = "<p style='font-family:calibri;font-size:11'>" & "Please find attached your booking confirmation." & vbCrLf & vbCrLf & "</p>"
.Attachments.Add "C:\PDF Files\Booking Confirmation.pdf"
.Attachments(1).Position = Len(.Body)
.HTMLBody = .HTMLBody & vbCrLf & vbCrLf & vbCrLf
.Display



But it displays only the last line at 8.5 size?!?!?
Any clue?

Regards
manc

 

[John_w]

Post your code in HTML tags like I did to prevent the forum interpreting your < P> and style attributes. Build up HTMLBody like you did with Body, but use the < P> tag and the style attributes and < /p> instead of VbCrLf.

 

[manc]

It's ok John w, the easiest solution is to change the new mail template to 12 rather than 11, as rorya suggested! Appreciate your comments though.

I am looking to add a signature to the email though. I have read Ron de Bruin's guide to adding signature in mail but it doesn't appear.

Any ideas on this one?

Regards
Manc

 

[RoryA]

Why not add the signature to the template too?

 

[manc]

When I click new mail in Outlook, the signature is applied automatically to new mail.

However, when this macro is run in excel and the message window appears, you have to click signature and choose it manually for some reason.

Regards
manc

 

[RoryA]

You can set up a blank email formatted the way you want, then save it as a template (oft file). Your code can then use that file as a template for your email.

 

[manc]

Old geezer, please enlighten me how...