VBA to skip attaching files to email when file is not found in the folder, but attach if found

Question

Need help with VBA to skip attaching file if file is not found in the folder i have tried adding the "if then" but no luck there. would someone please be able to help here I want the vba to skip if file is not found and attach other files that are found only.

With objEmail
.To = 
.Cc = 
.Subject = 
'.HTMLBody = RangetoHTML(rng)
.HTMLBody = StrBody2 & "<br>" & RangetoHTML(rng) & "<br>" & StrBody3 & "<br>" & .HTMLBody

 pdfFileName1 = Dir(pdfFolderPath1 & "file.pdf")
 pdfFileName5 = Dir(pdfFolderPath4 & "file2.pdf")
 pdfFileName6 = Dir(pdfFolderPath4 & "file3.pdf")
 pdfFileName2 = Dir(pdfFolderPath2 & "file4.pdf")
 pdfFileName3 = Dir(pdfFolderPath3 & "file5.pdf")
 pdfFileName4 = Dir(pdfFolderPath3 & "file6.pdf")
       
            pdfFilePath1 = pdfFolderPath1 & pdfFileName1
            pdfFilePath2 = pdfFolderPath2 & pdfFileName2
            pdfFilePath3 = pdfFolderPath3 & pdfFileName3
            pdfFilePath4 = pdfFolderPath3 & pdfFileName4
            pdfFilePath5 = pdfFolderPath4 & pdfFileName5
            pdfFilePath5 = pdfFolderPath4 & pdfFileName6
                
                .Attachments.Add pdfFilePath1, , , pdfFileName1
           
                 .Attachments.Add pdfFilePath2, , , pdfFileName2
            
              pdfFileName = Dir
            
        
                .Attachments.Add pdfFilePath3, , , pdfFileName3
                .Attachments.Add pdfFilePath4, , , pdfFileName4
                .Attachments.Add pdfFilePath5, , , pdfFileName5
                .Attachments.Add pdfFilePath5, , , pdfFileName6
                    
       
.Display ' DISPLAY MESSAGE.

End With
With Application

End Sub

look here https://stackoverflow.com/q/16351249/16578424 - there are several methods described how to check if a file exists — Ike, Aug 22 '23 at 14:32

score 3 · Answer 1 · answered Aug 22 '23 at 14:42

In programming, you don't need "luck" - you need to be precise.

With Dir, you can check if a file exists. If not, Dir returns the empty string and you simply omit this file.

I would suggest you add a small subroutine for checking and attaching a file so that the code that creates the mail can be simplified. Don't know if you use early or late binding, so I go with late binding:

Sub AddFileAsAttachment(eMail As Object, path As String, filename As String)
    Dim fullName As String
    If Right(path, 1) = "\" Then
        fullName = path & filename
    Else
        fullName = path & "\" & filename
    End If
    If Dir(fullName) = "" Then Exit Sub   ' File not found
    eMail.Attachments.Add fullName, , , filename
End Sub

...and in your code to create the mail (not sure about your path and filenames, maybe you have to adapt that):

AddFileAsAttachment objMail, pdfFolderPath1, "file.pdf"
AddFileAsAttachment objMail, pdfFolderPath4, "file2.pdf"
AddFileAsAttachment objMail, pdfFolderPath4, "file3.pdf"
AddFileAsAttachment objMail, pdfFolderPath2, "file4.pdf"
AddFileAsAttachment objMail, pdfFolderPath3, "file5.pdf"
AddFileAsAttachment objMail, pdfFolderPath3, "file6.pdf"
.Display

VBA to skip attaching files to email when file is not found in the folder, but attach if found

1 Answers1