How do I add a PDF attachment to my mail via VBA?

[Erik968]

At the moment my code works like this: when you click on play it automatically saves the file to PDF, then it opens the mail itself with a title, etc and also with a XLSM file. now I want it to add the same file as PDF instead of XLSM.. how do I do that if the title of the PDF is the cellname Range ("39")?

this is my code:

Sub saveandsend()

Dim Path As String
Dim filename As String
Path = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename = Range("P39")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm"


' SendEmail Macro
'


Dim outlookapp As Object
Dim OutlookMail As Object
Set outlookapp = CreateObject("Outlook.Application")
Set OutlookMail = outlookapp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = "erikstoeken@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = "Bon zit in de bijlage."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Attachments.Add tempPDFFileName
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub

 

[KennyGreens]

Hi, I feel like I am missing something here (and please correct me if I am wrong), but I don't see anywhere in the code where it saves it as a PDF. This starts off saving it as an xlsm file. I imagine you would have to save it as a PDF and then use this:

.Attachments.Add (Path & filename & ".pdf")

Again, I may be misunderstanding, so please correct me if I am wrong.

 

[Erik968]

Thanks for responding, it already saves as PDF to my pc so thats not a problem anymore, but I dont know how to send a PDF as attachment in outlook with VBA.... also the PDF name needs to be the cell name "P39" (Range P39), but I just dont know how....

 

[Norie]

You attach a PDF the same way you attach any file, by using Attachments.Add filepath.

So assuming the value in the P39 is the filename alone all you should need is this.

.Attachments.add Path & filename

 

[Erik968]

thanks for the reply,

I added that line of code and it does include an attachment to my mail but its not a true PDF file.. its unable to open it.. how do I make sure it attaches a real PDF file?

the code right now:

Sub saveandsend()


Dim Path As String
Dim filename As String
Path = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename = Range("P39")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm"


' SendEmail Macro
'


Dim outlookapp As Object
Dim OutlookMail As Object
Set outlookapp = CreateObject("Outlook.Application")
Set OutlookMail = outlookapp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = "erikstoeken@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = "Bon zit in de bijlage."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Attachments.Add (Path & filename & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub

 

[KennyGreens]

What is in cell P39? Also, are you able to open the PDF normally? It's a normal PDF?

 

[Erik968]

Cell P39 is just a name (what changes depending what choice you make in the worksheet) but I think the main problem is that my code doesnt save the PDF properly to my PC in the first place, all the PDF's are broken... so I think thats where the problem starts..

any idea how I can fix the part where I save the file as a PDF to my PC before it gets used as an attachment in the mail?

Thanks in advance

 

[Norie]

Can you post the code that is saving the file as a PDF?

 

[Erik968]

well thats kind of the problem, because its somehow saving it as a broken PDF but the code is not right at all...

It should be at the start

Sub saveandsend()


Dim Path As String
Dim filename As String
Path = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename = Range("P39")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm"


' SendEmail Macro
'


Dim outlookapp As Object
Dim OutlookMail As Object
Set outlookapp = CreateObject("Outlook.Application")
Set OutlookMail = outlookapp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = "erikstoeken@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = "Bon zit in de bijlage."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Attachments.Add (Path & filename & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub