From Access to Automated E-mail

Garry Robinson

Microsoft Access supports Microsoft Exchange and Microsoft Outlook through a downloadable Link Manager. This article shows you how to set up a Web-based system to help automate your e-mail collection using Access 97.

Access can work with more than just relational data. In this article, I'm going to show you how to tie Access into Microsoft's mail server, Exchange. The processes involved are a simple HTML-based form, your e-mail software, Microsoft Exchange, and Microsoft Access. Even if you're completely HTML-clueless, you should be able to set up a complete mail processing Internet application with the material in this article. If the techniques in this article inspire you to investigate further, you might want to go through your back issues and take a look at Helen Feddema's article, "Using Outlook as a Front End for Access Data," in the August 1997 issue or David Shank's article, "Expand Your Outlook with Access 97," from the February 1997 issue.

I should warn you, though, that unless you develop for a tightly controlled in-house e-mail setup, you'll find that the solutions offered here will probably have to be customized for each site. Generally, every mail installation has its own peculiar mail server configuration. Ignoring those details, the processes that I'll outline here will be similar for each site. More important, the Access software components I'll describe are completely reusable. In this article, I've used Exchange as my mail client for my examples and in my screenshots, but the techniques shown are very similar if you're working with any MAPI-compliant e-mail client (see the sidebar, "Where Art Thou, MAPI?").

The accompanying Download file contains an Access 97 software database called Mail-fx.MDB and an HTML document called SoftwareDownload.htm. You'll also need Microsoft's Exchange linker, which you can download from http://support.microsoft.com/support/downloads/. Follow the links to the MS Office page and then to Access 97. The download file is called wzmapi80.exe and is about 1M in size. It has a very straightforward installation process.

Linking to Exchange
If you're going to use Exchange as your database, you'll need to transfer some mail over to it. Prior to using the Exchange linker, I manually transferred my e-mail messages across to Exchange. To do this in Outlook Express, select File | Export and then select the Exchange folder that you want to export your mail to. At this point, you'll actually want to ensure that MAPI has been turned off in your default e-mail service so that you can open the MS Exchange linker.

Once you have some mail in Exchange, you can link one or more of its folders into an Access database as a table. Linking to Exchange data is done the same way that you'd link to table data. Figure 1 shows the first step in this process: I've right-clicked in the database window and selected Link Tables from the popup menu. This figure also shows the end of this process: GrafDownloads is a linked Exchange table that I've already established.

The next step in linking an Exchange table is to find the Exchange details file. In my case, I found it in the Windows directory. In my installation, the file was called Personal.Pab, but you can also use MailBox.Pst (see Figure 2). Choose one of those files and click on the Link button. While the method I've outlined here works, you can actually get away with selecting any file on your computer. A third method is to select the Add-Ins menu choice and choose the Link-Exchange Outlook Wizard.

Now you need to choose the folder that you want to link to (preferably the one holding your messages). If you don't have the display shown in Figure 3, then you should exit Access, re-boot, and try this process again. Double-clicking the selected folder will add it to your Database window as a linked table. After selecting the folder, you're given an opportunity to give the resulting Access table a different name.

If you want to follow along with the rest of this article, you should open the demonstration database that I've supplied in the accompanying Download file. To ensure the demonstration would work without Exchange or Outlook, I ran a Make Table query to transfer some of my Exchange table data to a normal Access table called SoftwareDownloads. This is an exact replica of the data from my linked Exchange table, and it will allow you to run the demonstration software without first setting up the Exchange links.

Table 1 shows the structure of a linked Exchange table. You'll probably recognize most of these fields from your e-mail program. The names of the fields are sufficiently descriptive that I won't take up space describing them here.

Table 1. Fields in a linked Exchange table.

Name	Type	Size
Importance	Number (Long)	4
Message Class	Text	255
Priority	Number (Long)	4
Subject	Text	255
From	Text	255
Message To Me	Yes/No	1
Message CC to Me	Yes/No	1
Sender Name	Text	255
CC	Text	255
To	Text	255
Received	Date/Time	8
Message Size	Number (Long)	4
Body	Memo	-
Creation Time	Date/Time	8
Last Modification Time	Date/Time	8
Subject Prefix	Text	255
Has Attachments	Yes/No	1
Normalized Subject	Text	255
Row Type	Number (Long)	4
Instance Key	OLE Object	-
Object Type	Number (Long)	4
EntryId	OLE Object	-
Depth	Number (Long)	4
Content Unread	Number (Long)	4

