Send Data Without Attachments!

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!

This entry was posted in Office. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.