OnTime Method VBA Code

[smartguy]

Hello all,

I have one Macro. I need to run macro automatically on daily 8.30 P.m

Please help....

 

[VoG]

Here's an example http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[smartguy]

Hello Sir,

I am not sure.....

Where can i add the code?

Sub Mailer()

    Dim OutApp As Object, OutMail As Object
    Dim rngCell As Range
               
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    
    For Each rngCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If DateSerial(Year(Date), Month(rngCell.Value), Day(rngCell.Value)) = Date Then
            With OutMail
                .To = rngCell.Offset(0, 1).Value
                .CC = ""
                .BCC = ""
                .Subject = "Happy Birthday"
                .Body = "Blah Blah Blah"
                .Display
            End With
                            
            Set OutMail = Nothing
            Set OutApp = Nothing
                               
            With Application
                waitTime = TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
                .Wait waitTime
                .SendKeys "%s~~~"
            End With
        End If
    Next rngCell
                
End Sub

 

[VoG]

Adjust that to

Public dTime As Date

Sub Mailer()

    Dim OutApp As Object, OutMail As Object
    Dim rngCell As Range
dTime = Now + 1
Application.OnTime dTime, "Mailer"
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    
    For Each rngCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If DateSerial(Year(Date), Month(rngCell.Value), Day(rngCell.Value)) = Date Then
            With OutMail
                .To = rngCell.Offset(0, 1).Value
                .CC = ""
                .BCC = ""
                .Subject = "Happy Birthday"
                .Body = "Blah Blah Blah"
                .Display
            End With
                            
            Set OutMail = Nothing
            Set OutApp = Nothing
                               
            With Application
                waitTime = TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
                .Wait waitTime
                .SendKeys "%s~~~"
            End With
        End If
    Next rngCell
                
End Sub

Then in the ThisWorkbook module

Private Sub Workbook_Open()

  Application.OnTime TimeValue("20:30:00"), "Mailer"

End Sub

Save, close and re-open the workbook.

 

[smartguy]

Thanks.

I tried I got a run time error 91.

Object variable or with Block Variable not set..

.To = rngCell.Offset(0, 1).Value

 

[VoG]

The code compiles OK for me. Did it work OK when you ran it manually (not using OnTime).

 

[smartguy]

Hello sir,

No its not working...

 

[smartguy]

Hello All,

Please help...

 

[smartguy]

Hello sir,

No its not working...

 

[VoG]

Does the code work correctly when you run the original code that you posted? If not then post back to the thread where you were given that code.