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

manc

Active Member
Joined
Feb 26, 2010
Messages
335
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

Active Member
Joined
Feb 26, 2010
Messages
335
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

New Member
Joined
Jul 17, 2013
Messages
1
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,715
Messages
5,660,446
Members
418,580
Latest member
JP82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top