Hi,
I'm new to macros and I'm trying to compile together a code that will allow me to send mass emails with personalized bodies and attachments. I would like two attachments to be in every single email, and to be able to add as many invoices each client has in that email as well. The minimum is one and it can go up to 7. The problem is when I run the code it shows an error because some rows have only one attachment while others may have two or three.
I show the current code below any help is highly appreciated! Thank you so much.
Sub sendCustEmails()
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
Dim objEmail As Object
intRow = 2
strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text
While (strClientID <> "")
Set objEmail = objOutlook.CreateItem(olMailItem)
strMailSubject = ThisWorkbook.Sheets("Mail_Details").Range("A2").Text
strMailBody = ThisWorkbook.Sheets("Mail_Details").Range("B2").Text
strMonth = ThisWorkbook.Sheets("Mail_Details").Range("C2").Text
strfolder = "C:\Users\yshriki\OneDrive - GLSC & Company\Desktop\Invoices"
strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text
strEmail = ThisWorkbook.Sheets("Client_Data").Range("B" & intRow).Text
strCC = ThisWorkbook.Sheets("Client_Data").Range("C" & intRow).Text
strAmount = ThisWorkbook.Sheets("Client_Data").Range("C" & intRow).Text
strfilename = ThisWorkbook.Sheets("Client_Data").Range("D" & intRow).Text
strfilename2 = ThisWorkbook.Sheets("Client_Data").Range("D" & intRow).Text
strMailSubject = Replace(strMailSubject, "<ClientID>", strClientID)
strMailBody = Replace(strMailBody, "<MONTH>", strMonth)
strMailBody = Replace(strMailBody, "<Amount>", strAmount)
' to run only when cell is not blank in column B introw
With objEmail
.To = CStr(strEmail)
.Subject = strMailSubject
.Body = strMailBody
If found Then
.Attachments.Add strfilename
.Attachments.Add strfilename2
End If
.Attachments.Add "C:\Users\yshriki\OneDrive - GLSC & Company\Desktop\Invoices\Credit_Card_Authorization_Form_!.pdf"
.Attachments.Add "C:\Users\yshriki\OneDrive - GLSC & Company\Desktop\Invoices\Wire Transfer Instructions.pdf"
.Send
End With
intRow = intRow + 1
strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text
Wend
MsgBox "Done"
End Sub
Bookmarks