A simple Web form
Now I'm going to show you how to capture data on an intranet or Internet site and have it mailed to you. For this article, I created a very generic HTML Web form that asks people to provide some information prior to downloading a file from my Web site. Figure 4 shows this simple download form.

Here's the HTML that creates this download page. It's very vanilla HTML and includes no scripting or calls to executable programs:

 <html>
 <head>
 <title>Software Download Form</title>
 </head>
 <body bgcolor="#00FFFF">
 <p>
 <small>This form is for downloading software.</p>

 <p>First you should fill in your details, then
  click below to download. </small></p>
 <form method="POST" ENCTYPE="text/plain" 
  action="mailto:youre-mail@gr-fx.com">
 <pre>
 Name     <input type="text" 
   size="35" name="Username">
 E-mail   <input type="text" 
   size="35" name="UserE-mail">
 Company  <input type="text" 
   size="35" name="UserCompany">
 Country  <input type="text" 
   size="35" name="UserCountry">
 </pre>
 <p>Where did you find out about our software
    and what will you use it for?<dl>
 <dd>
 <textarea name="UserComments" 
   rows="3" cols="40"></textarea>
 </dd>
 <dt>&nbsp;</dt>
 <dd>&nbsp;&nbsp;&nbsp;
 <input type="submit" value="Post Your Details Here "> 
 <input type="reset" value="Clear Form"></dd>
  </dl>
 </form>
 <b>
 <a href=
  "http://www.users.bigpond.com/fx/zip/graf97.exe">
 <font size="4">
 Click to download the software</font></a></b>
 </body>
 </html>

For those of you who are new to HTML, these are the things that you should look for in this page:

<Form Method -- This is the start of the definition of the form in the page. The fields that are listed in the e-mail that this form generates are defined in this section of the HTML. The end of the form is marked by </form>.
Action="Mailto -- This is the form Action parameter. Setting the form's Action parameter to Mailto causes the browser to e-mail the data on the form to the address following "Mailto," using whatever e-mail client the user has installed on his or her computer. I'd recommend that you set up this address and the e-mail client that will be receiving this mail so that when the mail arrives, it will be placed into a standalone e-mail folder. This might be an excellent time to learn how to use your inbox assistant. Segregating the mail that comes from the form will make processing of the e-mail by Access a little easier. In this sample page, for instance, I use a different e-mail address from my normal business address. Regardless of how you do it, you need to get this mail routed to the folder that you've linked into your Access database.
<Inputname="Username" -- Input defines an element on the page; in this case, a text box. The Name parameter gives a name to the text box. When you receive the mail, each piece of information in the text of the message will be flagged with the name of text box on the HTML form that the data was entered into. You should use unique names so that you can decode the data in the e-mail later in Access.
<input type="submit" -- This creates the Submit button on the form. If you want to get fancier, this is where you can start up your CGI scripts and other more intelligent form-based programs.
<a href="http://www -- This tag establishes the standard Web hyperlink to a file on a Web site somewhere. If you don't have an FTP site on which to store your software, it's quite okay to store your software on your normal Web site and reference it using an HTTP address. When a user clicks on this link, the file it points to will be saved to the user's computer. Note that this form allows the user to download the software without entering the requested information.

Decoding the data
Let's skip ahead to when a number of users have filled in the response form and, as a result, sent you e-mail that now resides in the linked Exchange folder (in my example, this folder is called SoftwareDownloads). With the data received, you need to decode the messages.

You'll have one entry in your linked table for each message you've received. For this exercise, the only field in the table that you're interested in is called Body (a memo field). An example of what you might receive is shown in Table 2.

Table 2. The Body field from a linked Exchange table.

Received Message size Body

27/09/98 12:10:31 PM

678

Username=Terry L. Farmer
UserE-mail=tfarmer@millicent.com
UserCompany=Millicent Pty Limited
UserCountry=USA
UserComments=learning about data mining and helping analyze trouble ticket data
Search=web search via infoseek

