VBA code to run on specified time

[vwsteven]

Hello

I'm relatively new to VBA and I'm trying to get a code to run daily but nothing seems to be working. My code is:


[VBA]Sub ObservationSheet()

Application.OnTime "17:00:00", "ObservationSheet"

ActiveSheet.Calculate

Dim OutApp As Object
Dim OutMail As Object
Dim i As Integer
Dim z, a As String
Dim Subject As String


Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup

For i = 3 To 500

If Sheet1.Cells(i, 1) = Sheet1.Cells(i, 8) And Sheet1.Cells(i, 12) = "No" Then

w = Sheet1.Cells(i, 11)
z = Sheet1.Cells(i, 10)
a = Sheet1.Cells(i, 13)
b = Sheet1.Cells(i, 14)
c = Sheet1.Cells(i, 15)
d = Sheet1.Cells(i, 16)

strbody = "test"

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail
.To = w
.CC = a
.BCC = b
.Subject = hello
.Body = strbody
.send

End With
On Error GoTo 0

Set OutMail = Nothing

Sheet1.Cells(i, 12) = "Yes"

Else

End If

Next i


cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True

End Sub
[/VBA]

Any help is appreciated.
Thankx

[mdmackillop]

Split your code into two subs, call the Time sub from the Function sub

[VBA]Sub Test()
Application.OnTime "17:00:00", "ObservationSheet"
End Sub


Sub ObservationSheet()
MsgBox "Test Text"
Call Test
End Sub
[/VBA]

[vwsteven]

thank you. I write the
Call Test after my macro right? just before End Sub?


will this run the macro every day?

thankx again

[mdmackillop]

Yes and I believe so. I'll know tomorrow!