Microsoft Access VBA for EmailSending Microsoft Access Email Blasts Programmatically (VBA)

The add-in Wizard in Total Access Emailer lets you interactively create the email blasts you want to send from Microsoft Access.

In the Professional Version, Total Access Emailer offers a VBA programmatic interface that lets you run these email blasts from your MS Access code. This can be tied to an event (for instance, a button click), a macro via the RunCode action, or within your other VBA processes.

The nice thing is the complexity of creating, specifying all the options, and testing/previewing the email blast can be done interactively through the add-in, and you simply launch it in your code.

Contents

Total Access Emailer add-in includes a Code Generator that lets you interactively choose to run or preview the email blast, specify other options, and see the corresponding VBA code for the currently selected email blast. Then paste the code into your module to run your email blast.

VBA Code Generator for Automating Email Blasts from Microsoft Access databases
Code Generator to Send or Preview Email Blasts from Total Access Emailer

By adding Total Access Emailer's royalty-free runtime distribution file as a library reference to your project, you can invoke its procedure. Any email blast created by the add-in can be added to your application with a simple function call. Each email blast is given a number. In this example, email blast #25 is sent:

strError = TotalAccessEmailer(25, False, "", "", True, True)

That's all you need to add the sophisticated email features of Total Access Emailer into your applications. The user interface can be completely hidden so your users don't even realize Total Access Emailer is running in the background. You control your user's experience.

Procedure Definition

Public Function TotalAccessEmailer( _
  lngEmailID As Long, fRestartBlast As Boolean, _
  strDisplayType As String, strDisplayTitle As String, _
  fShowResults As Boolean, fShowErrors As Boolean, _
  Optional strEmailTbl As String, Optional strOptionsTbl As String, _
  Optional strEmbedTbl As String, Optional strOptionsDataTbl As String) _
As String

Procedure Parameters

These parameters are available to customize how your email blast is sent. You can choose to have Total Access Emailer display its progress screens with your title, or hide it completely from your user.

Parameter

Definition

lngEmailID The ID of the email blast to send
fRestartBlast True to restart the email blast if it stopped, or False to start it from the beginning.
strDisplayType Choose whether and how to display the progress of the email blast to the user (form or status bar)
strDisplayTitle The title for the display form and messages to avoid "Total Access Emailer" from appearing.
fShowResults True shows the email blast results in a message box when completed.
fShowErrors True displays the errors table if errors are encountered. False still generates the errors table, but does not display it.

Optional Parameters

By default, the function uses the email blasts stored in the three tables used by the add-in. When you add email blasts to your application, you may not want users to be able to edit them with their add-in. By renaming these tables, you can ensure they are not modified. Simply pass the names of your tables as the optional parameters and the function looks there for the email blast specifications.

Parameter

Definition

strEmailTbl The table containing the email blast settings. The default value is "usysTEmailerSettings".
strOptionsTbl The table containing the Send and SMTP options. The default value is "usysTEmailerOptions".
strEmbedTbl The table containing the Embedded Object information. The default value is "usysTEmailerEmbedded".
strOptionsDataTbl The table containing the Google Gmail OAUTH values and is not necessary if you are not supporting Gmail. The default value is "usysTEmailerOptionsData".

Return Value

The function returns a blank string if it's successful or an error string if there's a problem. You can choose to display that to the user or hide it and process it yourself.

Running the Function from a Macro

Public functions in a standard module (not in a class or behind a form or report) can be run from a macro using the RunCode command. That means you can create a macro that can run an email blast or a series of email blasts. For more details, visit Running VBA Code from a Microsoft Access Macro.

Similar to the TotalAccessEmailer function, you can preview an email blast by sending the email to an email address you specify rather than the recipient in your data source. This lets you or your users verify the emails are correct before actually sending them. This is similar to the Preview feature in the add-in.

Email Preview Function

A function called TotalAccessEmailer_Preview is similar to the TotalAccessEmailer function described above with the addition of two parameters:

Parameter

Definition

strEmailAddress The email address to send the emails. This override the TO email address of the emails you are sending. (Any Cc and Bcc addresses are ignored during preview).
lngRecords Number of records to process

