Runtime Error - Cannot Find This File; Verify Name & File Path Correct (Excel / Vba)

Question

Running into error message in title when attempting to link attachments to email. The attachments are stored in Folder Names respective to the "type" of company, which is why I'm attempting to add a for loop to retrieve "type" from spreadsheet.

Sub mailTest()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim olAttachmentLetter As Outlook.Attachments    
Dim fileLocationLetter As String
Dim dType As String

For i = 2 To 3

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    Set olAttachmentLetter = olMail.Attachments
    fileLocationLetter = "C:\...\user\Desktop\FileLocation"
    letterName = "TestLetter1"
    dType = Worksheets("Test1").Cells(i, 2).Value

    mailBody = "Hello " _
                & Worksheets("Test1").Cells(i, 4) _
                & "," _
                & Worksheets("BODY").Cells(2, 1).Value _
                & Worksheets("BODY").Cells(3, 1).Value _
                & Worksheets("BODY").Cells(4, 1).Value & " " & dType _
                & Worksheets("BODY").Cells(5, 1).Value & " TTT" & dType & "xx18" _
                & Worksheets("BODY").Cells(6, 1).Value _
                & Worksheets("BODY").Cells(7, 1).Value

     With olMail
        .To = Worksheets("Test1").Cells(i, 5).Value
        .Subject = Worksheets("Test1").Cells(i, 3).Value & " - "
        .HTMLBody = "<!DOCTYPE html><html><head><style>"
        .HTMLBody = .HTMLBody & "body{font-family: Calibri, ""Times New Roman"", sans-serif; font-size: 13px}"
        .HTMLBody = .HTMLBody & "</style></head><body>"
        .HTMLBody = .HTMLBody & mailBody & "</body></html>"

        ''Adding attachment
        .Attachments.Add fileLocationLetter & letterName & ".pdf"
        .Display
        '' .Send (Once ready to send)
    End With
    Set olMail = Nothing
    Set olApp = Nothing
Next
End Sub

What am I doing wrong here? The file is stored in 'C:...\user\Desktop\FileLocation\TestLetter1.pdf'

Thank you kindly.

Accepted Answer

You are missing the \ between the fileLocation and the letterName. Thus, either write this:

.Attachments.Add fileLocationLetter & "\" & letterName & ".pdf"

or this:

fileLocationLetter = "C:\...\user\Desktop\FileLocation\"

Answer 2

With much help from @Vityata, figured it out.

Essentially being able to make two attachments, one is static with known file name, the second attachment's name is dependent on stored cell value. The workaround was to break the path/name of the file as stored strings. Maybe there's an easier way, but this worked for me!

Code used:

Sub mailTest()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

'' Identify Attachments
Dim olAttachmentLetter As Outlook.Attachments
Dim olAttachmentSSH As Outlook.Attachments

'' Identify Attachment Locations / Paths
Dim fileLocationLetter As String
Dim fileLocationSSH As String
Dim fileLocationSSHi As String
Dim fileLocationSSHii As String

 '' Type Variable, referencing cell in worksheet where "Type" is stored (in loop below)
 Dim dType As String

 '' Creating the loop - Replace 4 with end of rows. Will eventually create code to automatically identify the last cell with stored value
For i = 2 To 4

     Set olApp = New Outlook.Application
     Set olMail = olApp.CreateItem(olMailItem)


     Set olAttachmentLetter = olMail.Attachments
     Set olAttachmentSSH = olMail.Attachments


     ''File Location for Letter
     fileLocationLetter = "C:\...\Directory"

     ''File Location for Excel sheet - Need 3 fields as file name is dynamic based on loop value
     fileLocationSSH = "C:\...\Directory\Excel Files"
     fileLocationSSHi = "Beginning of File name..."
     fileLocationSSHii = " ... End of File name"


     letterName = "Name of PDF attachment"


     dType = Worksheets("Test1").Cells(i, 2).Value

     ''Body of Email - Each new line represents new value (linking to hidden worksheet in Excel doc)
     mailBody = "Hello " _
                 & Worksheets("Test1").Cells(i, 4) _
                 & "," _
                 & Worksheets("BODY").Cells(2, 1).Value _
                 & Worksheets("BODY").Cells(3, 1).Value _
                 & Worksheets("BODY").Cells(4, 1).Value & " " & dType _
                 & Worksheets("BODY").Cells(5, 1).Value _
                 & Worksheets("BODY").Cells(6, 1).Value _
                 & Worksheets("BODY").Cells(7, 1).Value


     With olMail
         .To = Worksheets("Test1").Cells(i, 5).Value
         .Subject = Worksheets("Test1").Cells(i, 3).Value 
         .HTMLBody = "<!DOCTYPE html><html><head><style>"
         .HTMLBody = .HTMLBody & "body{font-family: Calibri, ""Times New Roman"", sans-serif; font-size: 13px}"
         .HTMLBody = .HTMLBody & "</style></head><body>"
         .HTMLBody = .HTMLBody & mailBody & "</body></html>"

      '' Adding attachments, referencing file locations and amending file name if needed
         .Attachments.Add fileLocationLetter & "\" & letterName & ".pdf"
         .Attachments.Add fileLocationSSH & "\" & dType & "\" & fileLocationSSHi & dType & fileLocationSSHii & ".xlsx"

            .Display
         '' .Send (Once ready to send)

    End With


    Set olMail = Nothing
    Set olApp = Nothing

Next



End Sub

Runtime Error - Cannot Find This File; Verify Name & File Path Correct (Excel / Vba)

2 Answers

Must Read

Your Answer

Sophia Al-Mansoor

Runtime Error - Cannot Find This File; Verify Name & File Path Correct (Excel / Vba)

2 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Sophia Al-Mansoor