From Access to Automated E-mail|
Microsoft Access supports Microsoft Outlook and Microsoft Exchange through the Link Manager. This article shows you how to set up a Web-based system to help automate your e-mail collection using Access 2000, 2002, 2003. Access can work with more than just relational data. In this article, I'm going to show you how to tie Access into Microsoft Outlook. The processes involved are a simple HTML-based form, your e-mail software, Microsoft Outlook, 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.
Technology Introduction and Resource Files
The processes involved are a simple html based form, your email software, Microsoft Outlook/Microsoft Exchange and Microsoft Access. Unless you develop for a tightly controlled in-house email setup, you will find that the solutions offered here may have to be customized for different sites as each will have their own mail server configuration. All that aside, the processes that you will follow will be similar and the Access software side will be reusable. In this article, I have shown all the examples using Outlook .
This document comes with a download file called Exchange_BE.zip This has an Access 2000 software database called mail-fx.mdb and a HTML document called SoftwareDownload.htm.
Where Art Thou
If you are not using Outlook or Exchange as your email system, click here for some old notes on this topic.
Linking To Exchange
The link to Exchange is started up in the usual manner that table links are started up. In this figure GrafDownloads is a Linked table that has already been established to Outlook. Choose the menu File ~ Get External Data ~ Link Tables. Now choose Outlook and you should see the option in Figure 1. Note: Figure 2 shows step 1 where the table container has been selected and I have Right Clicked to start Link Table.
Figure 1 - Outlook Linking Window.
to Exchange Step 1
Now you need to choose the folder where your messages are held. If you do not have the display that is shown in Figure 4 then you may have to start your machine again (as I did once). In this article I now go on to discuss the use of data in the Personal Folders.
Of Different Folders In The Exchange Mail and Address Book
At this stage you would then choose the folder that has the information that you want to look at in the Linker. There is one more screen after this where you give the folder a name.
The Email Software Database & Linked Tables
At this stage you will probably want to open the demonstration database. To ensure the demonstration would work without Exchange or Outlook, I ran a make table query to transfer the Exchange table to a normal Access table called SoftwareDownloads. This is an exact replica of the Exchange Linked table and will allow you to run the demonstration software without first setting up the Exchange links. Listing 5 shows the structure of a linked Exchange table. You will probably recognize most of these fields from your normal email program.
Of Fields In A Linked Outlook Table
NOTE: Once you have tested with the existing Access table.
Setting Up A Simple Web Form For Downloading Software
Now we get to the bit where we will aim to capture uniform data on a intranet or internet site. For this article, I have setup a very generic html web form that you can use to ask people to complete prior to downloading a file from your Web Site. If you are into programming web sites, you will wonder about why I am not using .Net or Perl or Java, the answer is because I am an Access technophobe and would like to keep my web site simple. Figure 6 shows this simple download form where you can optionally fill in your details before selecting the download button.
Simple Web Form For Downloading Software
Listing 7 shows the actual code behind the Web page. These are the things that you should look for in the HTML form.
<Form Method: This is where the web form starts. The fields that are listed in your resultant email are those that occur in this section of the HTML The end is denoted by </form>
Action="Mailto: This is the form action that will direct the browser to use the readers email service to direct the form entries to your email address. I would recommend that you devise a way to place the form response into a standalone email folder using your inbox assistant if you can. This will make management of the email a little easier. In this case I use a different email address from my normal one.
name="Username" The name tag is what appears in the email message that is generated by the user. You should use unique names so that you can decode the data later in Access (see Listing 9)
This is 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.
you should fill in your details then click below to download.
<input type="text" size="35"
<input type="text" size="35"
<input type="text" size="35"
did you find out about our software and what will you use it for ?<dl>
7 HTML For The Web Form In Figure 4
Decoding The Exchange Data
Now that a number of users have filled in the response form and the resultant email resides in the linked Exchange folder (called SoftwareDownloads), we need to decode the messages. For this exercise the only field that we are interested in is called Body (a memo field). This is shown in Listing 8.
8 The Body Field In
The Linked Exchange table called SoftwareDownloads
The Body field data is text that is
characterised by the Name tag from the report ( e.g. UserEmail= ) and each
separate HTML field response is concluded by a carriage return.
To decode this information into separate fields for use in a ordinary
Access application, open the form called
"FX_ExtractBody" in design mode.
This is a one button form that handles the transfer as shown in Listing
9. This code consists of the
input table and the output table which are both handled by recordsets.
Before the record is posted to the new table, you can manually confirm
that the user details are worth posting.
dbs As Database
Set the current database and define the 2 recordsets
dbs = CurrentDb
Until rstExchange.EOF ' Begin loop.
= ExtractDetail(rstExchange!Contents, "userEmail=")
= ExtractDetail(rstExchange!Contents, "userCompany=")
= ExtractDetail(rstExchange!Contents, "userCountry=")
= ExtractDetail(rstExchange!Contents, "userComments=")
If Len(UserEmail) > 0 And InStr(UserEmail,
postIt = MsgBox(UserName & "
" & UserEmail & " " &
postIt = vbYes Then
Len(UserComments) > 0 Then
' Save changes.
Error GoTo errCmdUserDetails
If postIt = vbCancel Then
' End of loop.
' Close table.
dbs = Nothing
shown in Listing 10 is a function that will
find a HTML form field value and return the text that has been entered between
the Equal sign and the next carriage return.
For a body text with UserCountry=USA,
FormItemReq = "Country" will return "USA" as a text string.
Function ExtractDetail(textLine As Variant, FormItemReq As String) As Variant
Web Form email text can be broken down by extracting the
StartLine As Variant, EndLine As Variant, ExtractText As Variant
StartLine = InStr(textLine, FormItemReq)
StartLine = StartLine + Len(FormItemReq)
Len(ExtractText) = 0 Then
ExtractDetail = ExtractText
The email data is transferred across to a new table called SoftwareUsers as shown in Listing 11. In this table, I have added a EmailSent field that keeps track of whether a reply email has been issued to the user plus UserEmail is set as a primary key field to avoid adding multiple entries for the same person.
Automating Email Replies To The Users
Now that a useable Access table has been established, it is time to auto generate email to send to the users. This section of the demonstration database can be used irrespective of whether you use Exchange or Outlook and is only reliant on you having a MAPI compliant email service. You will find the code thus illustrated in the form FX_MailCentre as shown in Figure 13. Features of this form include the optional selection of a trial email plus the ability to enter your own subject, greeting and standard message. When the email is generated, you can personalize the message prior to finally sending it.
All the important code for this form lies under the Email The List button. Look for the SendObject command which builds a email message complete with the entries that are found on the screen. The message is concatenated into a full message using the line feed character.
Private Sub emailList_Click()
' Loop through a email list generate messages one at a time
Dim dbs As Database, whereStr As String
Dim rstMail As Recordset, UserEmail As Variant
Dim postIt As Integer, UserName As Variant
Dim UserCompany As Variant, UserCountry As Variant
Dim UserComments As Variant,
AccessVersion As Variant
If Not IsNull(Me!TrialEmail) Then
' Just do a single trial email
' Email the list one at a time
' Open the database object and select users names that havent been sent yet
Set dbs = CurrentDb
If rstMail.RecordCount = 0 Then GoTo exitCmdUserDetails
Do Until rstMail.EOF
' Begin loop
postIt = MsgBox(UserName &
" " & rstMail!UserEmail & _
If postIt = vbYes Then
Output the message as email. Build
a complete email message
DoCmd.SendObject acSendNoObject, , acFormatTXT, _
' Update the email sent box
Whilst there may be more sophisticated methods of managing email, using the link table manager to integrate your email 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 VB to decode the email. Finally you can reply in a systematic manner to these responses by looping through the decoded data.
Other Exchange Ideas
Your email folders consist of messages with dates so why not consolidate your emails by week and report the frequency of email messages coming into your email server. Those suspicious ones amongst you might even consider writing a little bit of code to find all the Spam messages and delete them before you waste time reading them. The email messages linked through Access can be deleted and changed as though they were ordinary tables. Finally this article easily could have explored the use of the Exchange Address book and synchronizing that with your Access database.
Published 1999-03 by GR-FX from Sydney, NSW, Australia
Other related pages on the site