making sure file exist if cell is not empty

[dcharland]

Hi,

I would need help with the following:

I have 4 possible cells where user can enter the location of a file they would like to attache to their email.

The step i'm looking to achieve are:

1. If cell as a value in it
2. Check if file exist
3. if it does good, check next
4. if it doesn't store value
5. display a msgbox with the list of files that don't exist.

This check is being planned at the beginning of the script as want all of the checks to pass prior to building the email. Since the macros has option to use a outlook template file or use values from the spreadsheet (or a combination of) I need to have some user inputs. That being said this check is the last one that I need to do After, I'll go into upgrades to the spreadsheet.

This is the current code that I have, but it's really not working and i'm totally lost Not even sure that it will be helpful?

check = 0
                For row = 9 To 15 Step 2
                 If Dir(Cells(row, "C").Value) > 1 And (Cells(row, "C").Value) <> "" Then
                   check = check + 1
                 End If
                 Next row

 

[GlennUK]

Your code looks close to what you're asking for. Does this do better:

check = 0
 strmessage=""
                For row = 9 To 15 Step 2
                 If Dir(Cells(row, "C").Value) > 1 And (Cells(row, "C").Value) <> "" Then
                   check = check + 1
                 Else
                    strmessage=strmessage & Cells(row, "C").Value & vbCrLf
                 End If
                 Next row
                 if check < 4 Then
                     MsgBox "File(s) not found, as follows:" & vbCrLf & strmessage
                     End ' end the macro here, or whatever other actions are required
                 End If

 

[dcharland]

Thanks that is exactly what I was trying to achieve.

 

[GlennUK]

That's great! You were quite close with your initial attempt.