VBA Highlight files which cann't be attached

leemcder

New Member
Joined
Feb 26, 2018
Messages
40
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

VBA Code:
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
56
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 11, 2020
Messages
56
Office Version
  1. 365
Platform
  1. Windows
Disregard my last post. Are the files that are not attaching in columns E:F?
 

leemcder

New Member
Joined
Feb 26, 2018
Messages
40
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,896
Members
412,752
Latest member
LUIS SAMANO
Top