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

 

[hsolanki]

Hi

i would really appreciate if anyone could help me

 

[Candyman8019]

What line are you getting the error on?

 

[hsolanki]

Hi Candyman8019, thanks. getting error on

 If r.Value <= (Date - 90) And .Value > 15 And .Value < 30 Or .Value > 63 And .Value < 73 Or .Value > 124 Then

 

[Candyman8019]

It looks like your column E range contains dates and B6 contains a number. I moved a few things around in your script, but I haven't actually tested it...but I think it should get you on the right track.

Private Sub cleansheet()
Dim lastrow As Long
Dim i As Long
Dim testvalue As Integer

lastrow = Cells(Rows.Count, 5).End(xlUp).Row + 1

For i = 7 To lastrow
    If Cells(i, 6) = "2" Then Cells.Rows(i).Delete
Next
End Sub
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"

With Me.Range("B6")
    If Not IsNumeric(.Value) Then
        MyMsg = "Not numeric"
    Else
testvalue = Me.Range("B6").Value
    For Each cell In r
         If r.Value <= (Date - 90) And testvalue.Value > 15 And testvalue.Value < 30 Or testvalue.Value > 63 And testvalue.Value < 73 Or testvalue.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

 

[hsolanki]

Morning Candy

Many thanks for drafting a code for me however i still get an error on the same line. just to give you an idea of my workbook below is.

when on cell number "J2" changes the number between 15-30, 64-70 & 124 above, it will also need to look for msg sent or not sent in cell number "K2& and if the date is falls within the last 3 months from the last 3 months

 

[Candyman8019]

try changing:

If r.Value <= (Date - 90) And testvalue.Value > 15 And testvalue.Value < 30 Or testvalue.Value > 63 And testvalue.Value < 73 Or testvalue.Value > 124 Then

to

If r.Value <= (Date - 90) And testvalue > 15 And testvalue < 30 Or testvalue > 63 And testvalue < 73 Or testvalue > 124 Then

 

[hsolanki]

Hi Candy, sorry still got a same error

 

[Candyman8019]

Okay, I tried this out on my sheet and the following works.

Private Sub cleansheet()
Dim lastrow As Long
Dim i As Long
Dim testvalue As Integer

lastrow = Cells(Rows.Count, 5).End(xlUp).Row + 1

For i = 7 To lastrow
    If Cells(i, 6) = "2" Then Cells.Rows(i).Delete
Next

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"

    If Not IsNumeric(Me.Range("J27").Value) Then
        MyMsg = "Not numeric"
    Else
testvalue = Me.Range("J27").Value
    For Each cell In r
         If cell.Value <= (Date - 90) And testvalue > 15 And testvalue < 30 Or testvalue > 63 And testvalue < 73 Or testvalue > 124 Then
            MyMsg = SentMsg
            If cell.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
   
    Application.EnableEvents = False
    cell.Offset(0, 1).Value = MyMsg
    Application.EnableEvents = True

Next
End If
Sheet3.Protect Password:="Bhaji2020", DrawingObjects:=False, Contents:=True, Scenarios:= _
        True
On Error GoTo 0
Application.EnableEvents = True

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

End Sub

 

[hsolanki]

Hi Candy, Thank you. sorry i Can't get it going with the new code and was wondering if you could help. basically, what i am trying to achieve is that on sheet 2 any information is typed is recorded on the logsheet tab and all the capital S is then calculated on the cell number "J2" and if the total is reached between 16-30, 64-70 & anything above 124 and if the last S entered falls within the past 3 months date which is in column E then it will ask to send an automated email. i have attached my sample book just to give you an overview for a better idea

Staff Sample File.xlsm

2022 MSC,Staff Candy File O/T,BS Late,0,SAMPLE - NAME Sick,27,Not Sent,ERN Number:,11223454 Absnt,0,Date of joining,20/09/21 Holiday 3,0,3 Recovery = 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 JANUARY FEBRUARY MARCH,S APRIL:235.20 MAY JUNE JULY AUGUST,S SEPTEMBER OCTOBER NOVEMBER DECEMB...

docs.google.com

i would really appricate if you would help me with my code