Code required to make my program work please

[JoeyBren]

Hello,
When I manually insert a "1" into cell A2, the code written in VBA sends an email to me reminding me to "Service lawnmower."
If I try to insert the "1" with a date activated formula, it doesnt work
I have dates set up, one that automatically updates every 20 sec (dynamic) comparing to a rigid (static) date.
When dynamic is higher than static it returns a "1" to cell A2, but nothing happens.
Could you please insert a section of code into my VBA code to make this work, I have tried everything, I am very new to programming.

In worksheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OLook As Object
Dim Mitem As Object
Dim SendAnEmail As Boolean
If Range("A2") <> Range("B2") Then
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
Mitem.to = "your email goes here"
Mitem.Subject = "Service Alert!"
Mitem.body = "" & Sheet1.Range("F3").Value & " "
Mitem.Send
End If
Set OLook = Nothing
Set Mitem = Nothing
End Sub

To make the the date and time update automatically:

In module 1

Sub Update_Save()
Application.OnTime Now + TimeValue("00:00:20"), "Update_Save"
Application.CalculateFull
ActiveWorkbook.Save

End Sub

Cheers,

Desparate Joeybren

 

[VoG]

Try this instead

Private Sub Worksheet_Calculate()
Dim OLook As Object
Dim Mitem As Object
Dim SendAnEmail As Boolean
If Range("A2") <> Range("B2") Then
    Set OLook = CreateObject("Outlook.Application")
    Set Mitem = OLook.createitem(0)
    Mitem.to = "your email goes here"
    Mitem.Subject = "Service Alert!"
    Mitem.body = "" & Sheet1.Range("F3").Value & " "
    Mitem.Send
End If
Set OLook = Nothing
Set Mitem = Nothing
End Sub

 

[JoeyBren]

Vog,

You are a complete legend.
I have spent hours and hours on this and you had a reply within a minute of me sending the thread. I cant thankyou enough.
eternally greatful

Joeybren