Send auto email based on two factors, date falls within 90days & cell value B6

[hsolanki]

Good Morning all

i was wondering if anyone could help me. i have got a code that works fine based on the "B6" value however i now want to add an also if the value changes and it is within the 90 days (date value its in range E column.

below it is the code whereby i get an error run time error '13'

Dim SentMsg As String

Dim r As Range, cell As Range
Set r = Range("E3:E10000")

On Error GoTo errHandler:
Sheet3.Unprotect Password:="Bhaji2020"

NotSentMsg = "Not Sent"
SentMsg = "Sent"
    For Each cell In r
With Me.Range("B6")
    If Not IsNumeric(.Value) Then
        MyMsg = "Not numeric"
    Else

         If r.Value <= (Date - 90) And .Value > 15 And .Value < 30 Or .Value > 63 And .Value < 73 Or .Value > 124 Then
            MyMsg = SentMsg
            If .Offset(0, 1).Value = NotSentMsg Then
                Call Mail_Outlook_With_Signature_Html_1
                MsgBox "Email has been sent", vbInformation
            End If
        Else
            MyMsg = NotSentMsg
        End If
    End If
    Application.EnableEvents = False
    .Offset(0, 1).Value = MyMsg
    Application.EnableEvents = True
End With

Application.EnableEvents = True
Sheet3.Protect Password:="Bhaji2020", DrawingObjects:=False, Contents:=True, Scenarios:= _
        True

On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred  " & vbCrLf & _
       "The error number is:  " & Err.Number & vbCrLf & _
       Err.Description & vbCrLf & "Please Contact Admini"
Next
End Sub

 

[Candyman8019]

Hi Candyman thanks for pulling the codes together for me however what it is doing is that once the date is within the past 90 days and the set value is reached in cell number K2 it will send multi emails for all those cells the date was not entered, and it is also bypassing the mgs Not Sent / Sent cell in L2.

i only require sending 1 email if the date is within the past 90 days from the last email sent against Capital S in column C and the set value is reached in cell number K2 and then only to send email based on this

If there is no date that the email was sent, that means they have never received an email and so one will be sent out and the date updated. What is the purpose of the value in Cell L2?

As long as there is a date in Column G, it won't send an email unless the date is older than 90 days.

 

[hsolanki]

Morning Candyman but when there is no date in column G, it is continuingly sending an email until the last row in Column G. purpose of the value in a cell in L2 is to it will detect if email require sending as well as the set value in K2 is met and if the dates fall within 3 months

 

[Candyman8019]

If you want L2 to be taken into account, uncomment this line

 ' If Sheet6.Range("L2").Value = "Send" Then

and a few rows down, also this line

' END IF

I still don't understand the purpose of that...if you don't want emails sent then just don't run the SendMessage script? I'm sure I'm missing something, but that's okay as long as it does what you want.