How to send email with multiple attachments even when when one is missing?

Question

Have a simple code to send email with attachments from directory.

Problem is when one of the files is missing, email is not sent. I want to send even if only one attachment exist.

Sub Send_email_IPS()

  Dim OutApp As Object
  Dim OutMail As Object

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)

  With OutMail
    .to = "test@email.com"
    .CC = ""
    .BCC = ""
    .Subject = "Update " & Date & " " & Time
    .HTMLbody = "Hello " & "<br>" & "<br>" & "Please find attached latest update" & "<br>" & "<br>" & "Best Regards" & "<br>" & "<br>" & "Me"
    .Attachments.Add "C:\Users\testuser\Work Folders\Desktop\KB4 Reporting Macro\IPS.xlsx"
    .Attachments.Add "C:\Users\testuser\Work Folders\Desktop\KB4 Reporting Macro\IPS (St Helens).xlsx"
    .Display
  End With

  On Error GoTo 0

  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub

Check if the file exists and only add it to the `Attachments` collection if it does. The `Dir(pathname)` function is used to check that (see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function). — ssarabando, Nov 04 '22 at 10:40
I am not vba dev, but you need read directory with files and use cycle(for, while, foreach). Something like that https://stackoverflow.com/questions/63357500/vba-code-to-loop-through-files-in-a-folder — Winteriscoming, Nov 04 '22 at 10:41

Kostas K. · Accepted Answer · 2022-11-04T14:48:40.107

0

As advised in comments, add the attachment only if the file exists.

Const File1 As String = "C:\Users\testuser\Work Folders\Desktop\..."
Const File2 As String = "C:\Users\testuser\Work Folders\Desktop\..."

With OutMail
    '...
    If Len(Dir(File1)) > 0 Then .Attachments.Add File1
    If Len(Dir(File2)) > 0 Then .Attachments.Add File2
    .Display
End With

To make a note if the file was not found, use the Else condition to write to the body of the email. Change the content to fit your needs.

If Len(Dir(File1)) > 0 Then .Attachments.Add File1 Else .HTMLbody = .HTMLbody & "<p> File not found: '" & File1 & "'</p>"
If Len(Dir(File2)) > 0 Then .Attachments.Add File2 Else .HTMLbody = .HTMLbody & "<p> File not found: '" & File2 & "'</p>"

edited Nov 04 '22 at 14:48

answered Nov 04 '22 at 11:01

Kostas K.

8,293
2
22
28

Works a charm! Thank you! Any Idea how to modify this to add comment in the body of the email if attachment is not found? – Pawel Cw Nov 04 '22 at 14:41
See edit. Use the `Else` condition to write to the body. – Kostas K. Nov 04 '22 at 14:49
1

Thank you for your help! All works as intended. – Pawel Cw Nov 04 '22 at 14:59

How to send email with multiple attachments even when when one is missing?

1 Answers1