Send email using Microsoft Access and Outlook using VBA

This page shows how to send email from your database using Microsoft Access, Outlook and VBA, and how creation of email processes is made easy using the email wizard, email body HTML Template Designer and VBA code generation included in 4TOPS Document Creation using Microsoft Access – which can be downloaded here….

This approach can be applied with organizations storing data in SQL Server, Oracle, SharePoint, Dataverse, etcetera by using MS Access as an application front-end.

Sending email from your database

When organizing your work inevitably you will maintain email adresses of your contacts – whether they be customers, suppliers, employees or other relations. The common approach then to sending emails is to write VBA code that does the job. This way, there are no restrictions on what you can achieve: who it will be send to, the subject line and html body, possibly personalized, attachments and so on – all based on your database.

Microsoft Access VBA Email code generation

If you have some experience writing VBA you can easily find some code online for you to adapt to your purpose. However if emailing is an essential part of your application and you want to include more sophisticated features things may soon be getting out of hand. In such case you will be better of using the Email wizard and Outlook HTML body Template Designer included in 4TOPS Document Creation using Microsoft Access (in the image below the added ribbon tab). With these tools, the VBA email code is automatically generated while your task is simplied to writing the email and specifying what features you want. Actually, you need not even be aware of the VBA that implements the emailing process. To run it, no software needs to be installed on the user’s computer.

Table of Contents

Email creation wizard

In the following common requirements are reviewed and how you can specify them in the Email wizard. On wizard’s Finish, the VBA code, supporting modules and a reference to Outlook are added to your project. The code will at least involve instantiation of an Outlook MailItem object (mimEmail) and a mail action, depending on how the procedure is called.

Public Function My_Outlook_Email( _
     Optional MailAction As dcMailAction = dcMailAction.Display) As Variant
Dim appOutlook As Outlook.Application
Public Function My_Outlook_Email( _
      Optional MailAction As dcMailAction = dcMailAction.Display) As Variant
Dim appOutlook As Outlook.Application
Set appOutlook = New Outlook.Application
Dim mimEmail As Outlook.MailItem
Set mimEmail = appOutlook.CreateItem(olMailItem)
'specify To, Subject, Body, Attachments, etc.
'...
Select Case MailAction
Case dcMailAction.Display
    mimEmail.Display
Case dcMailAction.Send
    mimEmail.Send
Case dcMailAction.Draft
    mimEmail.Close olSave
End Select

Email recipient roles: To, CC, BCC

After starting the Email wizard from the ribbon (image above), the first step is to specify one or more recipients.

First, you can type or paste an email adress in a recipients role textbox. Alternatively, first select what you want to use to fill a recipients role page with you press on the lists on one of the tabs …

• Contacts: Email recipient from Outlook Contacts, useful if you always want to CC your boss.
• Source: A field (control) from the data source.

… and then press the button with the role (e.g. CC) where you want the item to be inserted.

Send email to multiple recipients

Taking a field from the source means the recipient will dynamically be taken from the current record, e.g. the contact you want to send the invoice to. In this example, if you are sending invoices to multiple clients the email address of that client is automatically used.

In the above the To thus has been filled with a VBA expression that will be dynamically evaluated, resulting in two persons receiving the mail in this case.

mimEmail.To = ControlValue(![E-mail Address]) & ";" & "sue@cottonville.co.uk"

ControlValue is a function included by the VBA generator. The correct formatting of the expression is done by the wizard.

Dynamically setting the email Subject

Pressing Next brings us to the possibily to either enter a fixed subject text, or have it evaluated from a string expression. Excercise: Try to figure out what the subject will be for this expression.

  mimEmail.Subject = "Invoice " & ControlValue(![InvoiceID]) & "- atn " & ControlValue(![First Name]) & " " & ControlValue(![Last Name])

Create Outlook HTML body using the Template Designer

Next we will create the HTML body template. This will be a .html file stored in a chosen Template folder. Note the central role played by the HTMLFiller class: it places data obtained from the data source (here the Access form) into the bookmarked positions of the template.

  Dim hfr As New HTMLFiller
  hfr.Load FileName:=strTemplateFile
  hfr.FillTableFromArray _ Bookmark:="MaterialLinesubform", var2D:=arr2D
  hfr.FillElement Bookmark:="FirstName", _ Value:=ControlValue(control:=![First Name])
  '...
  mimEmail.HTMLBody = hfr.HTML

Email HTML body Template Designer

After specifying the name press the Create button. This opens the Template Designer, a WYSIWYG HTML editor resembling the Outlook email editor with the added data items pane on the right side. Start editing your message, drag and drop data bookmarks from the pane in appropriate positions.

Send email with signature

From the ribbon you can select a signature and have it added to your email template. Alternatively, you can make this variable depending on the actual person sending the email using a [signature] text field with Tag HTMLFile containing the signature as HTML.

hfr.InsertFile Bookmark:="HTMLFile", _
    FileName:=ControlValue(control:=![HTMLFile])

Preview and refine

When ready, closing the editor will return you to the wizard. You can review your work now by pressing the Preview button which will make an email with the recipients, subject and the body as you made it so far. You can now Edit the template to make further refinements or go to the subject or recipients pages using the Previous button.

Send email with attachment

To attach an existing file such as Terms and Conditions to the email, press Add file … which opens a file selection dialog.

To attach a dynamically select the document creation process that will produce it. Only processes that save the created document are listed. The function that implements the process returns the filename.

Dim strFile As String
strFile = _ 
    Invoice_time_and_materials_PDF_Current()
mimEmail.Attachments.Add strFile