PDF File Won't open

[DMT Dave]

Hi Guy's the following code i have copies and Excel file to save as pdf format for emailing reasons, the adobe acrobat is coming up with a can't open issue, when i try opening any pdf on the PC, other files open fine so is there anything obvious in my code that would cause this ?

    InvNo = Forms!frmMainMenu!txtInvoiceNumber    pOpen = "T:\Removed On Here\XL Files\EXISTING INVOICES\"
    fOpen = Forms!frmMainMenu!cboCustomerMain & " " & InvNo & " " & Me.txtInvDate & ".xlsx"
    pOpen = "T:\Removed On Here\XL Files\EXISTING INVOICES\"
    PDFFile = myCust & " " & Me.txtInvoiceNumber
    InvFile = "T:\Removed On Here\XL Files\EXISTING INVOICES\" & myCust & " " & Me.txtInvoiceNumber & ".xlsx"
If Len(Dir(pOpen & PDFFile, vbDirectory)) > 0 Then
    DoCmd.CancelEvent
    Else
    FileCopy pOpen & fOpen, pOpen & PDFFile & ".pdf"
End If
    myInput = InputBox("Enter Option For Invoice: " & InvNo & " " & "?" & vbNewLine & vbNewLine & _
    "0 > ABORT THESE OPTIONS" & vbNewLine & vbNewLine & _
    "1 > Open Invoice: " & InvNo & " " & "Original XL Invoice" & vbNewLine & vbNewLine & _
    "2 > Send Invoice: " & InvNo & " " & Me.cboStCust, "ENTER OPTION")
Select Case myInput
Case 0
    DoCmd.CancelEvent
Case 1
    pOpen = "T:\Removed\XL Files\EXISTING INVOICES\"
    fOpen = Forms!frmMainMenu!cboCustomerMain & " " & InvNo & " " & Me.txtInvDate & ".xlsx"
    Set apXL = CreateObject("Excel.Application")
With apXL
    .Visible = True
    .Workbooks.Open pOpen & fOpen
End With
Set apXL = Nothing
Case 2
    pOpen = "T:\Removed\XL Files\EXISTING INVOICES\"
    PDFFile = myCust & " " & Me.txtInvoiceNumber & ".pdf"
    a = TOD & " " & myClient
    b = "Please find attached your invoice for your kind attention."
    c = "With Kind Regards"
    d = Forms!frmMainMenu!txtLogin
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
Set OutAccount = oEmailItem.Session.Accounts.Item(2)
    .To = myEmail
    .Subject = "Invoice Attached"
    .Attachments.Add pOpen & PDFFile
    .HTMLBody = a & "<br>" & "<br>" & b & "<br>" & "<br>" & c & "<br>" & "<br> " & _
    d & "<br>" & "<br>" & _
    "<P><IMG border=0 hspace=0 alt='' src='file://T:/Removed/Email Signature.jpg' align=baseline></P>" & "<br>" & "<br>" & _
    "<FONT color=#0000CD>" & eDisc & "<br>" & "<FONT color =#0000CD>" & eDisc2
    .SendUsingAccount = OutAccount
    .Display
End With
End Select

[Gicu]

Dave,
You can't just copy an Excel file and change its extension to PDF and I think that is the cause of the error:

FileCopy pOpen & fOpen, pOpen & PDFFile & ".pdf"

Have a look at this example of how to automate Excel to save the file as a PDF (many more example available):https://exceloffthegrid.com/vba-code...l-file-as-pdf/

Cheers,
Vlad

[DMT Dave]

Thanks Vlad, will do some adapting

[DMT Dave]

Perfect Vlad, thank you, the link gives me other options also

[DMT Dave]

Hi Guy's Vlad gladly helped me on this, the issue i am having now if someone can correct/highlight the code, Excel is not closing correctly and having to use Ctrl Alt Delete to task manager and close excel by ending task

I have at the bottom of my code what i thought would close it ??

Case 2    Set apXL = CreateObject("Excel.Application")
    Set xlWB = apXL.Workbooks.Open(pOpen & fOpen)
    apXL.Workbooks.Open pOpen & fOpen, True, False
    apXL.Visible = False
    pdfPath = "T:\Removed\PDF\Invoices\" & myCust & "\"
    pdfFile = myCust & " " & InvNo & ".pdf"
If Len(Dir(pdfPath, vbDirectory)) = 0 Then
    MkDir (pdfPath)
Else
    DoCmd.CancelEvent
End If
    xlWB.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=pdfPath & pdfFile
    a = TOD & " " & myClient
    b = "Please find attached your invoice for your kind attention."
    c = "With Kind Regards"
    d = Forms!frmMainMenu!txtLogin
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
Set OutAccount = oEmailItem.Session.Accounts.Item(2)
    .To = myEmail
    .Subject = "Invoice Attached"
    .Attachments.Add pdfPath & pdfFile
    .HTMLBody = a & "<br>" & "<br>" & b & "<br>" & "<br>" & c & "<br>" & "<br> " & _
    d & "<br>" & "<br>" & _
    "<P><IMG border=0 hspace=0 alt='' src='file://T:/Removed/Email Signature.jpg' align=baseline></P>" & "<br>" & "<br>" & _
    "<FONT color=#0000CD>" & eDisc & "<br>" & "<FONT color =#0000CD>" & eDisc2
    .SendUsingAccount = OutAccount
    .Display
End With


End Select
End If
    Set xlWB = Nothing
    Set apXL = Nothing

[Gicu]

You are only resetting the variable, you need to actually close the workbook before doing that:

 
xlWB.Close Savechanges:=True 'or Savechanges:=False 
apXL.Quit

EDIT: added second line to quit the Excel app

Cheers,
Vlad

[DMT Dave]

Hi Vlad, thank you again, do i need to add this before set to nothing but after end select

Kindest
Dave

[Gicu]

Just before setting to nothing.

[DMT Dave]

Thanks Vlad, will change it, thank again for your help