The Body field data is plain text with the following characteristics:

Each field on the form generates a new line in the message that's terminated by a carriage return.
Each line begins with the name of a field.
The name of the field is immediately followed by an equal sign.
The equal sign is immediately followed by the text that the user entered into the field.

I created a form called FX_ExtractBody that reads this table and decodes the data in the Body field. FX_ExtractBody is a one-button form that parses the data using the code that follows. The code creates a recordset based on the linked Exchange table for input and, as the data is parsed out, updates another recordset. The form displays each record before it's posted to the new table, so that you can manually confirm that the user details are worth adding.

' Set the current database and define the two recordsets. Set dbs = CurrentDb Set rstExchange = dbs.OpenRecordset("SoftwareDownloads") Set rstUsers = dbs.OpenRecordset("SoftwareUsers", DB_OPEN_TABLE) rstExchange.MoveFirst Do Until rstExchange.EOF UserName = ExtractDetail(rstExchange!body, "userName=") UserE-mail = ExtractDetail(rstExchange!body, "userE-mail=") ...processing for the remaining fields... If Len(UserE-mail) > 0 And _ InStr(UserE-mail, "@") Then postIt = _ MsgBox(UserName & " " & UserE-mail & " " & _ UserCompany & " " & UserCountry & " " & _ AccessVersion _ & " " & UserComments, _ vbYesNoCancel, "Post The Following") If postIt = vbYes Then On Error Resume Next rstUsers.AddNew rstUsers("userName") = UserName rstUsers("userE-mail") = UserE-mail ...updates for remaing fields... rstUsers.Update On Error GoTo errCmdUserDetails Else If postIt = vbCancel Then GoTo exitCmdUserDetails End If End If End If rstExchange.MoveNext Loop exitCmdUserDetails: rstExchange.Close rstUsers.Close Set dbs = Nothing Exit Sub errCmdUserDetails: Error Err.Description GoTo exitCmdUserDetails End Sub

As you can see, a lot of the work is done by the ExtractDetail function. Passed a string and the name of a field, ExtractDetail will find the field in the string and return the text between the following equal sign and carriage return. For a Body field containing UserCountry=USA somewhere in it, calling ExtractDetail and passing "Country" will cause the function to return "USA" as a text string.

Public Function ExtractDetail(textLine As Variant, FormItemReq As String) As Variant Dim StartLine As Variant Dim EndLine As Variant Dim ExtractText As Variant StartLine = InStr(textLine, FormItemReq) If StartLine > 0 Then StartLine = StartLine + Len(FormItemReq) EndLine = InStr(StartLine, textLine, Chr(13)) ExtractText = Mid(textLine, StartLine, _ EndLine - StartLine) End If If Len(ExtractText) = 0 Then ExtractText = " " ExtractDetail = ExtractText End Function

The form transfers the e-mail data from the linked Exchange table to a table called SoftwareUsers. In the SoftwareUsers table, I have an E-mailSent field that lets me keep track of whether I've sent a reply e-mail to the user. I use the UserE-mail field as the primary key field to avoid adding multiple entries for the same person. Table 3 shows a sample of the SoftwareUsers field.

Table 3. The table of user responses.

UserName UserCompany UserCountry UserE-mail E-mailSent

Garry Robinson GR-FX Pty Limited Australia access@gr-fx.com Yes

M Derrick AM Ltd UK derrek@parkview.com.uk No

Shane Warner Lucky Co USA tlwarner@luckyco.com No

Terry L. Farmer Millicent Pty Limited USA tfarmer@millicent.com No

Automating replies
Now that I've built a usable Access table, it's time to auto-generate an e-mail to my new users. This section of the demonstration database is completely independent of which e-mail client you use, as long as it's MAPI-compliant. To handle this processing, I created the form FX_MailCentre, as shown in Figure 5. Features of this form include the optional selection of a trial e-mail plus the ability to enter your own subject, greeting, and standard message. When the e-mail is generated, you can personalize the message prior to finally sending it.

All the important code for this form lies under the button labeled E-mail The List. In the following code, look for the SendObject command, which builds an e-mail message using the entries on the form:

