SendObject on pre-set future date. . .timer? 1

[dpav29]

I have read many helpful (and interesting) posts re: sendobject. Can't seem to find anything like this:

I want to send an email triggered by a date in a form, but I want the email to be sent 30 days in advance. The database is for contract management, and I need certain people to be notified in advance that a contract is about to expire. Can access do this? If so, does the database have to be open for the mail to send when the date arrives?

Thanks for any help on this!

 

[TheAceMan1]

How are ya dpav29 . . . . .

Yes Access can! The easiest way would be to run a routine form the [blue]AutoExec Macro[/blue] ([purple]runs everytime you open the database[/purple]). The routine would query the Current Date and via a [blue]Flag[/blue] (another field) determine if e-mail should be sent. The [blue]Flag[/blue] is a boolean field (true/false) which tells you if e-mail was sent on the [blue]specified date[/blue]. Change the specified date and thru code you [blue]reset the flag to false[/blue].

The Date in the form and the Flag have to be fields in a table [blue]so their status can be maintained[/blue] when the database is closed.

Your basic routine would be something like the following:

[blue]Public Sub SendMail()
   Dim db As DAO.Database, rst As DAO.Recordset
   
   setdb = CurrentDb()
   Set rst = db.OpenRecordset("[purple][b]YourTableName[/b][/purple]", dbOpenDynaset)
   
   Do
      If IsNull(rst!Date) Then
         [green]'Code if no date[/green]
      Else
         If Now() >= (rst!Date - 30) And Not rst!Flag Then
            'Code for sending e-mail
            rst.Edit
            rst!Date = [blue]Null[/blue]
            rst!Flag = [blue]False[/blue]
            rst.Update
         End If
      End If
      
      rst.MoveNext
   Loop Until rst.EOF
   
   Set rst = Nothing
   Set db = Nothing
         
End Sub[/blue]

The code resets the Flag and nulls the date as a visual indicator that mail for the spcific client was set. This may or may not appeal to you. In any case, the scheme is easily modified . . . .

See Ya! . . . . . .

 

[dpav29]

I really appreciate that and will explore it further. The only thing I'm worried about is the fact that the trigger is the opening of the database. It won't be used very often and contracts expire all the time. . . one could be easily missed if the database wasn't opened for several weeks.

Any thoughts on that?

 

[TheAceMan1]

dpav29 . . . . .

Yes, thats the real problem.

Since you only need one shot per day, might I suggest using [blue]Windows Task Scheduler![/blue]. You could set it to open the DB, let it remain open long enough to complete sending mail, then close it . . . . .

See Ya! . . . . . .

 

[dpav29]

Great idea. . .that should work. Since it only has to be open on one user's machine, it won't be intrusive. Thanks a lot!