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
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
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.