VBA Highlight files which cann't be attached

leemcder

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
54
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
54
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
37
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,118,358
Messages
5,571,710
Members
412,414
Latest member
KasunC
Top