Can Excel "SEND" an email via Outlook based on criteria in spreadsheet?

12,668

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
Share:
12,668

Related videos on Youtube

Admin
Author by

Admin

Updated on September 18, 2022

Comments

  • Admin
    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.