VBA attach PDF to email stopped working

[colzre]

Dear Experts,

I have been using the below VBA for years and suddenly stopped working. This VBA is used to convert the Excel sheet to a PDF format and then attach this to an email. Not sure if the issue is related to an update or new version of Microsoft (I am now using 365) but long story short my VBA no longer works and I am now getting the following error:
"Run-time error '-2147024894 (80070002)':
Cannot find this file. Verify the path and the file name are correct."

Once debugged, it highlights this part of the VBA ".Attachments.Add PdfFile"

Sub pdf_email()


  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
 End With
 
 ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .Display
    .To = "info@outlook.com"
    .htmlbody = "<p style='font-family:calibri;font-size:15'>Good morning,</font></p>" & _
    "<p style='font-family:calibri;font-size:15'>Please find attached the report.</font></p>" & _
     "<p style='font-family:calibri;font-size:15'>Kind regards,</font></p>" & .htmlbody
          
     
    .Attachments.Add PdfFile
    .Display
 
  
  ' Delete PDF file
  Kill PdfFile
 
 
 End With
End Sub

 

[Micron]

To start with, I would place
Debug.Print pdfFile
Stop

before the problem line where you attempt to add. Then run it and look at the immediate window output. If it looks good, use File Explorer and copy the file path, place the insertion point on the line below that output and paste. You will have to manually add the file name, or you can go back to FE, right click and copy/paste after you select Properties from the right click. This is to absolutely ensure that nothing has changed in the file path; e.g. MyDocs was changed to My Docs.

Seems you have upgraded, and I have seen many threads where this caused methods that would run before to not run after an upgrade due to what I'd characterize as malformed code. I would pass the string as an argument to the email object but you are not creating one explicitly. You are expecting it to be created with your With block. So instead

Dim objMail As Object
Set objMail = objOutlook.CreateItem(0)
'other code here

With objMail
   .subject = ...
   'and so on
End With

Don't forget to remove your debug and stop lines. I recommend that you clean up object references; e.g. Set objMail = Nothing
Lots of debate on that notion but I do it since it keeps me on the cautionary side of the fence and has no impact if it's truly not required.

 

[colzre]

To start with, I would place
Debug.Print pdfFile
Stop

before the problem line where you attempt to add. Then run it and look at the immediate window output. If it looks good, use File Explorer and copy the file path, place the insertion point on the line below that output and paste. You will have to manually add the file name, or you can go back to FE, right click and copy/paste after you select Properties from the right click. This is to absolutely ensure that nothing has changed in the file path; e.g. MyDocs was changed to My Docs.

Seems you have upgraded, and I have seen many threads where this caused methods that would run before to not run after an upgrade due to what I'd characterize as malformed code. I would pass the string as an argument to the email object but you are not creating one explicitly. You are expecting it to be created with your With block. So instead

Dim objMail As Object
Set objMail = objOutlook.CreateItem(0)
'other code here

With objMail
   .subject = ...
   'and so on
End With

Don't forget to remove your debug and stop lines. I recommend that you clean up object references; e.g. Set objMail = Nothing
Lots of debate on that notion but I do it since it keeps me on the cautionary side of the fence and has no impact if it's truly not required.

Thank you very much for your response.

Really appreciate your tips however I am not too sure if I am doing well after your suggestions. I am kind of newbie in this sort of VBA´s.

I have done the changes however I don´t it get resolved. Still error.

Perhaps am I missing something? Any chance to see the entire code for my reference?


Thanks!

 

[Micron]

Posting your new version might have helped to determined if your edits were correct. Here's a sample code that I edited to help someone else. What I don't care for is that it uses ActiveSheet, which can cause it to fail if the wrong sheet is the active one. However it should explain the concept of the email and attachment objects being Set prior to trying to use them. You were doing this for the first object but not the second.

Public Sub Save_Range_As_PDF_and_Send_Email_PO()

    Dim PDFrange As Range, PDFfile As String
    Dim toEmail As String, emailSubject As String, HTMLBody As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    
    With ActiveWorkbook
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        PDFfile = .ActiveSheet.Range("C7").Value
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached
    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached purchase order. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                    
        .Attachments.Add PDFfile
        '.Send
        .Display
    End With

    'Delete the temporary PDF file
    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

 

[colzre]

To start with, I would place
Debug.Print pdfFile
Stop

before the problem line where you attempt to add. Then run it and look at the immediate window output. If it looks good, use File Explorer and copy the file path, place the insertion point on the line below that output and paste. You will have to manually add the file name, or you can go back to FE, right click and copy/paste after you select Properties from the right click. This is to absolutely ensure that nothing has changed in the file path; e.g. MyDocs was changed to My Docs.

