How to create a macro to incorporate the following code

[Jack]

Hi,
I have some code that currently is being executed by click of a button.
However, I want to automate this process and also schedule this process. For
this I need to make a macro to run the code. I am not much familiar with
Access macros. I need help to formulate a macro that will be called from a
scheduler. I also need to incorporate the macro in a batch file. I would
appreciate any help to formulate the macro. Thanks.
CODE:
Dim Db As DAO.Database, Rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set Db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
Set Rs = Db.OpenRecordset("qrySendEmailAdvice")
While Not Rs.EOF

'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs![EmailAddress], , , "Case Closing
Reminder", _
"Dear " & Rs![Employee] & vbCrLf & vbCrLf & "Your case corresponding to " &
Rs![CaseID] & _
vbCrLf & vbCrLf & "is five days overdue. You need to work on this to close it"

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE tblEmployeeCase SET EmailSent=-1 where CaseID=" & Rs![CaseID]
Db.Execute sSQL

'Cycle on to the next record
Rs.MoveNext
Wend
Rs.Close
Set Rs = Nothing
'Control recordset now closed

'Reset environment
DoCmd.SetWarnings True

 

[Piet Linden]

Hi,
I have some code that currently is being executed by click of a button.
However, I want to automate this process and also schedule this process. For
this I need to make a macro to run the code. I am not much familiar with
Access macros. I need help to formulate a macro that will be called from a
scheduler. I also need to incorporate the macro in a batch file. I would
appreciate any help to formulate the macro. Thanks.
CODE:
Dim Db As DAO.Database, Rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set Db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
Set Rs = Db.OpenRecordset("qrySendEmailAdvice")
While Not Rs.EOF

'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs![EmailAddress], , , "Case Closing
Reminder", _
"Dear " & Rs![Employee] & vbCrLf & vbCrLf & "Your case corresponding to "&
Rs![CaseID] & _
vbCrLf & vbCrLf & "is five days overdue. You need to work on this to close it"

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE tblEmployeeCase SET EmailSent=-1 where CaseID=" & Rs![CaseID]
Db.Execute sSQL

'Cycle on to the next record
Rs.MoveNext
Wend
Rs.Close
Set Rs = Nothing
'Control recordset now closed

'Reset environment
DoCmd.SetWarnings True

save all this as a function instead of a subroutine, and then you can
call it from a macro. Check out the scheduler utility on Access
web... www.mvps.org
Use RunCode and then specify the Function you want to run. And make
sure the function is declared Public.