Set dbs = CurrentDb Set rstMail = _ dbs.OpenRecordset("select * from softwareUsers " _ & whereStr) If rstMail.RecordCount > 0 Then rstMail.MoveFirst Do Until rstMail.EOF ' Begin loop. If Len(rstMail!UserE-mail) > 0 Then postIt = MsgBox(UserName & " " & _ rstMail!UserE-mail & _ " ... " & rstMail!UserName & " ... " & _ rstMail!UserCompany, vbYesNoCancel, _ "E-mail The Following") If postIt = vbYes Then DoCmd.SendObject acSendNoObject, , _ acFormatTXT, rstMail!UserE-mail, , , _ Me![SubjectReq], Me![GreetingReq] & " " _ & rstMail!UserName & _ Chr(10) & Chr(10) & Me![Instructions] & _ Chr(10) & Chr(10) & rstMail!UserComments ' Update the e-mail sent box rstMail.Edit rstMail("E-mailSent") = True rstMail.Update End If End If rstMail.MoveNext Loop End If rstMail.Close Set dbs = Nothing Exit Sub

Making it with mail
Using the Exchange link table manager to integrate your e-mail and address book into your Access systems will allow you to extend your Access skills into the important arena of electronic communications. If you have an investment in an intranet or the Web, you can start managing the responses generated on the Web sites using HTML-based forms and some VBA code to decode the e-mail. Finally, thanks to Access, you can reply in a systematic manner to these responses by looping through the decoded data.

In fact, you can use Access to do some analysis of the material that you've received. Your e-mail folders consist of messages with dates, so why not consolidate your e-mails by week and report the frequency of e-mail messages coming into your e-mail server? Those suspicious ones among you might even consider writing a query to find all the spam messages and delete them before you waste time reading them. The e-mail messages linked through Access can be deleted and changed as though they were ordinary tables. And all of this is possible because of Access's ability to treat a variety of data sources as relational tables.

Download EXCHANGE.exe

Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. Garry has developed many Access applications for clients in small business, mining, and other areas. Garry found himself thrust into the Web as a way of promoting the data mining program Graf-FX. Given that Web software specialization is a science in itself, these Web/e-mail techniques are tempered by Garry's own field of expertise, MS Access. +61 2 9665 2871, http://www.gr-fx.com/, Garry@gr-fx.com.

Sidebar: Foundation Concepts
The part of your e-mail system that resides on your desktop is your e-mail client. Microsoft provides at least three clients: Exchange, Outlook, and Outlook Express. All e-mail clients communicate with a server. Microsoft provides a proprietary mail server (also called Exchange) but also provides support for industry-standard mail services. The most popular of these are the protocols used over the internet: POP and SMTP.

Sidebar: Where Art Thou, MAPI?
If Exchange or Outlook isn't set up as your default e-mail system, you'll need to find out whether the default e-mail client is MAPI-compliant before you can send mail. MAPI is one of Microsoft's standard interfaces for working with mail. I like MAPI because it's the basis of the multi e-mail account features of Outlook Express (I've yet to be convinced to go to Outlook 97/98). On the other hand, one of my clients uses Eudora, which is MAPI-compliant, and it works very well with the MAPI processes that I use in this article.

An easy way to tell whether your e-mail software is set up as a MAPI client is to go into Access 97, open a report in preview mode, right-click on the report, and click on the Send option, choosing the RTF format. Whatever e-mail program comes up is the one that's your current MAPI client. If you get a screen display that looks like Figure 1a, then your MAPI client is Exchange. If you get an error message, then you have no MAPI compliant e-mail client installed. If you think that I'm laboring this a little, it's because I was more than a little confused myself when I first started setting this software up.

If you wish to set up your normal e-mail service as your default MAPI client e-mail, check the Options menu in your e-mail system. This information might not be readily available: Outlook Express didn't even have a MAPI reference in the Help file, though MAPI support is one of the choices in Outlook Express's options.

UserName	UserCompany	UserCountry	UserE-mail	E-mailSent
Garry Robinson	GR-FX Pty Limited	Australia	access@gr-fx.com	Yes
M Derrick	AM Ltd	UK	derrek@parkview.com.uk	No
Shane Warner	Lucky Co	USA	tlwarner@luckyco.com	No
Terry L. Farmer	Millicent Pty Limited	USA	tfarmer@millicent.com	No