Seems you have upgraded, and I have seen many threads where this caused methods that would run before to not run after an upgrade due to what I'd characterize as malformed code. I would pass the string as an argument to the email object but you are not creating one explicitly. You are expecting it to be created with your With block. So instead

Dim objMail As Object
Set objMail = objOutlook.CreateItem(0)
'other code here

With objMail
   .subject = ...
   'and so on
End With

Don't forget to remove your debug and stop lines. I recommend that you clean up object references; e.g. Set objMail = Nothing
Lots of debate on that notion but I do it since it keeps me on the cautionary side of the fence and has no impact if it's truly not required.

Posting your new version might have helped to determined if your edits were correct. Here's a sample code that I edited to help someone else. What I don't care for is that it uses ActiveSheet, which can cause it to fail if the wrong sheet is the active one. However it should explain the concept of the email and attachment objects being Set prior to trying to use them. You were doing this for the first object but not the second.

Public Sub Save_Range_As_PDF_and_Send_Email_PO()

    Dim PDFrange As Range, PDFfile As String
    Dim toEmail As String, emailSubject As String, HTMLBody As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
   
    With ActiveWorkbook
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        PDFfile = .ActiveSheet.Range("C7").Value
    End With
   
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached
    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached purchase order. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                   
        .Attachments.Add PDFfile
        '.Send
        .Display
    End With

    'Delete the temporary PDF file
    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Thank you for the update. I am afraid that using this code now I get the error "Compile error: User-defined type not defined".

As what I am trying to achieve is quite basic, would you or anyone have a full code sample for just creating a PDF on same sheet as the VBA is and then attach it to an email? I guess from there I can tweak it to my personal workbook.

I checked other threads and looks that it´s a common problem after upgrading to newer Excel versions however I can´t see any resolution and see any full sample in this forum.

Cheers.

 

[Micron]

That error is usually caused by not having the required reference(s). Hard to be specific if you don't say which line, the error number and its message. Maybe you need a reference to Office but don't have it set. In the vb editor, Tools>References, look for one that you think you might need. You select with the checkbox, not by highlighting the line. After closing the dialog, compile the code.

 

[FormR]

Hi, I think you should try replacing all of this section:

  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = ActiveSheet.Name & ".pdf"

With..

  PdfFile = ActiveWorkbook.Path & "\" & ActiveSheet.Name & ".pdf"

 

[colzre]

Thank you both.

You have been of a great help!!

I found the solution so I am copying below the coding so I can help everybody. It is resolved now.

* VBA creates a PDF of the sheet you are in and then attaches the PDF with the sheet name to an Outlook email (I am doing this in version 365).

Sub pdf_email()




  Dim IsCreated As Boolean

  Dim i As Long

  Dim PdfFile As String, Title As String

  Dim OutlApp As Object



  ' For what the Title is

  Title = Range("A1")



  ' Define PDF filename

  PdfFile = ActiveWorkbook.FullName

  i = InStrRev(PdfFile, ".")

  If i > 1 Then PdfFile = Left(PdfFile, i - 1)

  PdfFile = ThisWorkbook.Path & "\" & ActiveSheet.Name & ".pdf"



  ' Export activesheet as PDF

  With ActiveSheet

    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False



 End With



 ' Use already open Outlook if possible

  On Error Resume Next

  Set OutlApp = GetObject(, "Outlook.Application")

  If Err Then

    Set OutlApp = CreateObject("Outlook.Application")

    IsCreated = True

  End If

  OutlApp.Visible = True

  On Error GoTo 0


  ' Prepare e-mail with PDF attachment

  With OutlApp.CreateItem(0)



    ' Prepare e-mail

    .Subject = Title

    .Display

    .To = "info@outlook.com"


    .htmlbody = "<p style='font-family:calibri;font-size:15'>Good morning,</font></p>" & _

    "<p style='font-family:calibri;font-size:15'>Please find attached the report.</font></p>" & _

     "<p style='font-family:calibri;font-size:15'>Kind regards,</font></p>" & .htmlbody


    .Attachments.Add PdfFile

    .Display


  ' Delete PDF file

  Kill PdfFile


 End With

End Sub

 

[FormR]

Hi, you don't need these lines at all:

  PdfFile = ActiveWorkbook.FullName

  i = InStrRev(PdfFile, ".")

  If i > 1 Then PdfFile = Left(PdfFile, i - 1)

Because immediately after them you replace the variable with:

PdfFile = ThisWorkbook.Path & "\" & ActiveSheet.Name & ".pdf"