Run-time error '-214702894(80070002)' - Cannot Find This File.

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
758
Hello friends, hope all is well! Please help me with the code.
it supposed to take the sheet PDF, convert it to a pdf file and send it via outlook. I am getting the run time error.

The Debug is highlighting the line: .Attachments.Add PdfFile

Thank you so much in advance!


Code:
 Private Sub Email()
    
    Set OutApp = GetObject(, "Outlook.Application")
    Dim IsCreated As Boolean
    Dim i As Long
    Dim ab, ac, ad, emTo, emCC As String
    Dim PdfFile As String, Title As String
    Dim OutlApp As Object

    'Application.ScreenUpdating = False
    
    emTo = Worksheets("DB").Range("B10").Value
    emCC = Worksheets("DB").Range("B14").Value
    ab = Worksheets("DB").Range("AP25").Value
   
    ' Not sure for what the Title is
   Title = " - " & Sheets("DB").Range("D6")
    TitleF = Sheets("DB").Range("D6") & " - " & Sheets("PDF").Range("F8") & " - " & Format(ab, "ddd dd-mmm-yyyy")
    
    ' Title & " - " & ab
    ' Define PDF filename
    PdfFile = ActiveWorkbook.FullName
    i = InStrRev(PdfFile, ".")
    If i > 1 Then PdfFile = Left(PdfFile, i - 1)
    PdfFile = Sheets("DB").Range("D6") & " - " & Sheets("PDF").Range("F8") & ".pdf"
     
     
    'Specify the worksheet name
    Sheets("PDF").Activate
    ActiveSheet.UsedRange.Select
    'ThisWorkbook.Sheets(Array("PDF")).Select
     
  Set xsht = ThisWorkbook.Sheets("PDF")
     
    xsht.ExportAsFixedFormat Type:=xlTypePDF, _
                                       Filename:=PdfFile, _
                                       Quality:=xlQualityStandard, _
                                       IncludeDocProperties:=True, _
                                       IgnorePrintAreas:=False, _
                                       OpenAfterPublish:=False
     
    ' 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 = TitleF
        .to = emTo ' <-- Put email of the recipient here
        .CC = emCC ' <-- Put email of 'copy to' recipient here
        .HTMLBody = "Greetings, " & vbLf & vbLf _
                    & "<p> The attachment here displays the results of the candidate: " & Sheets("pdf").Range("f8") & vbLf _
                    & "<p> Please open the attachment to see the number of correct and answers and correct the 5 essay questions." & vbLf _
                    & "<p><i>for your information, the candidate scored " & Sheets("PDF").Range("E11") & " in MCQs</i>" & vbLf & vbLf _
                    & "<p><p>All the Best Regards,<br>" & vbLf _
                    & " " & vbLf & vbLf

                .Attachments.Add PdfFile
        ' Try to send
        On Error Resume Next
        Set .SendUsingAccount = OutlApp.Session.Accounts.Item(1)   'Use 2nd Account in the  list
 
        .DISPLAY
        Application.Wait (Now + TimeValue("0:00:01"))
        Application.SendKeys "%s"
         
        Application.Visible = True
        
        On Error GoTo 0
    End With
    
    ' Delete PDF file
    Kill PdfFile
    
    ' Quit Outlook if it was created by this code
    If IsCreated Then OutlApp.Quit
    
    ' Release the memory of object variable
    Set OutlApp = Nothing
 
Application.ScreenUpdating = True

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top