VBA Highlight files which cann't be attached

[leemcder]

Hi, i'm hoping someone can help me adjust this code code from Ron de Bruin. It attaches documents to an outlook email, where the file path is in cell E and F. It works great but I'd like to highlight in yellow any files it could not attach. Is this possible? Many thanks

Sub Send_Files()

'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)

    'Enter the path/file names in the E:F column in each row
    Set rng = sh.Cells(cell.Row, 1).Range("E1:F1")
    
    If cell.Value Like "?*@?*.?*" And _
    Application.WorksheetFunction.CountA(rng) > 0 Then
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
            .to = sh.Cells(cell.Row, 1).Value
            .CC = sh.Cells(cell.Row, 2).Value
            .Subject = sh.Cells(cell.Row, 3).Value
            .Body = sh.Cells(cell.Row, 4).Value
            
            For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                If Trim(FileCell.Value) <> "" Then
                    If Dir(FileCell.Value) <> "" Then
                        .Attachments.Add FileCell.Value
                    End If
                End If
            Next FileCell
            
            .display 'Or use .Display/Send
        End With
        
        Set OutMail = Nothing
    End If
Next cell

Set OutApp = Nothing

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

 

[ManiacB]

You can make it attach any columns you want. We have to know how many columns are used in the spreadsheet. Do you want to highlight the headers of the lines only?

 

[ManiacB]

Disregard my last post. Are the files that are not attaching in columns E:F?

 

[leemcder]

Hi, the file paths are in columns E and F. Ideally I'd want it to highlight the cells in column E or F of any attachments that cannot be found.

You can make it attach any columns you want. We have to know how many columns are used in the spreadsheet. Do you want to highlight the headers of the lines only?