Processing E-Mail Orders using Outlook and Access
(Specifically written for the Regnow orders format): Note that the downloads also includes a SWREG order sample and if you purchase the Toolshed, you will receive the Digibuy article as well.

We generate a test order through a function that reads a complete text file.  As in many of my previous articles I use the getDbPath function that I first read about in the free Smart Access newsletter.  This gives us the relative path of the demonstration data base which is where we store the text file with the email text body.

' Now open a text file and reads its contents for inclusion in the email
testEmail.Body = TextFileToString_FX( _
GetDBPath_FX & "MyRegNowOrder.txt")

Finally we display the email so you can amend details and add your email address.   Then finally we clear the reference to the objects as we no longer need them.  If you want to test the system with multiple orders, please change the order number in the e-mail before you send it. 

testEmail.Display

Set testEmail = Nothing

Set appOL = Nothing

Figure one below shows the sample order that is the same in structure as an order email that you would receive from Regnow.com. 

  << Click to see picture

Figure 1 – A sample Regnow email order is generated for processing in the database

Importing A Text File

To make the body of the e-mail message, we’ve found the best way is to store the static information in text files.  This seems to work well because it allows you to process the order even if you do not have access to Outlook and the Access data base.  The general function that follows can be used in any visual basic program.  It works by reading every line of the text file into a long string.  When the end of the line is encountered the carriage return and line feed characters are added to the string. 

Function TextFileToString_FX(fileName As String) _

  As String

Dim stemp, linesfromfile, nextline As String

Dim iFIle As Integer

  TextFileToString_FX = ""

  On Error GoTo error_TextFileToString_FX

  iFIle = FreeFile

  Open fileName For Input As iFIle

  While Not EOF(1)

    Line Input #1, nextline

    linesfromfile = linesfromfile + nextline _

      + Chr(13) + Chr(10)  Wend

  Close iFIle

  TextFileToString_FX = linesfromfile

exit_TextFileToString_FX:

  Exit Function

error_TextFileToString_FX:

  MsgBox "Error opening file  " & fileName _

   & " with " & Err.Description, vbCritical, _

    "Error Number " & Err.Number 

End Function

If your order comprises of multiple parts , it is easy to combine the order e-mail using different text files that are appropriate to the individual parts of the order. 

Using The Inbox Rules To Direct To Folders

To start the process of reading the order emails,  the first step is to move the orders from your inbox into the Orders folder.  We use the inbox rules wizard in Outlook for moving the emails when they are downloaded from our ISP.  To find out how to do this in Outlook , searched the help for “rules wizard“. 

Note that we have two constants to represent the names of the Outlook folders.  You will need to change these for your own folder names.  Do not use sub folders as the code for this in Outlook is quite tricky.  The OrderTable and TipsList constants are used for storing the customers order details and the e-mail address for a newsletter respectively. 

Const OrdersInFolder = "_ORDERS"

Const OrdersDoneFolder = "_Orders Processing"

Const OrderTable = "SoftwareOrders"

Const tipsList = "TipsMailList"

Processing The Order

To process the orders, we’re going to open all the emails that are in the Orders Folder in Outlook.  We will then read the text in the body of the e-mail.  First though we need to instantiate Outlook and the two Outlook folders that we will be processing. 

Set dbs = CurrentDb

Set myolApp = CreateObject("Outlook.Application")

Set myNameSpace = myolApp.GetNamespace("MAPI")

Set myfolder = myNameSpace.Folders( _

  "Personal Folders").Folders(OrdersInFolder)

Set myNewfolder = myNameSpace.Folders( _

  "Personal Folders").Folders(OrdersDoneFolder)

Now we work through those Order e-mails and process them one at a time.  We also need to open the table where we store the new customer’s order details.  

iMax = myfolder.Items.Count

If iMax = 0 Then

  MsgBox "Unfortunately there are no orders"

Else

  Set rstSoftOrders = dbs.OpenRecordset( _

    "SoftwareOrders", DB_OPEN_DYNASET)

  For iOrd = 1 To iMax

As we have to move the email message after it is processed, we always refer to item one in the order folder list.  This works because the items list is amended after the Outlook Items move method. 

Set myItem = myfolder.Items(1) 

Now we need to save the text of the order e-mail to a string variable called EmailContents.  Outlook provides this through the Items property Body.  If you look at figure one, you will find the water is broken into lines with the subject followed by a colon and a number of spaces.  As these are always the same in every e-mail, we pass the body text to a function that extracts all the remaining text after the subject and the spaces.  We explain this routine later on.

