Can Excel "SEND" an email via Outlook based on criteria in spreadsheet?
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
Related videos on Youtube
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on September 18, 2022Comments
-
Admin almost 2 years
Is there an easy way to get
Excel (2013)
to actually
send an email (Outlook or Exchange)
if certain criteria is met?
For instance:if A2 is between 80 and 90, send email to G2.
A2 would be the result of employee start date to
today's date
, an 80 to 90 result meaning 90 day review is needed.
G2 would have the actual email address of the supervisor, manager, etc.
This process would happen automatically without having to go into each cell to grab the email address and send the email one by one.