Next Tip  Processing E-Mail Orders using Outlook and Access


(Specifically written for the SWREG orders format):

One of the most important components of running a web site is the ability to take orders.  Anyone who has successfully completed a secure website ordering system has my full admiration.  It is not easy to achieve.  If you’re then had a large number of customers who have used that ordering system successfully, you and your team are geniuses.  For the rest of the web sites in the world the safest and easiest way to add an ordering system is to use one from a reputable third party. 

This article shows how to read an order that arrives as an e-mail, store that information in your database, prepare an e-mail to inform your new customers and then move your e-mail to another folder on completion.  The example that we have presented is designed for orders from DigiBuy.com, a third party secure ordering web site with many thousands of software developers as their clients.  Whilst this article is specific to DigiBuy, the visual basic code illustrated could be applied to any web form or software system that produces text (e-mail) in a consistent format. 

 The Download Database  

If you wish to try this Outlook demonstration, you will need a copy of either Outlook 2002+ on your computer.  The download database is in Access 2000 format.  The database will require you to setup two folders in Outlook, one for storing the incoming message and one for moving the message to when it is processed.  The table fields used to store the customer details in will definitely require modifications to suit your system.

 

The download database requires external references as follows

Microsoft Outlook 10.0 or above Object Library

Microsoft DAO 3.6 Object Library

 

If you have Microsoft Outlook 97, you must program in VB script rather than vb.  This is not covered in this article.  Beware though if you upgrade to Outlook 2002 or 2000 version 2 that some email automation from mail merge will not work as it used to.

Click here for downloads

Coping With The Latest Outlook Security Features



If Outlook security popup messages makes this software a little cumbersome, read the following article

Stop Those Annoying Outlook Warning Messages

 

Preparing An Email

 

When we first thought about writing this article, we wandered how the readers could get a sample e-mail order to process with the demonstration software.  We solved this by building a sample order in text and then allowing you to e-mail it to yourselves.  To do this, we first create an instance of Outlook and then create a new Outlook e-mail as follows. 

 

Dim appOL As Outlook.Application

Dim testEmail As Outlook.MailItem

 

Set appOL = Outlook.Application

Set testEmail = appOL.CreateItem(olMailItem)

testEmail.Subject = "Add your own email address"

 

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.

 

testEmail.Body = TextFileToString_FX( _
  GetDBPath_FX & "MyFirstOrder.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 SWREG.org

 

Figure 1 – A sample 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"

 

Is Your Database Secure ? Need to know more about how to protect your database investment. Confused about Access security. Then have a look at the book that Garry is writing for Apress.
Click here for book link

 

Processing The Order

 

To process the orders, were 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 customers 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
UserEmail = ExtractToCR_FX(emailContents, "  Email Address: ")
UserName = ExtractToCR_FX(emailContents, "          First: ") & " " _
 & ExtractToCR_FX(emailContents, "           Last: ")
UnitPrice = ExtractToCR_FX(emailContents, "          Total: ")
strOrderNumber = ExtractToCR_FX(emailContents, "   Order Number: ")

 

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 & ": " & UnitPrice, vbYesNoCancel, "Post The Following")

If postIt = vbYes Then

       

  On Error Resume Next

  rstSoftOrders.AddNew     

 rstSoftOrders("Person") = UserName
rstSoftOrders("SeqNumber") = 1 ' ExtractToCR_FX(emailContents, "Seq. Number: ")
rstSoftOrders("Email") = UserEmail
rstSoftOrders("Company") = ExtractToCR_FX(emailContents, " Company Name: ")
rstSoftOrders("graf-fxVersion") = UnitPrice
rstSoftOrders("OrderNumber") = strOrderNumber

If Len(rstSoftOrders("Company")) < 2 Then
rstSoftOrders("Company") = UserName
End If

.... thru to

 rstSoftOrders("Notes") = ExtractToCR_FX(emailContents, " Question: ")

 

 

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.    

 

 UserAmountPaid = ExtractToCR_FX(emailContents, " Total: US$")
 rstSoftOrders("AmountPaid") = UserAmountPaid
       

  On Error GoTo getOrdersDetails_error

  rstSoftOrders.Update     

  On Error Resume Next

       

  DoCmd.SendObject acSendNoObject, , acFormatTXT, _

   UserEmail, , , "The Toolbox from GR-FX", _

   "Greetings " & UserName & "," & vbCrLf & vbCrLf _

   & TextFileToString_FX(GetDBPath_FX & "SA News.txt")

       

Finally remove the Outlook e-mail item that were 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 & " " & _

    UnitPrice & " .. " & 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

 

 

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, Scott and 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 9340 7789  
 
 

By Garry Robinson

  Your SWReg ready sample database is called   "outlookorders.zip"

Sample databases are suited to all versions of Access and Outlook

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

 

Other Pages at VB123.com You Might Like To Read

Stop Those Annoying Outlook Warning Messages

Automate Your Email Using Access and Exchange/Outlook
Access 2002 / XP DAO Errors Numbers And Descriptions

Pages Outside VB123.com

Send Email Without A Security Warning in Outlook

 

Click on the following button Next Tip to jump to the next page in the document loop.

Keywords:
Transaction Identification, Date, RegNow OrderID, Gift Information, Gift, Pickup, Product Information, Item #, Product Name, Quantity, Tax, Total, Purchaser Information, User ID,  Email Address, Shipping Info , First Name, Last Name, Company, Address1, Address2, City, State/Province, Zip/Postal Code, Country, Phone, Email, Referrer, Custom Referrer, Link Location,
 

Our Tools and Resources

  • RSS & Newsletter Here
    Join our newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
  • Get Good Help
    If you need help with a database, our Australian Professionals could be the answer

  • Smart Access is online 
    The best magazine written about Microsoft Access is now being transferred to the web. There are 400 articles written by a 100 authors in the collection.  Purchase Smart Access

  • The Workbench
    Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, creation versions,  shutdown database

  • Convert Access to SQL Server  
    Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query  translation and web form conversion.
  • Purchase the Popular FMS Products  
    If you purchase the Popular FMS products from us, you will receive a complimentary of Smart Access Gold, Silver or Bronze Collections [Your choice]

 

 

vb123 Professionals


Get Good Help Here

If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions



Frontpage Conversions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.


About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia

Access 2003 Security

MS Access Security

Read More here