-1

I'm currently creating a database for my work that consists of basic information on the company car fleet. I've created the database and front sheet which consists of variables such as car,c ar color, MOT Date, Tax Date, etc.

I need to somehow have Access detect when a company car's MOT date is due and email someone to notify them that the date is upcoming by comparing today's date with the date entered into the MOT date field.

Something like, if today's date is a week prior to the MOT date, send an email to whoever to notify them that this is due.

I need this to happen automatically, I plan to open and refresh the sheet daily so it doesn't need to be particularly fancy and do it without Access being open, I just need it to perform this task without trawling through pages and pages of data.

Paul D Crossley
  • 1
  • 1

1 Answers1

1

You can do this on launch. Just write a query that picks the records you want. Then loop through the query and fire off an email to each person, then make sure you have a field called "EmailSent" that you'll update to True so you don't send them an email the following day (assuming you just want to email them once).

You'll probably just want some VBA along these lines:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset("SELECT * FROM MyQueryName")

Do while rec.EOF = False
  'Loop through each record, send them an email
  'Add code to send email here
rec.MoveNext

'Now update the table so these guys don't get emailed again
dim MySQL as String
MySQL = "UPDATE MyQueryName SET EmailSent = 'True'"
DoCmd.RunSQL MySQL

The above is all "aircode" and is untested, but should set you in the right direction.

Just make sure EmailSent = False is a condition in your query.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • ... and for code to send the email via CDO, see the related answer [here](http://stackoverflow.com/a/19594875/2144390). – Gord Thompson Nov 23 '15 at 17:15