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.

You've verified the path and you've verified the name, but you haven't verified the combination of path + filename. Which must be separated by a "\" character :) — David Zemens, Aug 01 '18 at 17:31
What O/S are you running that recognizes triple dots in a file specification? That had me scratching my head. https://blogs.msdn.microsoft.com/oldnewthing/20160202-00/?p=92953 — Bill Hileman, Aug 01 '18 at 17:35
@BillHileman that's (most likely) just a placeholder for the actual path, redacted due to some privacy concern/etc. — David Zemens, Aug 01 '18 at 17:35
I tried it with WIndows 10 command prompt, it returns no error and does nothing, i.e. from \Windows\User CD ... does nothing. but CD .. does as expected and moves to parent. There is no grandparent shortcut. — Bill Hileman, Aug 01 '18 at 17:36
@DavidZemens I thought perhaps too, but it looks like it's being literally used in the code. — Bill Hileman, Aug 01 '18 at 17:38
It doesn't look like that any more than it looks like it's just a shorthand way of doing "C:\fake\path\to\your\file\directory\", IMO. — David Zemens, Aug 01 '18 at 17:39

score 3 · Accepted Answer · answered Aug 01 '18 at 17:24

3

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\"

answered Aug 01 '18 at 17:24

Vityata

42,633
8
55
100

Ahhh so silly of me, thanks a lot. I have one more question-- your amendment worked perfectly in the case where I know the exact file name. For another attachment I'm adding, the file name has the "Type" in it. So it's like "Excel file type A - (Please insert your name here).xslx" or "Excel File type B - (Please insert your name here).xslx". I'm trying to add the path as such: .Attachments.Add fileLocationSSH & "\" & dType & "Excel file type " & dType & "- (Please insert your name here)" & ".xlsx" And it's giving me the same error. Is what I'm trying to do even possible? Thanks again! – Tester_Y Aug 01 '18 at 17:40
1

@Tester_Y - to see the problem simply write `MsgBox fileLocationSSH & "\" & dType & "Excel file type " & dType & "- (Please insert your name here)" & ".xlsx"`. Then examine the path. Or even `Debug.Print` instead of the `MsgBox`, so you can copy and paste. https://stackoverflow.com/questions/2916287/where-does-vba-debug-print-log-to – Vityata Aug 01 '18 at 17:45
Using that made me realize that the code is not taking the & dType& in the actual file name. It's displaying as "Excel file type - (Please insert your name here).xslx". Is there any way to amend the path based on differing file names? If not, then I will have to manually change all the file names to be the same...hmm. – Tester_Y Aug 01 '18 at 17:48
@Tester_Y - there should be a way to change the path, definitely. Just make sure to write a [mcve] and ask a new question, I guess you would get an answer within less than 15 minutes. It is mid-day in the US, people are quite active in SO :) – Vityata Aug 01 '18 at 17:54
1

Thanks, figured it out with your help! – Tester_Y Aug 01 '18 at 19:19

score 0 · Answer 2 · answered Aug 01 '18 at 19:19

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 Answers2