This routine just keeps on going! Why?

Originally Posted by LFCFan

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

I found a comment (quote) "Note how we pass the time of 15 minutes to the Public Variable dTime This is so we can have the OnTime Method cancelled in the Workbook_BeforeClose Event by setting the optional Schedule argument set to False. If we didn't pass the time to a variable Excel would not know which OnTime Method to cancel as Now + TimeValue("00:15:00")is NOT static, but is when passed to a variable. If we didn't set the optional Schedule argument set to False the Workbook would automatically open every 15 minutes after closing it and run MyMacro" (end) which could be useful to you. It was from http://www.ozgrid.com/Excel/run-macro-on-time.htm

I see no purpose in te red code, unless you are aware of something else.

for "If WeekDay(Now) = 7 Then
saturday
ElseIf WeekDay(Now) = 1 Then
sunday
ElseIf WeekDay(Now) <> 7 And WeekDay(Now) <> 1 Then"

you have already removed 7 and 1, so why the need to re-test. same in SendIt, you went to sat on day 7, sunday on day 1, and SendIt for the other days, so the point of testing 7 & 1 in SendIt seems wasted.

Does this help you?

---