Best way to automate clicking a form button

[bharlow]

I have built a form to send out multiple emails by clicking a button. What is the best way to automate/schedule clicking he button once a week. I was building a macro to OpenForm, GoTo Control an SendKeys "{Enter}" but it keeps getting an error message at the end. It does not get that error message if I do this manually.

Any suggestions would be greatly appreciated.

 

[CameronM]

Does the form actually contain any information required to send the emails or is it only used to Click the button?

If you only click the button to send emails, I would place the same code in a Function that can be run each time the database opens. You store the date each time the function runs and only run it when the date is older than a week.

For example, store the date in a table called ProgSettings and have code along the lines of;

Public Function SendEmails()
    Dim strSQL As String
    Dim rst As Recordset
 
    'create a recordset to retrieve the last date emails were sent
    strSQL = "SELECT LastEmailDate FROM ProgSettings"
 
    Set rst = CurrentDb.OpenRecordset(strSQL)
 
    If rst("LastEmailDate") <= DateAdd("d", -7, Date) Then
        'insert the code from your command button Click event
 
        'update the last date sent to today
        rst.Edit
        rst("LastEmailDate") = Date
        rst.Update
 
        'close your recordset
        rst.Close
    End If
 
End Function

The SendEmails function could be called after users click on a login screen, or can be called via the a RunCode Action in the Autoexec macro (if you use one).