The TotalAccessEmailer_PreviewSaveFiles function supports previewing email blasts with the File Save option. The extra parameter specifies the folder to save copies of the file attachments.

Total Access Emailer offers a function to let you send a single email without creating a pre-defined email blast. It lets you send an email directly from your VBA code while using the SMTP server settings you've already specified in Total Access Emailer. It doesn't have all the features of an email blast such as attaching filtered reports, but it does support the basics of sending a text or HTML email.

Procedure Definition

Public Function TotalAccessEmailer_SendOne( _
  strFrom As String, strTO As String, strCC As String, strBCC As String, _
  strSubject As String, strMessageText As String, strMessageHTML As String, strHTMLFile As String, _
  Optional strAttachments As String, Optional intPriority As Integer, _
  Optional intSensitivity As Integer, Optional fReadReceipt As Boolean, _
  Optional strOptionsTbl As String, Optional strOptionsDataTbl As String) _
As String

Parameters

The parameters let you create standard text and HTML emails with attachments, and message status

  • Email addresses for From, To, Cc, Bcc
  • Subject
  • Text message
  • HTML message or HTML message from a saved HTML file. If the HTML file references graphics in its own folder, those graphic files will be automatically included in the message.
  • Files on disk to attach
  • Message priority, sensitivity, read receipt (see the manual or help file for details)

With the TotalAccessEmailer_SendOne function, you can send emails with more options and control than the built in DoCmd.SendObject method.

The SMTP settings are saved in the usysTEmailerOptions and usysTEmailerOptionsData tables (or tables you specify). This can be easily changed by the Total Access Emailer add-in under Options. Many of the values are encrypted. You can't simply edit the tables.

When you deploy your application with Total Access Emailer to others, they will probably not have the add-in or the ability to run it against your application. They may also not want to tell you their passwords. Total Access Emailer provides the TotalAccessEmailer_SetPasswords function to overwrite the passwords saved in the table. The TotalAccessEmailer_SetGmail function is used to manage the Google Gmail settings.

The TotalAccessEmailer_SMTP_IsValid function validates the saved SMTP settings without sending an email blast. The function returns an empty string if everything is okay, or an error string describing the problem.

This is useful to check before launching an email blast or in conjunction with user input of their SMTP settings.

The TotalAccessEmailer_Email_IsValid function tests a string to see if itís a valid email address with syntax like name@domain. For instance, it makes sure there are no spaces, an @ sign exists, text before and after the @, text after the @ has a period, etc.

Procedure Definition

Public Function TotalAccessEmailer_Email_IsValid(strEmail As String) As Boolean 

Parameters and Return Value

Just pass the email address and the function returns True or False depending on whether it has standard email syntax. The function does not validate that it's an actual email address with a real recipient.

To use the Total Access Emailer function, add the Total Access Emailer runtime file as a library reference from your VBA module editor menu: Tools, References:

Library Reference to Total Access Emailer

Use the [Browse] button to select the appropriate library for your environment. Make sure to change the Add Reference dialog's file type "All Files (*.*)", "ACCDE Files", or "MDE Files" depending on what you need.

There are three Total Access Emailer runtime libraries available depending on your Access database format and Access version:

  • TAEmailR.accde for ACCDB databases running a 32-bit version of Access 2010 or later
  • TAEmailR_64.accde for ACCDB databases running a 64-bit version of Access 2010 or later
  • TAEmailR.mde for MDB or ADP databases running Access 2003 or later

You need to select one of the Runtime libraries. Otherwise, when you try to compile, you'll get a compile error: Sub or Function not defined

Compile Error with Total Access Emailer if Referencing Wrong Runtime Library

If your database that includes the Total Access Emailer reference is run by other users, you'll need to deploy it properly. This depends on whether it's deployed across your network or to another location.

For more information, visit Distributing Microsoft Access Databases with Total Access Emailer to Other Users.

With the programmatic interface, you can easily add table driven emailing systems within your Microsoft Access applications. More details in Creating a Continuous Email Service in Microsoft Access.

Free Product Catalog from FMS