Automated Emails

Okay total Noob here so please bare with me.
I used this video to write what I’ve got.
http://www.youtube.com/watch?v=Z-hsCUl1qW8

I’m trying to send email notifications, only once, if J14 = <7.0, then stop the emails.
And then wait for J14 = >6.9, send another email, stop emails and wait for J14 to = <7.0 again.
Right now it just keeps emailing every 20 minutes when J14 is updated by a web query.

Any help would be greatly appreciated.
Thanks

Here’s what I’ve got.
==============================================================
Private Sub Worksheet_Calculate()

Control1 = Range("I11").Value

Trigger = Range("J11").Value

HighestRecordedPeriod = Range("J14").Select
If HighestRecordedPeriod < Trigger And Control1 = "MAIL" Then

Dim ADDRESS As String, SUBJECT As String
Dim BODY As String, HYPER As String

ADDRESS = "[email protected]"
SUBJECT = "This is an Alert - The period has reached " & Range("J14") & " seconds"
BODY = "test"

HYPER = "mailto:" & ADDRESS & "?Subject=" & SUBJECT

ActiveWorkbook.FollowHyperlink (HYPER)
Application.Wait (Now + TimeValue("0:00:15"))
Application.SendKeys "%S"

Range("I11").Select
ActiveCell.FormulaR1C1 = "MAIL"

End If
End Sub

you need some variable to keep track if you want the high value or low value for the next email to be sent, try using a static variable like

Code:

---

static lownext as boolean

if (HighestRecordedPeriod < 7 and not lownext) or (HighestRecordedPeriod > 6.9 and lownext) then
  lownext = not lownext
  ' code to send mail
end if

---