create pdf and email

[manc]

Good morning,

I've used code below with previous versions of excel with no problems, but now that we have changed to Office 2016, it doesn't seem to work.
Here is the code that should create a .pdf and send it via email, without it being displayed:

Sub Create_and_SendPDF_Sheet1()
 
    Dim strPName As String
    
    strPName = Application.ActivePrinter
  
    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 = Range("AP12")
            .To = Range("AP13")
            .CC = Range("AP14")
            .Body = "Dear " & Range("ER20") & "," & vbCrLf & vbCrLf
            .Body = .Body & "Please find attached your booking confirmation for your recent shipment." & vbCrLf & vbCrLf
            .Body = .Body & "Your ref:" & vbCrLf & Range("EN23") & vbCrLf & Range("EN24") & vbCrLf & Range("EN25")
            .Attachments.Add "C:\PDF Files\Booking Confirmation.pdf"
            .Attachments(1).Position = Len(.Body)
            .Body = .Body & vbCrLf & vbCrLf & vbCrLf
            .Send
        End With
        
    Application.ActivePrinter = strPName
 End Sub

The problem is that it is not creating the .pdf file - it says the file cannot be found and, low and behold, no file appears in c:\PDF Files\ folder.

I am sure I have selected the correct references:
Visual Basic For Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Acrobat Distiller
AdobePDFMakerForOffice
AdobePDFMakerX
Microsoft Outlook 16.0 Object Library
Any help greatly appreciated.

Best regards
manc

 

[manc]

Problem solved.

Here is the code i used:

Sub SavePDF()

    Dim FileName As String
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:="C:\PDF Files\Export.pdf", _
        OpenAfterPublish:=False




        Set App = CreateObject("Outlook.Application")
        Set Itm = App.CreateItem(0)
        With Itm
            .Subject = Range("AP12")
            .To = Range("AP13")
            .CC = Range("AP14")
            .Body = "Dear whoever," & vbCrLf & vbCrLf
            .Body = .Body & "Body of text here" & vbCrLf & vbCrLf
            


            .Attachments.Add "C:\PDF Files\Export.pdf"
            .Attachments(1).Position = Len(.Body)
            .Body = .Body & vbCrLf & vbCrLf & vbCrLf
            .Send


        End With
        
  End Sub

Hope it becomes useful to others
Best regards
manc