Postponing a macro for 10 minutes

  • Thread starter Thread starter Tornados
  • Start date Start date
T

Tornados

Hello,

I have this setup:

When a certain cell changes value folllowing a calculation an automatic
email is sent. This uses Worksheet_calculation in which i call a
sendmail macro. This works fine.

However sometimes the cell value changes too much and too many emails
are being send. Therefore i would like to make sure that when a macro
event has been triggered, it will not send another email in for example
10 minutes..

Anyone? Thanks in advance. Ivo

ps. I already tried application.wait. however in this case the macro
continues to run and excel will sort of freeze for those 10 minutes,
which is not what i can afford :)
 
Store the time at which the e-mail is sent.
When the value changes, only send another e-mail if it is at least 10
minutes since the last one was sent, otherwise do nothing.
 
One way:

Private Sub Worksheet_Calculate()
Static dLastSent As Double
If dLastSent = 0 Then dLastSent = Now - TimeSerial(0, 10, 0)
If CDbl(Now) >= (dLastSent + TimeSerial(0, 10, 0)) Then
'<your code here>
dLastSent = Now
End If
End Sub
 
Hmm..

This code somehow doesn't work.. . It seems rather logical though..

Could there maybe be something wrong with the Double format?

It doesn't give an error yet the macro just does not get exucuted
anymore..

Regards, Ivo
 
Works for me with a test book. The use of Double should not be a
problem - you could try switching to variants, but I've had more
trouble with them than with doubles.

Make sure the

dLastSent = Now

is inside the

If CDbl(Now) >= (dLastSent + TimeSerial(0, 10, 0)) Then
<your code
dLastSent = Now
End If

structure. If it were to come after the End If, dLastSent will be
updated every calculation instead of every time an email is sent.
 
Back
Top