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

 

[RoryA]

Create the email, set it up and then File-Save As, and choose Outlook Template from the dropdown. You then use:

Set Item = App.CreateItemFromTemplate("C:\my_template_mail.oft")

for example.
(that's about all I know - Outlook is not my area of expertise)

 

[manc]

Sir, I tried it and I also tried it as Set Itm as well, no joy...Have I put it in the right place?

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)
Set Item = App.CreateItemFromTemplate("C:\Users\Stuart\AppData\Roaming\Microsoft\Templates\excel.oft")
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


Regards
manc

 

[RoryA]

Change this:

Set Itm = App.CreateItem(0)
Set Item = App.CreateItemFromTemplate("C:\Users\Stuart\AppData\Roaming\Microsoft\Templates\excel.oft")

to this:

Set Itm =  App.CreateItemFromTemplate("C:\Users\Stuart\AppData\Roaming\Microsoft\Templates\excel.oft")

 

[manc]

No luck, it adds the image from the signature as an attachment to email, and doesn't display any of the text from the signature either.

I appreciate your help rorya, again!, but its time for the pub after a hard days work..Apologies if you post a solution and don't hear a reply any time soon!


Regards
manc

 

[RoryA]

As I will be in the pub, I won't be posting either.