how to incorporate error handling into this

[bdouglas1011]

I have this code

With olEmail
        .Display
        .HTMLBody = EmailBody & .HTMLBody
        .To = GetSurveyEmailDistList
        .CC = GetSurveyEmailCCList & "[EMAIL]Remote@legacydirectional.com[/EMAIL]"
                .Subject = ESubject
       
    'Attach files. The var "i" should start at the first row of file names, and go to the last row
                                 
        For i = row + 2 To row + 15
        If cells(i, col).Value = "Yes" Then
        .Attachments.Add (Directory & cells(i, col + 2).Value)
        End If
        Next i

    End With

Sometimes it looks for a file that did not get generated but I would like it to continue and just give a message to correct it for next time.
It is a runtime error saying cant find file verify path and name are correct.

Is this possible

 

[Domenic]

Try . . .

        Dim currentFilename As String

        For i = Row + 2 To Row + 15
            If Cells(i, col).Value = "Yes" Then
                currentFilename = Directory & Cells(i, col + 2).Value
                If Len(Dir(currentFilename, vbNormal)) > 0 Then
                    .Attachments.Add currentFilename
                Else
                    MsgBox "'" & currentFilename & "' does not exist!", vbExclamation
                End If
            End If
        Next i

Hope this helps!

 

[6StringJazzer]

Here is one way:

With olEmail
        .Display
        .HTMLBody = EmailBody & .HTMLBody
        .To = GetSurveyEmailDistList
        .CC = GetSurveyEmailCCList & "Remote@legacydirectional.com"
                .Subject = ESubject
      
    'Attach files. The var "i" should start at the first row of file names, and go to the last row
                                
        For i = row + 2 To row + 15
        If cells(i, col).Value = "Yes" Then
        On Error Resume Next
        .Attachments.Add (Directory & cells(i, col + 2).Value)
        If Err.Number > 0 Then
            Err.Clear
            MsgBox "File """ & Directory & cells(i, col + 2).Value & """ was not found, no attachment will be added."
        End If
        On Error GoTo 0
        Next i

    End With

 

[bdouglas1011]

Here is one way:

With olEmail
        .Display
        .HTMLBody = EmailBody & .HTMLBody
        .To = GetSurveyEmailDistList
        .CC = GetSurveyEmailCCList & "Remote@legacydirectional.com"
                .Subject = ESubject
     
    'Attach files. The var "i" should start at the first row of file names, and go to the last row
                               
        For i = row + 2 To row + 15
        If cells(i, col).Value = "Yes" Then
        On Error Resume Next
        .Attachments.Add (Directory & cells(i, col + 2).Value)
        If Err.Number > 0 Then
            Err.Clear
            MsgBox "File """ & Directory & cells(i, col + 2).Value & """ was not found, no attachment will be added."
        End If
        On Error GoTo 0
        Next i

    End With

You get a next without for error on this one

 

[bdouglas1011]

Let me also add the filename is unknow it is usually a blank in the range because someone forgot to uncheck the file. So essentially it will see a blank cell and that is the error because it thought a file name would be there

 

[Joe4]

You get a next without for error on this one

He actually forgot the second "End If", not a For/Next (Excel gets confused when something like that is missing).
The part in red is what is missing (it is more evident when the code is properly indented):

With olEmail
        .Display
        .HTMLBody = EmailBody & .HTMLBody
        .To = GetSurveyEmailDistList
        .cc = GetSurveyEmailCCList & "Remote@legacydirectional.com"
                .Subject = ESubject
    
    'Attach files. The var "i" should start at the first row of file names, and go to the last row
                              
        For i = Row + 2 To Row + 15
            If Cells(i, col).Value = "Yes" Then
                On Error Resume Next
                .Attachments.Add (Directory & Cells(i, col + 2).Value)
                If Err.Number > 0 Then
                    Err.Clear
                    MsgBox "File """ & Directory & Cells(i, col + 2).Value & """ was not found, no attachment will be added."
                End If
                On Error GoTo 0
            End If
        Next i

    End With

 

[Domenic]

I have amended my code to check for blank cells before it checks for an existing file . . .

        Dim currentFilename As String

        For i = Row + 2 To Row + 15
            If Cells(i, col).Value = "Yes" Then
                If Len(Cells(i, col + 2)) > 0 Then
                    currentFilename = Directory & Cells(i, col + 2).Value
                    If Len(Dir(currentFilename, vbNormal)) > 0 Then
                        .Attachments.Add currentFilename
                    Else
                        MsgBox "'" & currentFilename & "' does not exist!", vbExclamation
                    End If
                End If
            End If
        Next i

 

[bdouglas1011]

He actually forgot the second "End If", not a For/Next (Excel gets confused when something like that is missing).
The part in red is what is missing (it is more evident when the code is properly indented):

With olEmail
        .Display
        .HTMLBody = EmailBody & .HTMLBody
        .To = GetSurveyEmailDistList
        .cc = GetSurveyEmailCCList & "Remote@legacydirectional.com"
                .Subject = ESubject
  
    'Attach files. The var "i" should start at the first row of file names, and go to the last row
                            
        For i = Row + 2 To Row + 15
            If Cells(i, col).Value = "Yes" Then
                On Error Resume Next
                .Attachments.Add (Directory & Cells(i, col + 2).Value)
                If Err.Number > 0 Then
                    Err.Clear
                    MsgBox "File """ & Directory & Cells(i, col + 2).Value & """ was not found, no attachment will be added."
                End If
                On Error GoTo 0
            End If
        Next i

    End With

That worked it did not give the error but the message box did not show either

 

[Joe4]

That worked it did not give the error but the message box did not show either

I was just fixing the error in his code, but did not evaluate if I thought it does what you want/need or not.
Looks like Domenic posted an updated potential solution for you too.

 

[bdouglas1011]

I have amended my code to check for blank cells before it checks for an existing file . . .

        Dim currentFilename As String

        For i = Row + 2 To Row + 15
            If Cells(i, col).Value = "Yes" Then
                If Len(Cells(i, col + 2)) > 0 Then
                    currentFilename = Directory & Cells(i, col + 2).Value
                    If Len(Dir(currentFilename, vbNormal)) > 0 Then
                        .Attachments.Add currentFilename
                    Else
                        MsgBox "'" & currentFilename & "' does not exist!", vbExclamation
                    End If
                End If
            End If
        Next i

I just ran your too and it too does not display the MsgBox....it took care of the error though