VBA to attach PDF to email - halfway there I think!

[manc]

Good morning Demigods

I have the follwoing code that save the current worksheet to .pdf in a directory.


Sub Print_PDF()

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, ""

End Sub


All I need to happen now, is when the user clicks a button, the above code runs and then the email window opens and it attaches itself to an email address located in cell A1.

I've read the guides, namely Ron de Bruin's, and whilst interesting, it is unfortunately way above my level of a) inteligence, b) knowledge and c) awesomeness.

If someone can help appreciation will be granted.

Regards
manc

 

[daverunt]

I don't know whether you can tab this to the end of the current code or call it as a separate macro. Try it.


You can play around with how to specify the filename - with the path or as a variable.

Dim Mail_Object, Mail_Single As Variant
 
        Email_Subject = "Booking Confirmation3"
 
        nameList = Sheets("Sheet1").Range("A1").Value
        Email_Send_To = nameList
 
 
        Email_Cc = ""
        Email_Bcc = ""
        Email_Body = "Here's your confirmation"
 
        Set Mail_Object = CreateObject("Outlook.Application")
        Set Mail_Single = Mail_Object.CreateItem(o)
 
        With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
 
            .CC = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .Attachments.Add ("c:\PDF Files\Booking Confirmation.pdf")
            '.Display
            .send
 
End With
        MsgBox "E-mail successfully sent"
        Application.DisplayAlerts = False
End Sub

 

[manc]

Dear daverunt

Thanks very much for your suggestion. Whilst I haven't used your whole example, I have extracted bits of it to use in the following code...


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



Thanks again for your response. Only way I'm going to learn is by not directly using people's examples!

Regards
manc

 

[Kafunya]

Good day daverunt,

I ran your macro and it works perfectly,
I have a macro that generates a a weekly report and ends with the day i run it,
e.g. .Attachments.Add ("C:\PDF_Files\Weekly_XX_Report_20130715.pdf")
My query is how do i make the macro automatically use the latest updated PDF?

 

[Bethod]

Thanks Daverunt and Manc both Macros saved me lots of time!