Checking for attachments before sending the emails using VBA

Question

I have a macro to draft automatic emails based on the recipients in each columns.

However, I'm looking for a code which can if the attachments named in the excel sheet are attached to the email. If there is any attachment missing from that email it should show a msg box with the name of the missing attachment. SNip of one the sheets attached

Sub Email1()



Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

Dim FLNM As String
Dim AttchmentName As String
Set AddressList = Sheets("Tracker Summary").Range("Y:Z")

Dim AttchmentName1 As String
Dim path As String

Call FetchFileNames

path = ThisWorkbook.path & "/"

Dim i As Integer

i = 5

With olMail

ActiveSheet.Range("A1").Select
.BodyFormat = olFormatHTML

    .Display
    .To = ActiveSheet.Cells(2, i).Value
    .CC = ActiveSheet.Cells(3, i).Value
    .Subject = ActiveSheet.Cells(4, i).Value
    .HTMLBody = ActiveSheet.Cells(5, i).Value & .HTMLBody
    j = 6

    Do Until IsEmpty(Cells(j, i))

    On Error Resume Next

    FLNM = ActiveSheet.Cells(j, i).Value

    AttchmentName1 = Application.WorksheetFunction.VLookup(FLNM, AddressList, 1, True)

    If FLNM = AttchmentName1 Then

    AttchmentName = Application.WorksheetFunction.VLookup(FLNM, AddressList, 2, True)

    .Attachments.Add AttchmentName

    End If

    j = j + 1

    Loop

    '.Display

    End With

Sheets("Tracker Summary").Range("Y:Z").ClearContents

End Sub

Musicodelic Musicodelic · Accepted Answer · 2018-10-17T17:07:03

Presuming that AttachmentName is a full file path string, maybe your code could check if the file exists beforehand.

For the sake of simplicity...

If Len(Dir(AttachmentName)) = 0 then msgbox "The File " & AttachmentName & " is missing"

... Just after you set AttachmentName value at AttchmentName = Application.WorksheetFunction.VLookup(FLNM, AddressList, 2, True)

Obviously, same for any other Attachment variables.

Checking for attachments before sending the emails using VBA

1 Answers