automated email help

[tk]

Hello,
At the moment im building a database for a driving school, I have a
customer field, with email address as an attribute. I dont know much
about macros in access but once the customer has booked a lesson i
want a automated email to be sent to the customer 3 days or a few
days
before their driving lesson, stating the driving instructor, booking
ID time and date etc.
I have 5 tables apart from customer: Employee, Car, Booking, Package
and Session therefore the email will have to take out data from the
session table for the following data booking ID, drving instructor
ID,
session date and time
can someone please help?
Thank you!

 

[Guest]

The simplest way to go about this would be to use a query to pull those
entries that are within your determined span of time (you mentioned due in 3
days) and use a looping function to loop through each record returned by the
query and send the email using the DoCmd.SendOject method.

Below is sample code to get you started. I would also suggest you google
this a bit there are tons of example using SendObject out there.

Function SendEmail()
On Error GoTo SendEmail_Error

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strToWhom As String
Dim strMsgBody As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourQueryName")
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
'This is where you send your email
strSubject = "Your Subject Goes Here"
strToWhom = rst![FieldNameContainingTheRecipientEmailAddress]
strMsgBody = "The Email Body" 'Concatenate the other fields to
build your message as desired
DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
rst.MoveNext
Loop
End If

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

SendEmail_Error:
If Err.Number <> 0 Then
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: Module1 / SendEmail" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
End If
Exit Function
End Function

 

[Guest]

I forgot to mention the code I provide is DAO, therefore you must ensure that
you have the DAO reference library selected as a reference.