PDF sent by VBA to outlook as attachment doesnt work/open

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
I want to send the worksheet what im working on as PDF in outlook by using VBA, when I test this out it does make an attachment but the PDF file is broken and wont open.. anyone know how to fix this?

Code:
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]Sub saveandsend()[/FONT][/COLOR]
Dim Path As String
Dim filename As String
Path = "DIRECTORY"
filename = Range("P39")
ActiveWorkbook.SaveAs filename:=Path & filename & ".PDF"

' SendEmail Macro
'
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")
Dim NewMail As Object

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 = ""
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = ""
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing

 [COLOR=#252C2F][FONT=Courier]End Sub[/FONT][/COLOR][COLOR=#252C2F][FONT=Helvetica]
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,856
Office Version
  1. 2010
Platform
  1. Windows
Instead of using SaveAs, try this:

Code:
[LEFT][COLOR=#000000][FONT=Consolas]ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=[/FONT][/COLOR][FONT=Consolas]Path & filename & ".PDF"[/FONT][COLOR=#000000][FONT=Consolas], Quality:=xlQualityStandard, DisplayFileAfterPublish:=[/FONT][/COLOR][COLOR=#07704A][FONT=Consolas]True[/FONT][/COLOR][/LEFT]
 

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
thanks for helping out, but since I am a big VBA noob I dont know where I need to add that line of code, do I need to add it at the start of my code or at the email part?
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885

ADVERTISEMENT

FWIW just changing the extension does nothing to the format of the file.?
 

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
I tried it like this how you said but unfortunately I get an error.

Code:
Sub saveandsend()


    
Dim Path2 As String
Dim filename2 As String
Path2 = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename2 = 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 Application.ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Path & filename & ".PDF", Quality:=xlQualityStandard, DisplayFileAfterPublish:=True
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
That is not how you were advised.
Save the file first with the syntax supplied as instructed, THEN attach it with the same name and path that you saved it as.

Edit:
I've only just noticed that you have Path2 and path, but path is not set.?
 
Last edited:

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,856
Office Version
  1. 2010
Platform
  1. Windows
Erik, I think you mixed up this one with the one in another thread.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,346
Messages
5,635,758
Members
416,877
Latest member
SolarTheory

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