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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

daverunt

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,132
Messages
5,509,344
Members
408,727
Latest member
Cantello

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top