Periodical check of cell value to email message

[Quatron]

Hello, I use the following code to send email when the cell value reaches my criteria:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If (Range("L10").Value > Range("M4").Value AND Range("M10").Value = "YES") OR (Range("L12").Value > Range("M4") AND Range("M12").Value = "YES") Then
Call Mail_Workbook
End If

End Sub



Sub Mail_Workbook()
Application.ScreenUpdating = False

Dim OutApp As Object
Dim OutMail As Object

'Create Initial variables
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Email
On Error Resume Next
With OutMail
.to = "mail@mail.com"
.CC = ""
.BCC = ""
.Subject = "Alert"
.Body = "Good value"
'.Attachments.Add (FilePath) 'Optional if you have an attachment
.Display
.Send 'Optional to automate sending of email.
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

Application.ScreenUpdating = True
End Sub



However it needs to check the cell value every 5 minutes or so as the data in the cell is changing as it is pulled from the internet. How to change the code above to add the 5 minute check periodicity?

Any help is appreciated, thanks.

 

[Starnub]

This should do it. It will run the code in "check" every 10 minutes. Just run the "timer" sub when the workbook is opened.

Sub check()
'Your code here
Call timer
End Sub

Sub timer()
Application.OnTime Now + TimeValue("00:10:00"), "check"
End Sub

 

[Quatron]

Ended up using this found elsewhere on the internet:

In Workbook events:
<code>Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub</code> In a module:
<code>Sub RunEveryTwoMinutes()
//Add code here for whatever you want to happen
Application.OnTime Now + TimeValue("00:02:00"), "RunEveryTwoMinutes"
End Sub

The only issue now is that if the Outlook is not open the email is stuck in the Outbox. When Outlook is open it sends immediately. Any way to fix this?
</code>