Option Explicit
Sub SendEmailsWithAttachment()
' Worksheet containing the data.
Dim wsData As Worksheet
Dim sSenderCompanyName As String
Dim sInvoiceDate As String
Dim sRecipientName As String
Dim sEmailAddress As String
Dim sEmailSubject As String
Dim sInvoiceTotal As String
Dim sIsEmailed As String
Dim sIsPaid As String
Dim sFileNameAndPath As String
Dim sEmailBody As String
Dim iDataRow As Long
Dim iDataRowsCount As Long
sSenderCompanyName = "Hotel California"
Set wsData = ThisWorkbook.Worksheets("Sheet1")
' Get count of data rows to proess, minus one to account for header row.
iDataRowsCount = wsData.Range("A1").CurrentRegion.Rows.Count - 1
' Iterate through the entries in the data.
With wsData.Range("A1").Offset(1)
For iDataRow = 1 To iDataRowsCount
sIsEmailed = UCase(Left(.Cells(iDataRow, 12).Value, 1))
sIsPaid = UCase(Left(.Cells(iDataRow, 14).Value, 1))
If sIsEmailed = "N" And sIsPaid <> "Y" _
Then
sRecipientName = .Cells(iDataRow, 3)
sFileNameAndPath = .Cells(iDataRow, 16)
' Tell user if the attachment was not found.
If Dir(sFileNameAndPath) = "" _
Then
MsgBox "The attachment for recipient " & sRecipientName & " was not found." _
& Chr(10) & Chr(10) _
& sFileNameAndPath, vbExclamation
Else
sInvoiceDate = .Cells(iDataRow, 2)
sInvoiceTotal = Format(.Cells(iDataRow, 11), "$#,##0.00")
sEmailSubject = sSenderCompanyName & " invoice"
sEmailAddress = .Cells(iDataRow, 6)
' Create the email body.
sEmailBody = "Hello " & sRecipientName _
& ". Attached please find your invoice dated " _
& sInvoiceDate & " for " _
& sInvoiceTotal & "."
' Call function that does the send. Two "empty" commas are for CC and BCC fields which are N/A.
Call SendEmail(sEmailAddress, sEmailSubject, sEmailBody, , , sFileNameAndPath, True)
' Set Emailed ? and Emailed Date data for the person.
.Cells(iDataRow, 12).Value = "Y"
.Cells(iDataRow, 13).Value = Format(Now(), "m/d/yyyy")
End If
End If
Next iDataRow
End With
End Sub
