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. 

MAPI …  MAPI      Where Art Thou   MAPI

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.

 

 

Figure 2 Link 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. 

  << Click to enlarge

Figure 4 Lists 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.

 

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
Contents	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
Content Unread	Number (Long)	4

Listing 5 Lists 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.

 

Figure 6 A 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)

<input type="submit"  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.

Click here for html file

<html> <head>
<title>Software Download Form</title>
</head>

<body bgcolor="#00FFFF">
<p><small>This form is for downloading software.</p>

<p>Firstly you should fill in your details then click below to download. </small></p>

<form method="POST" ENCTYPE="text/plain" action="mailto:youremail@gr-fx.com">

  <pre>
Name     <input type="text" size="35" name="Username">

E-mail   <input type="text" size="35" name="UserEmail">

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> <h2></a></h2>
</body></html>

Listing 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. 

 

Received	Message Size	Contents
27/09/98 12:10:31 PM	678	Username=Terry L. Farmer UserEmail=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

Listing 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.

Private Sub cmdUserDetails_Click()

  ' Loop through the Exchange tables and decode the
' users web response into a useable Access table

Dim dbs As Database
Dim rstExchange As Recordset, rstUsers As Recordset
Dim UserEmail As Variant, postIt As Integer
Dim UserName As Variant, UserCompany As Variant
Dim UserCountry As Variant, UserComments As Variant
Dim AccessVersion As Variant, EmailDownload As Variant    

' Set the current database and define the 2 recordsets

Set dbs = CurrentDb
Set rstExchange = dbs.OpenRecordset("SoftwareDownloads")
Set rstUsers = dbs.OpenRecordset("SoftwareUsers", DB_OPEN_TABLE)

rstExchange.MoveFirst

Do Until rstExchange.EOF  ' Begin loop.

  '   Extract the users details from the Web Form email

     UserName = ExtractDetail(rstExchange!Contents, "userName=")

    UserEmail = ExtractDetail(rstExchange!Contents, "userEmail=")

    UserCompany = ExtractDetail(rstExchange!Contents, "userCompany=")

    UserCountry = ExtractDetail(rstExchange!Contents, "userCountry=")

    UserComments = ExtractDetail(rstExchange!Contents, "userComments=")

    If Len(UserEmail) > 0 And InStr(UserEmail, "@") Then

    '     Confirm that the entered detail is legitimate and post i

      postIt = MsgBox(UserName & " " & UserEmail & " " & 
       UserCompany & " " & UserCountry & " " & AccessVersion 
       & " " & UserComments, vbYesNoCancel, "Post The Following")

      If postIt = vbYes Then

        On Error Resume Next
        rstUsers.AddNew      ' Create new record.

        rstUsers("userName") = UserName

        rstUsers("userEmail") = UserEmail

        rstUsers("userCompany") = UserCompany

        rstUsers("userCountry") = UserCountry

        If Len(UserComments) > 0 Then

          rstUsers("userComments") = UserComments

        End If

        rstUsers.Update      ' Save changes.

        On Error GoTo errCmdUserDetails

      Else

         If postIt = vbCancel Then
           GoTo exitCmdUserDetails
         End If

      End If
   End If
   rstExchange.MoveNext  ' Locate next record.

  Loop              ' End of loop.

exitCmdUserDetails:

  rstExchange.Close     ' Close table.
  rstUsers.Close

  Set dbs = Nothing

  Exit Sub
errCmdUserDetails:

  Error Err.Description

  GoTo exitCmdUserDetails

End Sub

Listing  9 - Visual Basic Code To Decode Email From A Web Form To An Access Table

ExtractDetail  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.

Public Function ExtractDetail(textLine As Variant, FormItemReq As String) As Variant

' Web Form email text can be broken down by extracting the
' detail for a given form field as the text information
' between the required form item and the next carriage return

Dim StartLine As Variant, EndLine As Variant, 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 = " "
  end if

  ExtractDetail = ExtractText  

End Function  

Listing  10 - Visual Basic Code To Extract Web Form Field Data

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.

 

userName	userCompany	userCountry	UserEmail	EmailSent
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

Listing 11  The transferred table of user responses

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.

 

Figure 13 - The Auto Email form filled in with the standard subject, greeting and main message.

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
Dim EmailDownload As Variant

If Not IsNull(Me!TrialEmail) Then

' Just do a single trial email
  whereStr = " where userEmail = '" & Me!TrialEmail & "'"
  Me!TrialEmail = Null

Else

' Email the list one at a time
  whereStr = " where not emailSent "
End If

' Open the database object and select users names that havent been sent yet

Set dbs = CurrentDb
Set rstMail = dbs.OpenRecordset("select * from softwareUsers " & whereStr)

If rstMail.RecordCount = 0 Then GoTo exitCmdUserDetails

rstMail.MoveFirst

Do Until rstMail.EOF  ' Begin loop

    postIt = MsgBox(UserName & " " & rstMail!UserEmail & _
    " ...  " & rstMail!UserName & " ... " & _
    rstMail!UserCompany, vbYesNoCancel, _
    "Email The Following"

    If postIt = vbYes Then

'     Output the message as email.  Build a complete email message
'     from the user detail and the message on the output form
'     Place the user comments at the bottom so that you can refer
'     to them for that personal message !

       DoCmd.SendObject acSendNoObject, , acFormatTXT, _
       rstMail!UserEmail, , , Me![SubjectReq], _
       Me![GreetingReq] & "  " & rstMail!UserName & _
       Chr(10) & Chr(10) & Me![Instructions] & _
       Chr(10) & Chr(10) & rstMail!UserComments

'      Update the email sent box

       rstMail.Edit
       rstMail("EmailSent") = True
       rstMail.Update

    Else

exitCmdUserDetails:

  rstMail.Close

  Exit Sub

End Sub

Listing 14  - VB code used to generate multiple email messages from a Access table

Conclusion

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.

Author Bio.
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 specialisation is a science in itself, theseWeb/Email techniques are tempered by Garry's own field of expertise, MS Access.  Contact details  …  +61 2 9340 7789   Web  http://www.gr-fx.com/  

  Your Sample Database Is Called   "exchange_FX.zip"

Sample database requires Outlook as well as a modern version of Microsoft Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Written by Garry Robinson

Other related pages on the site
Processing E-Mail Orders using Outlook and Access

Taking Outlook and XML to Task in MS Access
Stop Those Annoying Outlook Warning Messages
IT Departments and Microsoft Access ~ Developers Have Their Say