You can't use Target outside of the sheet module.
Pass the loop object to the email sub so you can insert data required.
Dim foundemail As Range
Dim a As Range
Set Rng1 = Range("D2:D10")
For Each a In Rng1
If a.Value = 10 Then
Set foundemail = Sheets("Email").Range("A:A").Find(What:=Cells(a.Row, 1), _
LookAt:=xlWhole, _
MatchCase:=False)
If foundemail Is Nothing Then
MsgBox "Cannot match name on Email address sheet. ", vbExclamation, "No Match Found"
Else
Call Sendmail(a, foundemail.Offset(, 1).Value2)
End If
End If
Next
End Sub
Sub Sendmail(ObjA As Range, Email As String)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
sCC = ""
sBCC = ""
sSubject = "Reminder! Task Overdue"
strbody = "Dear " & ObjA.Offset(, -3) & "," & vbNewLine & vbNewLine & _
"Task:" & " " & ObjA.Offset(, -2) & vbNewLine & vbNewLine & _
"Due Date:" & " " & ObjA.Offset(, -1) & _
vbNewLine & vbNewLine & " Thank You. "
With OutMail
.To = Email
.CC = sCC
.BCC = sBCC
.Subject = sSubject
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Bookmarks