Sending e-mail if conditions are met

[NewbieMike]

Hello, I am trying to set up a macro that automatically sends out an e-mail if the date in column b equals today and also if the first 10 characters in columns c equal [Fastlane]. I have multiple rows data so I would need it to check each row for the 2 criteria.

I can't figure out how to do this based on multiple columns and rows, can someone help please?

 

[Wookiee]

Hello! Please try this solution.

First, place this code in the WORKSHEET where you want to evaluate for emailing criteria.

Private Sub Worksheet_SelectionChange _
  (ByVal Target As Range)

Dim rngB    As Range
Dim rngC    As Range
Dim rngCell As Range
Dim str10   As String

Set rngCell = Target
Set rngB = Range("B" & rngCell.Row)
Set rngC = Range("C" & rngCell.Row)
str10 = VBA.UCase(VBA.Left(rngC.Text, 10))

If rngCell.Value = VBA.Date Then

  If str10 = "FASTLANE" Then
  
    Call Email("To", "Subject", "Text")
    
  End If

End If

Set rngB = Nothing
Set rngC = Nothing
Set rngCell = Nothing

End Sub

Next, in your code module, enter this code:

Sub Email _
  (sTo As String, sSub As String, _
   sHTML As String, Optional sAttach As String, _
   Optional sAction As String = "Display", _
   Optional sCCs As String, Optional sFrom As String)


'Declare Variables
Dim olApp As Object
Dim olMsg As Object

'Create Email Message
Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)

On Error Resume Next
   With olMsg
    
      If sFrom <> "" Then

          .SentOnBehalfOfName = sFrom

      End If
      
      .Display
      .To = sTo
      .CC = sCCs
      .HTMLBody = sHTML & .HTMLBody
      .Subject = sSub
      
      If sAttach <> "" Then

          .Attachments.Add sAttach

      End If
      
      If sAction = "Display" Then
      
         .Display
         
      ElseIf sAction = "Send" Then
      
         .Send
         
      End If
      
   End With
On Error GoTo 0

'Clear Set Variables
Set olApp = Nothing
Set olMsg = Nothing


End Sub