3

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.

  • 2
    Take a look at this Send a mail when a cell reach certain value – yass Mar 30 '17 at 7:12
  • I can only recommend the commercial product that does exactly what you want. – thims Mar 30 '17 at 15:54
  • @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 Mar 30 '17 at 22:49
2

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.