emailexceloutlookvba

Create email if file for attachment exists


I'm using Ron de Bruin's code for emailing different files to different people, as shown below.

The issue I have is, if an email address exists in column B and the corresponding workbook doesn't exist it still creates an email but with no attachment.

How could I modify the code so that if a workbook doesn't exist it doesn't create the email?

Sub Send_Files()
    'Working in Excel 2000-2013
    '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("B").Cells.SpecialCells(xlCellTypeConstants)

        'Enter the path/file names in the C:Z column in each row
        Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")

        If cell.Value Like "?*@?*.?*" And _
          Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)

            With OutMail
                .to = cell.Value
                .Subject = "Testfile"
                .Body = "Hi " & cell.Offset(0, -1).Value

                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell

                .Send  'Or use .Display
            End With

            Set OutMail = Nothing
        End If
    Next cell

    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Solution

  • You can set a flag to go to the next item if the file does not exist:

       Dim noFile as Boolean
    
       noFile = True
       For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
         If Trim(FileCell) <> "" Then
           If Dir(FileCell.Value) <> "" Then
             noFile = False
             .Attachments.Add FileCell.Value
           End If
         End If
       Next FileCell
    
       if Not noFile then .Send
    

    There are other ways to do this (see for example Sidharth Rout's suggestion which checks for the existence of files before even starting to create the email); I chose the above because it minimizes the amount of change needed in your existing code (just three lines, easy to see what they do).

    Some people would prefer to invert the logic, with a hasFile boolean:

       Dim hasFile as Boolean
    
       hasFile = False
       For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
         If Trim(FileCell) <> "" Then
           If Dir(FileCell.Value) <> "" Then
             hasFile = True
             .Attachments.Add FileCell.Value
           End If
         End If
       Next FileCell
    
       if hasFile then .Send