Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add different attachment to each email in list

0

If someone would so kind as to review the following code, and point me in the right direction.

I'm using a table with the following headers as my list.

Email -  First Name - Last Name - Clients - Attach1 - Attach2 - Attach3 - Attach4 - Attach5

All messages will have at least one attachment but some have as many as 5.

The issue with my current code is when I read a record with only 1 attachment it errors out on the next attempt to add the attachment and the cell is empty.

Runtime error

"Cannot find this file. Verify the path and file name are correct"

emailList ThisWorkbook.Worksheets("CoordinatorList").Range("TblEmailList").Value 
 For i = LBound(emailList) To UBound(emailList)
  Set emailItem = outlookApp.CreateItem(0)
 emailEmail = emailList(i, 1)    ' Email Column
 emailName = emailList(i, 2)     ' First Name Column
 ClientName = emailList(i, 4)    ' Clients Column
 AttachedFiles1 = emailList(i, 5) ' Attachments Column
 AttachedFiles2 = emailList(i, 6) ' Attachments Column
 AttachedFiles3 = emailList(i, 7) ' Attachments Column
 AttachedFiles4 = emailList(i, 8) ' Attachments Column
 AttachedFiles5 = emailList(i, 9) ' Attachments Column
' Setup values for the email.
 emailSubject = SrvMonth & " " & "Service Logs " & "For " & ClientName
 emailBody = "<html><body> <font face=OpenSans color=#000000 size=3> </p>  <p>" & emailName & "," & "<br> Please find the attached Monthly Service Logs for all your Pine Castle ResHab Clients."
emailItem.Attachments.Add attPath & AttachedFiles1        emailItem.Attachments.Add attPath & AttachedFiles2        emailItem.Attachments.Add attPath & AttachedFiles3        emailItem.Attachments.Add attPath & AttachedFiles4        emailItem.Attachments.Add attPath & AttachedFiles5

Thank you in advance for any and all suggestions 

OldCityCat

excel
MS Office Version: 2021
posted by: OldCityCat (rep: 6) on Fri Oct 21, 2022 at 8:15 am
Answer
Discuss

Answers

0
Selected Answer

Hi OldCityCat

Assuming you have valid file names in your TblEmailList range, I suggest you replace your 5 AttachedFileX statements with a loop and With block which checks there's something in each cell, as follows (changes in bold, including first line):

emailList =ThisWorkbook.Worksheets("CoordinatorList").Range("TblEmailList") ' not .Value

 For i = LBound(emailList) To UBound(emailList)
  Set emailItem = outlookApp.CreateItem(0)
 emailEmail = emailList(i, 1)    ' Email Column
 emailName = emailList(i, 2)     ' First Name Column
 ClientName = emailList(i, 4)    ' Clients Column
' AttachedFiles1 = emailList(i, 5) ' Attachments Column
' AttachedFiles2 = emailList(i, 6) ' Attachments Column
' AttachedFiles3 = emailList(i, 7) ' Attachments Column
' AttachedFiles4 = emailList(i, 8) ' Attachments Column
' AttachedFiles5 = emailList(i, 9) ' Attachments Column
' Setup values for the email.
 EmailSubject = SrvMonth & " " & "Service Logs " & "For " & ClientName
 emailBody = "<html><body> <font face=OpenSans color=#000000 size=3> </p>  <p>" & emailName & "," & "<br> Please find the attached Monthly Service Logs for all your Pine Castle ResHab Clients."

With emailItem
    For n = 5 To 9
        If emailList(i, n) <> "" Then .Attachments.Add attPath & emailList(i, n)
    Next n

    .Display ' or. Send
End With

Next i

That will however fail if a file doesn't exist but you could use Dir to test for that (inside the n loop), something like:

For n = 5 To 9
        ' use Dir to check if a file exists
        Fl = attPath & emailList(i, n)
        ' if non-blanks, use Dir to check if a file exists

        If emailList(i, n) <> "" And Dir(Fl) <> "" Then 
               .Attachments.Add Fl                                    
        End If     

Next n
 

Hope this helps.

posted by: John_Ru (rep: 6142) on Sat Oct 22, 2022 at 9:27 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login