This routine just keeps on going! Why?

Ok,

I'm trying to schedule a mail being sent out every day, with an attachment. However I don't want it to run on a weekend (ie weekday is 1 or 7). The onTime method does work to the extent that it counts down and runs the routine, however it just keeps running it thereafter and I end up sending the same mail over and over. I have no idea why. Well, I think its to do with the schedule property of the onTime method, its set to true by default. But when I try and set it to False, I get a 1004 error. At my wits end here, can anyone help? Here's the code:

Sub evalDay()

sTimer = Now + TimeValue("23:59:59")
mSent = False
If WeekDay(Now) = 7 Then
saturday
ElseIf WeekDay(Now) = 1 Then
sunday
ElseIf WeekDay(Now) <> 7 And WeekDay(Now) <> 1 Then
Application.OnTime sTimer, "sendMenu.xls!Sheet1.sendIt"
End If
End Sub
-------------------------------------------------------------------------

Sub saturday()
Application.OnTime TimeValue("23:59:58"), "sendMenu.xls!sheet1.sunday"
Loop
End Sub
-------------------------------------------------------------------------
Sub sunday()
Do While Time <> "23:59:59"

If Time = "23:59:59" Then Application.OnTime Now + TimeValue("8:59:59"), "sendmenu.xls!sheet1.sendIt"
Loop
End Sub
-------------------------------------------------------------------------
Sub sendIt()

If WeekDay(Now) = 7 Then
saturday
ElseIf WeekDay(Now) = 1 Then
sunday
Else
Set oApp = CreateObject("outlook.application")
Set oItem = oApp.createItem(olMailItem)

oItem.attachments.Add ("\\FP06\common$\Facilities\Eurest - Restaurant\Menus\Menu.doc")
oItem.To = "Clevedon Support Centre"
oItem.Body = "Hi," & Chr(13) & Chr(13) & "Please find attached this weeks menu. You can access the ordering system from within the attached menu." & Chr(13) & Chr(13) & "Regards" & Chr(13) & "Joe Foster"
oItem.Send

Set oApp = Nothing

End If
End Sub
-------------------------------------------------------------------------

I can't even step through the code to check for the error because the timers get all screwy and I get messages about not being able to execute code in break mode. Irritating to say the least!!!!!

Help much appreciated.
Joe

Hi Bryan,

Thanks for getting back to me so quickly on this one. To satisfy your curiosity, the red items of code you pointed out are for the remote possibility of anyone opening this sheet and setting off only the sendIt module (I code paranoid Im afraid). The page you pointed out was kind of useful, but there still seems to be a problem with cancelling ontime events.

This line of code.....

Application.OnTime sTimer, "sendIt", , False

...threw an error as soon as I tried to use it (Method ontime of object application failed). I just have a nasty feeling that if the Ontime isn't cancelled it will schedule itself to run again and again. Its only the saturday and sunday routines I'm concerned about as the monday-friday routine is simple. Any ideas as to why this error is being thrown? This is how the code looks now:

Sub evalDay()
sTimer = TimeValue("09:00:00")
If WeekDay(Now) = 7 Then
saturday
ElseIf WeekDay(Now) = 1 Then
sunday
Else
Application.OnTime sTimer, "sendIt"
End If
End Sub
------------------------------------------------------------------------

Sub saturday()

Application.OnTime sTimer, "sendIt", , False
'****Cancel the daily routine - Error
Application.OnTime Now + TimeValue("23:59:59"), "sunday"
sunday

End Sub
-------------------------------------------------------------------------

Sub sunday()

Application.OnTime sTimer, "sendIt", , False
'*****Cancel the daily routine - Error
If Time = "23:59:59" Then Application.OnTime Now + TimeValue("8:59:59"), "sendIt"


End Sub
-------------------------------------------------------------------------



Sub sendIt()

Set oApp = CreateObject("outlook.application")
Set oItem = oApp.createItem(olMailItem)

oItem.attachments.Add ("attachment_Path")
oItem.To = "[email protected]"
oItem.Body = "Message content"
oItem.Send

Set oApp = Nothing

End Sub
------------------------------------------------------------------------

Much appreciated
Joe