PDA

View Full Version : VBA code to run on specified time



vwsteven
02-14-2011, 01:01 PM
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:


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


Any help is appreciated.
Thankx

mdmackillop
02-14-2011, 01:10 PM
Split your code into two subs, call the Time sub from the Function sub

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


Sub ObservationSheet()
MsgBox "Test Text"
Call Test
End Sub

vwsteven
02-14-2011, 01:41 PM
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
02-14-2011, 02:56 PM
Yes and I believe so. I'll know tomorrow!