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
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