Access has always provided a way for you to send data via e-mail, but your data has to go out as an e-mail attachment. As Richard Hunt points out, using attachments creates its own set of problems. In this article Richard provides an alternative to using attachments.
For a long time, I’ve wanted to be able to send the results of a query by e-mail without having to attach the query result as a separate file. There are plenty of articles and tips on how to output a query to Excel and send it as an attachment. Unfortunately, virus-checking software at the recipient’s end will create difficulties when receiving a document (like Excel) that can contain macros. If you want to avoid file types that support macros, your alternative is text files—and losing all your formatting. In my job, I’ve created several systems that send numerous database event logs to me (and others). It can grow tiresome to open eight or 10 attachments from different automated Access databases just to make sure everything ran according to schedule. Personally, I’ve never been a big fan of having to surf through attachments at the best of times.
No matter where I looked, I couldn’t find an example that demonstrated a method—simple or otherwise—for sending a recordset in the actual body of an e-mail. So I set out to find a way and came up with an elegant solution that works with any query. I’ve started using this system myself to deliver quick shots of data to users through e-mails. My users arrive in the morning and find their morning data-gram waiting for them in a format that they can read or ignore.
Technologies and software
First, a controversial statement: The solution that I came up with uses DAO. I’ve worked with DAO for years, and I prefer it when working in Access. Although Microsoft support for DAO isn’t as high-profile as its support for ADO, white papers by the company suggest that DAO is more efficient than ADO when working natively in Access. If you prefer ADO, it should certainly be possible to convert the code to ADO. One other warning: The process also relies on HTML rather than RTF or plain text messaging for formatting the text.
In my code, I’ve made two assumptions:
- There’s a version of Outlook installed on the computer that the messages are sent from.
- The recipient is using an e-mail client that can receive HTML-based messages.
Outlook doesn’t need to be open during the send operation, but it does need to be configured with a valid exchange account. I used Access 2000 with a reference set to the DAO 3.6 library and Outlook 9.0.
Getting started
The first thing you’ll need is the SendMessage() function. I found it on the Microsoft Web site during my search for solutions. The code is reprinted here with only minor modifications for my process (for instance, the variables that hold the message strings are declared as public and, most importantly, the body type is changed to HTML).
The top part of the code declares the variables that are used to pass data to the routine and the variables to work with the Outlook objects:
Public strRecipient As String Public strSubject As String Public strBody As String Sub SendMessage(Optional AttachmentPath) Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment
The next two lines of code load Outlook into memory and get a reference to Outlook. Once that’s done, the code creates an Outlook mail item:
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With the mail item created, the next steps configure the message. First a recipient is added to the mail message’s Recipients collection, and the recipient is set as a “To” type (as opposed to a cc: or bcc: type). In my code, I load the strRecipient variable with the person I’m sending the message to:
With objOutlookMsg Set objOutlookRecip = .Recipients.Add(strRecipient) objOutlookRecip.Type = olTo
With the message now going the right person, the code starts loading the information into the message. Since I load the strBody string with HTML formatting tags, I set the mail message’s HTMLBody property. It may just be ego on my part, but I also flag the message as of high importance:
.Subject = strSubject .HTMLBody = strBody .Importance = olImportanceHigh
Since I don’t use attachments, I could have omitted the next set of code snippets, which adds a filename to the mail message’s Attachments collection. However, just in case I ever want to use attachments, I’ve left the code in:
If Not IsMissing(AttachmentPath) Then Set objOutlookAttach = _ .Attachments.Add(AttachmentPath) End If
Simply adding a recipient to the recipients list isn’t enough to get the e-mail to the intended recipient. The recipient names must be resolved into e-mail addresses. Each item in the recipients collection must have its Resolve method called to handle the conversion. If the Resolve fails it returns False, in which case the code displays the mail message:
For Each objOutlookRecip In .Recipients If Not objOutlookRecip.Resolve Then objOutlookMsg.Display End If Next
If all goes well, the message is sent and variables set to Nothing:
.Send End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub
Simple messages, minimal formatting
Now that you have the code to send the query, it’s time to look at the query itself. This example uses a SQL string, but any DAO recordset reference will work. This is the code for retrieving a recordset from my database event log:
Function ReadEventLog() Dim strSQL As String Dim dbs As Database Dim rst2 As Recordset Dim strTemp As String Dim strSpace As String Set dbs = CurrentDb() strSQL = _ "SELECT TBL000LOGEV.Date, TBL000LOGEV.Time, " & _ "TBL000LOGEV.Type, TBL000LOGEV.Event, " & _ "TBL000LOGEV.Region " & _ "FROM TBL000LOGEV " & _ "WHERE (((TBL000LOGEV.Date)=Date()));" Set rst2 = dbs.OpenRecordset(strSQL, dbReadOnly)
The table the recordset is based on contains only five fields: Date, Time, Type, Event, and Region. The recordset uses the WHERE clause to restrict data to today only.
The next set of code starts the process of creating the message body. The first bit of HTML (three non-breaking spaces) appears here and is held in the strSpace variable. This string will be used to put some space between columns in the recordset within the message body:
strSpace = " " strBody = "<FONT FACE=""Century Gothic"" Size=2>" Do Until rst2.EOF strTemp = rst2!Date & strSpace & rst2!Time & _ strSpace & rst2!Event & strSpace & _ rst2!Region & "<BR>" strBody = strBody & vbCrLf & strTemp rst2.MoveNext Loop strBody = strBody & "</FONT>" End Function
The strBody variable is set to hold the initial HTML for the body—a tag that sets the font for the body. The code spins through the recordset using the strTemp variable to feed and append the data from the recordset into the strBody variable. I use my HTML string of spaces to provide ample spacing.
Now all that’s left is the code for sending the message:
Set rst = dbs.OpenRecordset("TBL000EMAIL")
rst.Index = "PrimaryKey"
rst.Seek "=", 1
strRecipient = rst!Recipient
strSubject = rst!Subject
rst.Close
SendMessage
This code pulls the recipient information from a simple table consisting of a primary key, the recipient’s e-mail address, and a subject line. I use that information to populate the public variables used by the SendMessage() function. The SendMessage() function is then called using the populated variables.
Table formatting
This next example is really just an extension of the first. This self-contained routine sends a message using HTML table tags to format the layout of the records in the message. The start of the routine declares the necessary variables:
Function SendRecordset02() Dim strSQL As String Dim dbs As Database Dim rst2 As Recordset Dim strTemp As String Dim strTDStart As String Dim strTDEnd As String Dim strTRStart As String Dim strTREnd As String Dim strSpace2 As String Dim x As Integer Set dbs = CurrentDb() The next step is to set the SQL statement and HTML: strSQL = "SELECT * FROM QRY001PANS6;" strTDStart = "<TD><FONT SIZE=""-1"">" strTDEnd = "</FONT></TD>" strTRStart = "<TR>" strTREnd = "</TR>"
The HTML code is used instead of the three non-breaking spaces held in a variable from my previous example. The HTML strings are used in this code to create an HTML table in the body of the message. The initial code sets up the start of the table, including the headings at the top of each column (“Team”, “HH”, “EOHH”, and “MTD”). In the TABLE element I’ve set the font, the overall width of the table, the border, and the padding and the spacing of the table cells with standard HTML:
Set rst2 = dbs.OpenRecordset(strSQL, dbReadOnly) strBody = "<FONT FACE=""Century Gothic"">" & _ "<TABLE WIDTH=""30%"" BORDER=""1"" " & _ "CELLSPACING=""0"" CELLPADDING=""2"">" & _ strTRStart & strTDStart & "<B>TEAM</B>" & _ strTDEnd & strTDStart & "<B>HH</B>" & strTDEnd & _ strTDStart & "<B>EOHH</B>" & strTDEnd & _ strTDStart & "<B>MTD</B>" & strTDEnd & strTREnd & _ "</B>"
The next part of the code loops through the recordset, placing each field inside a cell inside the table. Each record becomes one row in the table:
Do Until rst2.EOF strTemp = strTRStart & strTDStart & rst2!GROUP & _ strTDEnd & strTDStart & rst2!HH & strTDEnd & _ strTDStart & Format(rst2!EOD, "0.0%") & _ strTDEnd & strTDStart & _ Format(rst2!MTD, "0.0%") & strTDEnd & strTREnd strBody = strBody & vbCrLf & strTemp rst2.MoveNext Loop
I use the Format function to format a couple of columns of data as percentages (%) since the formatting in the recordset is otherwise ignored. Without the Format function, the data prints as decimal numbers.
With the recordset transferred into table rows, I add the HTML to close off the table. As in my previous example, I get the header information from my recipients table—this time adding the date to the subject line—and send the message:
strBody = strBody & "</FONT></TABLE>"
Set rst = dbs.OpenRecordset("TBL000EMAIL")
rst.Index = "PrimaryKey"
rst.Seek "=", 5
strRecipient = rst!Recipient
strSubject = rst!Subject & " for " & DATE - 1
SendMessage
End Function
The results can be seen in Figure 1.
Figure 1
A cool trick
Since this method is based on good old HTML, there are a few other possibilities for formatting the recordset. While I was writing this article I decided to try alternating the colors for each row for greater readability. The first thing I created was a simple function to test for odd/even numbers:
Function IsOdd(lngNumber As Long) As Boolean IsOdd = lngNumber And 1 End Function
Then I declared another variable to hold the HTML. The HTML in this variable contains the code to set a cell’s color to a ledger-style light green:
Dim strStartTD2 As String strTDStart2 = "<TD BGCOLOR=""#C0FFC0""><FONT SIZE=""-1"">"
In my code, I have an If statement that uses the function to test the recordset’s record count, and format even records with my new tag (see Figure 2):
If IsOdd(rst2.RecordCount) = False Then strTemp = strTRStart & strTDStart & rst2!GROUP & _ strTDEnd & strTDStart & rst2!HH & strTDEnd & _ strTDStart & Format(rst2!EOD, "0.0%") & _ strTDEnd & strTDStart & _ Format(rst2!MTD, "0.0%") & strTDEnd & strTREnd Else strTemp = strTRStart & strTDStart2 & _ rst2!GROUP & strTDEnd & strTDStart2 & rst2!HH & _ strTDEnd & strTDStart2 & _ Format(rst2!EOD, "0.0%") & strTDEnd & _ strTDStart2 & Format(rst2!MTD, "0.0%") & _ strTDEnd & strTREnd End If
The HTML in the message has the added benefit of being cut-and-paste friendly. So, if one of your recipients needs the data in Excel, they can move the data from the e-mail to Excel by copying and pasting the text in the e-mail. There are, of course, other possibilities limited only by your imagination and the capabilities of HTML.
Figure 2
Conclusion
This process is a great way to send short informational bursts of data without the bother of opening attachments. As with any tool, there are some cases where you wouldn’t want to use it to send data. In particular, you wouldn’t want to send large recordsets, since large amounts of data in an e-mail would be both too much to scroll through and unwieldy to work with. Just because the limit for variable-length strings is 2 billion characters, that doesn’t mean you want a message body that large!
