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

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
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:

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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
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:

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

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

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
.Attachments.add Path & filename
 

Erik968

New Member
Joined
Feb 8, 2019
Messages
12

ADVERTISEMENT

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:

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 (Path & filename & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
What is in cell P39? Also, are you able to open the PDF normally? It's a normal PDF?
 

Erik968

New Member
Joined
Feb 8, 2019
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Can you post the code that is saving the file as a PDF?
 

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
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 :(

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 (Path & filename & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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