Sending an automatic email from Excel when a cell reaches a value that will increase

I have an excel worksheet which I have already got a change event and module set up to automatically send and email when a cell reaches a certain value in one column.

I need to set up a new change event and I assume module for a different column to generate an automatic email when the value reaches a certain figure. My problem is this figure will always be increasing, and i need the email to be sent once the column reaches every +300 from the previous email sent. ie if the email is sent when the value reaches 1904, i need it to then send when it reaches 2204, then 2504 and so on.

I've tried changing some codes from examples but it doesn't seem to be working. I don't really know how to write the codes so assistance would be appreciated:

Private Sub Worksheet_Change1(ByVal Target As Range)
If Target.Column = 204 Then
    If Target.Value Mod 300 = 0 Then
        Call EngineHoursW01T
    End If
End If
End Sub

Module -

Sub EngineHoursW01T()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi" & vbNewLine & vbNewLine & _
          "Please service Pump" & vbNewLine & _
              "" & vbNewLine & _
              "Kind Regards" & vbNewLine & _
              "xxxx"

    On Error Resume Next
    With OutMail
        .To = "email address"
        .CC = ""
        .BCC = ""
        .Subject = "Service of Pump"
        .Body = strbody
        .Attachments.Add ("")
        .Display
    End With
    On Error GoTo 0

   Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Answers


This answer is a simplified approach. The problem we want to solve is: Send an email when the value of "X" has grown by 300 or more since the last email was sent.

Question 1: How do we know what value of "X" was present when the last email was sent?

Answer 1: We need to store the values of "X" each time an email is sent and save these values for future reference. In Excel the natural way to save a value that can be retrieved later is to store it in a worksheet. Let's call it LogSheet.

Question 2: Now I am checking new values and I have "Z". Should I send an email?

Answer 2: We consult LogSheet and find the last value that triggered an email. Is Z > 300 + X? If so, we send a new email and append "Z" to LogSheet. If not, we ignore it.

Question 3: How can I track 8 entity results independently?

Answer 3: Extend the logic above.

At each step you will need to write code. I suggest you tackle these in order, and create new questions if you get stuck.


Need Your Help

Could someone assist me with that save form?

php forms drupal drupal-7 drupal-modules

here follows the admin.inc file for my user's form.In this example i have return system_settings_form($pppp_form); what i really want is to make on my own a save button and save all that values whe...