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

[Erik968]

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?

[/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]

 

[yky]

Instead of using SaveAs, try this:

[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]

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?

 

[yky]

Replace the following line with what I posted above.

ActiveWorkbook.SaveAs filename:=Path & filename & ".PDF"

 

[welshgasman]

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

 

[Erik968]

I tried it like this how you said but unfortunately I get an error.

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]

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.?

 

[yky]

Erik, I think you mixed up this one with the one in another thread.