By Helen Feddema
Access versions: 2007-2013
Level:  Intermediate
Access Archon #240:

Introduction

In a database (like the sample Northwind database) that records orders, you might want a quick and easy way to send order lists to customers.  This article demonstrates how to send order lists to customers in both Plain Text and HTML format emails.

The Main Menu

The sample database for this article, Emailing Orders to Customers.accdb, has a New Style Main Menu (see Access Archon #212 for more details on this type of menu), with a large button for opening the main form, a selector for opening other forms, and a group of controls used to filter order records for emailing order lists:

Figure A.  The main menu of the sample database, with controls for filtering order lists

The Start Date control’s row source is the OrderDate field in tblOrders, and once you select a date from this combo box, the End Date combo box’s row source is filtered for dates equal to or later than the selected start date.  This method is preferable to letting users enter any dates, since it prevents the selection of date ranges with no orders.

The Select Customer control lets you select a customer from tblCustomers, using a filter to select only customers with orders, and applying a date range filter if a date range has been selected.  There is also an [All] selection so users can explicitly select all customers.  For both the date range and customer filters, if no filter value is selected, all dates (or all customers) are selected.

The option group at the bottom of the main menu offers a selection of Plain Text (the default selection) or HTML for the mail message format; when you click the Email Orders to Customers button, one of two procedures runs to create and open (but not send) emails to a customer, or to all customers.  The two procedures are listed below:

1	'Create emails for all customers

1	'Create filtered recordset of orders for this customer

1	'Create email for this customer

1	'Create header line

1	'Put orders data into body of email

1	'Create line of body text

1	'Create email for selected customer

1	'Create email for this customer

1	'Create header line

1	'Put orders data into body of email

1	'Create line of body text

1	'Create emails for all customers

1	'Create filtered recordset of orders for this customer

1	'Create email for this customer

1	'Create starter HTML text for this customer

1	'Put orders data into body of email

1	'Create line of body text

1	'Create email for selected customer

1	'Create email for this customer

1	'Create starter HTML text for this customer

1	'Put orders data into body of email

1	'Create line of body text

A Note on Outlook HTML

The dialect of HTML used in creating an Outlook HTML message is rather peculiar.  It seems to more or less correspond to HTML 3.2, circa 1993 (at least, an HTML manual for that version was helpful).  One extra peculiarity is the font sizing – instead of using actual font sizes in points (10 pt, 12 pt, etc), Outlook HTML uses sizes 1, 2 and 3.  When writing code to create an HTML email message, you may need to do a lot of trial and error tinkering to get the results you want.

The Customers and Orders Form

The main form in the sample database is frmCustomersAndOrders, a main form with a subform displaying orders for each customer.  The form footer has an option group for selecting the email format with a button for generating a message listing all orders for the customer, and a button for previewing a report of all orders for the current customer:

Figure B.  The Customers and Orders form, with controls for emailing an orders list or previewing a report.

The Email Orders button uses the same code as the button on the main menu, but just the portion that creates an email for a single customer.

The report for Alfreds Futterkiste is shown below:

Figure C.  The Orders report for a single customer

References

The code in the sample database needs the following references (in addition to the default references):

Microsoft DAO 3.6 Object Library (if running a version of Access older than 2007)

Microsoft Outlook 14.0 Object Library

Microsoft Word 14.0 Object Library (for SortDeclarations procedure only)

Microsoft Forms 2.0 Object Library (for SortDeclarations procedure only)

If you import code or objects into a database of your own, you may need to set one or more of these references.  The version number may differ, depending on your Office version; check the version you have.  References are set in the References dialog, opened from the VBA window.  For more information on working with references, see Access Archon #107, Working with References.

Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch240.zip, which is the last entry in the table of Access Archon columns for Access Watch.

Document Name	Document Type	Place in
Emailing Orders to Customers.accdb	Access 2007-2010 database (can be used in higher versions)	Wherever you want