This Sub
read the selected cells (column) where the condition is applied, execute the test if True read the email, subject, body and send the email and write Sent
at the same row after sending
It works with Outlook
Column 1 Column 2 Column 3 column 4 column 5 column 6
80 email Manager Name Body Text Employee Name Sent or empty
You can change cell(s,c+2), cell(s,c+4)... to correspond to your columns
for example G2 (column 2) will be cell(s,c+6) if A2 is column 1 and move the others according to your Data
You have to select the cells in column 1 and the Sub will continue
Sub SendReminderMail()
Dim s As Long, c As Long
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim strBody As String
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
For Each Cell In Selection
Cell.Select
s = ActiveCell.Row
c = ActiveCell.Column
If Cells(s, c).Value > 80 And Cells(s, c).Value < 90 Then
strBody = Cells(s, c + 3) & " " & Cells(s, c + 4)
Set OutLookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem
.To = Cells(s, c + 1).Value
.Subject = "Reminder: "
.Body = "Dear " & Cells(s, c + 2).Value & "," & vbCrLf & vbCrLf & strBody
.Display ' or .Send
End With
Cells(s, c + 5) = "Sent"
End If
Next Cell
End Sub
2
Take a look at this Send a mail when a cell reach certain value
– yass – 2017-03-30T07:12:04.567I can only recommend the commercial product that does exactly what you want. – thims – 2017-03-30T15:54:09.997
@yass, consider posting an answer that incorporates that solution. This question comes up occasionally, and it could be useful to have an answer resident here. It could be mostly copy/paste and attribution. – fixer1234 – 2017-03-30T22:49:31.517