emailContents = myItem.Body
UserName = ExtractToCR_FX(emailContents, "First Name: ") & " " & _
ExtractToCR_FX(emailContents, "Last Name: ")

ProductPurchased = ExtractToCR_FX(emailContents, "Product Name: ") & " " & _
ExtractToCR_FX(emailContents, "Total: ")          ")

Now we ask the user of the software if they wish to proceed with the order.  We then extract all the other fields in the e-mail body that we are going to store in access.  A portion of this code is shown below. 

postIt = MsgBox(UserName & ": " & ProductPurchased, vbYesNoCancel, "Post The Following")
If postIt = vbYes Then

On Error Resume Next
rstSoftOrders.AddNew ' Create new record.
rstSoftOrders("UserID") = ExtractToCR_FX(emailContents, "User ID: ")

rstSoftOrders("FirstName") = ExtractToCR_FX(emailContents, "First Name: ")
rstSoftOrders("LastName") = ExtractToCR_FX(emailContents, "Last Name: ")
rstSoftOrders("Address1") = ExtractToCR_FX(emailContents, "Address1: ")
rstSoftOrders("Address2") = ExtractToCR_FX(emailContents, "Address2: ")
rstSoftOrders("City") = ExtractToCR_FX(emailContents, "City: ")
rstSoftOrders("State") = ExtractToCR_FX(emailContents, "State/Province: ")

' Etc ete etc for all the fields in the order email

Finally as the customer can order different items with different prices you will need to produce the emails that are based on the order.   This will differ with every system.  Tot send the email,  we have used the older send object method to illustrate the other way to generate e-mail from Microsoft Access.    

On Error GoTo getOrdersDetails_error
rstSoftOrders.Update ' Save changes.
On Error Resume Next

If ProductPurchased = PRODUCT1FOREMAIL Then

' User has purchased the Toolbox
DoCmd.SendObject acSendNoObject, , acFormatTXT, UserEmail, , , ProductPurchased & " from GR-FX", _
"Greetings " & UserName & "," & vbCrLf & vbCrLf & TextFileToString_FX(GetDBPath_FX & "vb123 News.txt")

End If

Finally remove the Outlook e-mail item that we’re processing to the orders processed folder .  It is safer to do this in code than to manually move the email using drag and drop.  We can now process the next order. 

myItem.Move myNewfolder
MsgBox "Our Order for " & UserName & " " & ProductPurchased & " .. " & UserEmail & " >> has been moved to " & myNewfolder.Name

In addition to storing the order in a table, we add the user’s e-mail address and name to separate table so that they can receive emails about things that relate to their purchase. 

DoCmd.RunSQL "insert into " & tipsList & _

 " values ('" & UserName & "','" & UserEmail & "')"

Processing The Email Body

One important part of this software is the function that returns data from the e-mail body for a particular line of text in that e-mail.   This line is found by identifying a string constant.  All text after that constant to the next carriage return is returned by the function

For an example of how this works, look at Figure 1 which will become a very long text string produced from the Body of the email.  To extract the text for the Author ID, we would write the following

MyTextStr = ExtractToCR_FX(webstring, "Author ID:  ")

This will return “9999999” into the MyTextStr variable.  

The code for this function is as follows

Public Function ExtractToCR_FX(textLine As Variant, _

 FormItemReq As String) As String

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

End If

ExtractToCR_FX = ExtractText

End Function

NOTE: In the download database, the above function is expanded to cope with the duplicated field identifiers in the Shipping section of the Regnow order email.

Summing Up

Using Microsoft Access and Outlook  together can reduce manual processing of Ordering emails very substantially.  I know this because sometimes it would take up to 15 minutes to undertake all the little steps of saving customer details into tables and newsletter lists.   Also without software, it was very difficult to explain to other staff members what to do when an e-mail arrived.   Now we can process the orders in a couple of minutes when Outlook email arrives in the correct folder.    As an added bonus, I can now demonstrate to our clients that we can program the very popular Microsoft Outlook.

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies web site at  http://www.vb123.com/.   The web site features Access Source Code tools and resources..  When Garry is not sitting at a keyboard, he can be found viewing the Outlook from one of Sydney’s seaside cafes.   Contact details  … Click Here   +61 2 9665 2871  
 
By Garry Robinson

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals