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]

the code in the workbook you just attached does not match what I sent earlier. Try doing a copy/paste to ensure it is all updated.

 

[hsolanki]

Hi Candyman thanks.

i have tried all your codes and it keeps sending an email out.

i am going crazy, i have gone blind i would appreciate if you could see on the revised V1 sample where i am going wrong as i cant seem to know now

 

[Candyman8019]

I'm not sure what to tell you at this point. When I run it, It definitely doesn't send email beyond the list in the log details tab.

It would probably be helpful for you to document in detail what you want each of the formulas to do. I suspect your formula in column J is incorrect....and maybe that's why it doesn't do what you want it to do?

Staff Sample File.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Audit Trail / Track History
2	Sheet & Cell References	Old Value	New Value	User	Date & Time	Cell Number	5	0	5	125
3	2022 - L16:W27			HSolanki	08-08-2022	Sent
4	2022 - H15		S	HSolanki	09-09-2021	Sent
5	2022 - O25		S	HSolanki	10-10-2022	Not Sent
6	2022 - V23		S	HSolanki	04-11-2021	Sent
7	2022 - H15	S	S	joe	04-11-2022	Not Sent
8	2022 - AA29		S	joe	07-11-2022	Not Sent
9
10
11
12
13
LogDetails

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(LEN(C3:C99983)-LEN(SUBSTITUTE(C3:C99983,"S","")))
H2	H2	=SUMPRODUCT(LEN(C3:C99983)-LEN(SUBSTITUTE(C3:C99983,"s","")))
I2	I2	=SUM(G2:H2)
J2	J2	=I2*G2*G2

 

[hsolanki]

Morning Candyman

thanks, Cell number J2 is correct that works according to our company policy, and the numbers are correct. basically, it will add all Capital S & small S and then the total number of S is multibuy the small S twice which will give you a number that is recorded on cell number J2.

what I would like to do then is that when cell J2 is between the two set values it needs to send an email based on the between the two-value set and if the dates in 3 months comparing somehow when the last email was sent now I am not too sure if this is possible to achieve

 

[Candyman8019]

If I understand correctly you’re close. And I’m sure it is achievable. Rather than have sent/ not sent in column F you should have the date the email was sent. Then when looking at your date and number evaluations in VBA use the sent date rather than the audit log change date.

 

[hsolanki]

Hi Candyman thanks, would you be able to help me with that how to put that together

 

[Candyman8019]

I can but I’m out of the office today and don’t have access to a computer. I’ll touch base tomorrow

 

[hsolanki]

Hi Candyman no problem whenever you get a chance, i do appreciate for all your help

 

[Candyman8019]

Give this a try. I left a bunch of your code in there and just commented out what isn't needed. Once you're good with the functionality you can clean it up as desired.

Staff Sample File.xlsm

Microsoft Excel Workbook

1drv.ms

 

[hsolanki]

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