Application On Time Help

[kv2singh]

Hey Guys,

I am pretty new to VBA and have never really had any training with coding for it. I am setting up an automated report but am having trouble getting the application on time event to run more than 24 hours. The Reportupdate Sub updates quite a few cells based on the current, that why it is run so often. I am sending the report out at 6 am everyday and the report covers a 24 hour interval from midnight to midnight, hence the last report update time before 6 am being 0:00:00.

Here is my Code:

ThisWorkbook

Sub Workbook_Open()


'Automatically runs other macros at listed times, "reportupdate" refreshes report for listed times, "sendremindermail" sends out report
Application.OnTime TimeValue("07:00:00"), "Reportupdate"
Application.OnTime TimeValue("08:00:00"), "Reportupdate"
Application.OnTime TimeValue("09:00:00"), "Reportupdate"
Application.OnTime TimeValue("10:00:00"), "Reportupdate"
Application.OnTime TimeValue("11:00:00"), "Reportupdate"
Application.OnTime TimeValue("12:00:00"), "Reportupdate"
Application.OnTime TimeValue("13:00:00"), "Reportupdate"
Application.OnTime TimeValue("14:30:00"), "Reportupdate"
Application.OnTime TimeValue("15:00:00"), "Reportupdate"
Application.OnTime TimeValue("16:00:00"), "Reportupdate"
Application.OnTime TimeValue("17:00:00"), "Reportupdate"
Application.OnTime TimeValue("18:00:00"), "Reportupdate"
Application.OnTime TimeValue("19:00:00"), "Reportupdate"
Application.OnTime TimeValue("20:00:00"), "Reportupdate"
Application.OnTime TimeValue("21:00:00"), "Reportupdate"
Application.OnTime TimeValue("22:00:00"), "Reportupdate"
Application.OnTime TimeValue("23:00:00"), "Reportupdate"
Application.OnTime TimeValue("23:10:00"), "Reportupdate"
Application.OnTime TimeValue("23:20:00"), "Reportupdate"
Application.OnTime TimeValue("23:30:00"), "Reportupdate"
Application.OnTime TimeValue("23:40:00"), "Reportupdate"
Application.OnTime TimeValue("23:45:00"), "Reportupdate"
Application.OnTime TimeValue("23:50:00"), "Reportupdate"
Application.OnTime TimeValue("00:00:00"), "Reportupdate"

Application.OnTime TimeValue("06:00:00"), "sendReminderMail"

If Worksheets("Report 2").Range("N1").Value = Date Then

Application.OnTime TimeValue("23:55:00"), "Reportendofmonth"
Application.OnTime TimeValue("23:58:00"), "Correctionzero"
End If


End Sub

-----------------------------------------------------------------------------------------------------------------------------------
Module 1
Sub Reportupdate()


Worksheets("Report 2").Range("C5").Value = Now()
ActiveWorkbook.Save


End Sub

------------------------------------------------------------------------------------------------------------------------------------
Module 2
Sub sendReminderMail()


'change c;\users for local comp
ChDir "H:\PIC_SHAR\Production Report"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"H:\PIC_SHAR\Production Report\Daily Plant Technical Report " & Format(Date - 1, "mmmm dd yyyy") & ".PDF"




Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object


Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments


With OutLookMailItem
.To = ""
'.To = ""
'.CC = ""
.Subject = "Daily Technical Report " & Format(Date - 1, "mmmm dd yyyy")
.Body = "Automated PDF Daily Technical Report"
myAttachments.Add "H:\PIC_SHAR\Production Report\Daily Plant Technical Report " & Format(Date - 1, "mmmm dd yyyy") & ".PDF"
.send
End With


Set OutLookMailItem = Nothing
Set OutLookApp = Nothing




End Sub



Help on getting the application on time timers to work past 24 hours would be appreciated.

 

[Worf]

Cross posted here:

Automating a Report with Application on Time