Using Outlook / Email with Access

[shaunburke]

I have a Access training database which tracks employees trainings as well as their annual recertification dates on particular trainings. Is there a way to have an email sent to the employees automatically when their recertification date is 1 month away?

 

[Moniker]

Not to be terribly rude, but did. You. Search. At. All. In. This. Forum. Question Mark.

Short answer: Yes, there are at least a dozen ways to do it.

Bigger questions: What have you tried? Where is the code? What error messages do you get? Are there email attachments? Are you using Outlook? How experienced are you in programming? Have you tried to do this at all?

 

[shaunburke]

I have looked through the Forum and found some things that could help, but I am not learned in a lot of code and I'm fairly new to Access, so I'm guessing at best.

I have a query that pulls employees Annual Recertification dates (annualRecertificationdates) this lists each employee (Employee ID), their department (loc), and the Trainings that each employee needs recertification for (Training Title), the expiration date (Expr1: Max)

So, what I'm trying to do is when it's a month from their expiration date have an email automatically sent

 

[Moniker]

The things you'll want to study up on are:

DateDiff function -- will allow you to determine when an employee is 30 days from recertification requirements

DoCmd.SendObject -- will allow you to send an email to one or more people, including a subject line and a message body. Note that ObjectType in SendObject is optional as are all the fields, so skip what isn't necessary. For example, this will send an email to John Smith at ABC Company with the subject "Test" and the Message "Hello World":

DoCmd.SendObject ,,,"johnsmith@abc.com",,,"Test","Hello World",False

That will send the email without bringing up the email first for editing. Look at the EditMessage argument (the "False" at the end) in Access Help for SendObject. Changing that to True will open the email in a new email dialog for Outlook but allow you to edit it.

Both DateDiff and SendObject have good help entries in Access and should put you on the right track.

Generically, this is what you'll do (this is fake code, so don't copy/paste it -- use it as a guide):

If DateDiff("d",Now(),annualRecertificationdates) <= 30 then
    EmpEmail = Use code to get Employee email address from EmployeeID
    txtEmail = "Dear " & EmployeeName & ", " & vbCrLf
    txtEmail = txtEmail & "Our records indicate that you will need to be recertified in " & TrainingTitle & vbCrLf
    txtEmail = txtEmail & "within the next 30 days.  Please contact HR to schedule this." & vbCrLf & vbCrLf
    txtEmail = txtEmail & "Sincerely, " & vbCrLf & "The Support Staff"
    DoCmd.SendObject ,,,EmpEmail,,,"Recertification Reminder", txtEmail, False
End If