Creating a Continuous Email Service in Microsoft Access using Total
This article and example demonstrate how Total Access Emailer can
support a table driven system to continuously send emails within
your Microsoft Access application.
To do this, we create a simple Access application that regularly
checks for new emails to send. We design an email blast with the
Total Access Emailer add-in wizard that uses fields from a table
that will contain a separate record for each email.
The application then invokes Total Access Emailerís programmatic
interface to send new emails when email records appear.
New emails can be added to the table manually or triggered by
another action in the application (e.g. a new order, a new customer,
an invoice due date). The result is a way for you to send emails
from any Access application by simply adding it as a new record in a
The Access application requires the following objects:
- Email Table to contain the email records to send
- Total Access Emailer add-in to design the table driven
- Access form with timer to continuously check and send emails
The Email Shell table contains all of the important parts of the
- Email addresses (To, From, Cc, and Bcc)
- Message subject
- Message Text and/or HTML
- Optional attachment files
- Audit field (date/time that the message is sent)
In our example, this table is named tblEmails with this
Each record in this table is an email. In our example the table
is local, but it could just as easily be a linked table shared by
everyone in your application. It can also run against a SQL Server
table by using an ADP rather than a Jet database.
Total Access Emailer Add-in
The interactive Wizard add-in of Total Access Emailer lets you
easily create personalized emails. Simply run it with your database,
and interactively specify the options for your email blast. The
options are presented in separate tabs.
Specify the table name (tblEmails). This populates the dropdown
lists for other field references, such as the ToAddress and Subject
Specify the fields for the From, Cc, and Bcc addresses:
For sending plain text messages, use the MessageText field:
For sending HTML messages, use the MessageHTML field:
Specify the AttachmentFiles field to attach any file names in
Make sure auditing is turned on and tied to the SentDate field.
This field is automatically updated with the current time when Total
Access Emailer sends the message, and prevents this record from
getting emailed again.
Other Add-in Features
The Total Access Emailer add-in is also used to configure the
email SMTP settings for sending the messages. You can test this
email blast interactively to confirm it works the way you expect.
With the Professional Version of Total Access Emailer, you can
invoke any of the email blasts programmatically through VBA. It's a
simple procedure call:
TotalAccessEmailer(1, True, "", "", False, False)
The important parameters for our example are:
- The ID identifying the email blast to send (in this case 1).
- The restart option (True) which instructs Total Access
Emailer to ignore the records which already have a value in the
audit SentDate field.
More details about the parameters and programmatic interface are
explained in Sending Microsoft
Access Email Blasts Programmatically (VBA) and Total Access Emailer's documentation.
Form with Timer Event
You can call the TotalAccessEmailer function from any module in
your application. To have it invoked continuously so that it checks
for new records in the tblEmails table, use a form with a timer
event and open that form when the application starts.
You can set this to run normally or invisibly. On the form's
property sheet, set the timer interval to specify how often Emails
are sent. In the attached sample database, the form frmEmailer is
set to 300000 milliseconds (5 minutes).
In the formís Timer event, invoke Total Access Emailerís
programmatic interface to send the new emails. You can also call it
from the form's Open event so that it runs immediately.
When the database opens, you can open the form automatically
through an Autoexec macro or naming it as the startup form.
Sample Database Application
A Microsoft Access database with this example is available for
version lets you run the email blast interactively. To use the
programmatic interface, you need to have a copy of the Professional
Version of Total Access Emailer.
More about Microsoft Access Email