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 Toolbox, you will receive that article as well.

Garry Robinson                         

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 Regnow.com (Sample also for Digibuy.com), a third party secure ordering web site with many thousands of software developers as their clients.  Whilst this article is specific to Regnow (and 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 2000 or Outlook 2002 on your computer (and you will need to own The Toolshed).  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 9.0 or 10.0 Object Library
Microsoft DAO 3.51 Object Library 

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

' Send an email to yourself so that you can try out the rest of the software
' The following code reads a text file and uses that for the body of the email
' It then sends an email as an Outlook MailItem

      
Dim appOL As Outlook.Application 
Dim testEmail As Outlook.MailItem 
      
' Instantiate the Outlook and Mail Item objects 
' You will need a reference to Outlook 9, 10 or 11 libraries. 
On Error Resume Next 
Set appOL = Outlook.Application 
Select Case Err.Number 
  Case 0 
    'Everthing should be OK 
  Case OUTLOOKNOTOPEN 
    MsgBox "Outlook is not open, it is being opened now", vbCritical, "Please start again" 
    'Note: If you have more than one version of Office on your computer ' this could start up the wrong version of Outlook 
     Shell "outlook", vbNormalNoFocus 
     GoTo Exit_Routine 
   Case Else GoTo Error_exit 
  End Select 

Set testEmail = appOL.CreateItem(olMailItem) 
testEmail.Subject = "Please fill in 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.
' 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"
      
 


        

      


        Is Your Database Corrupt ? Need to know more about how to save 
        your database if something major goes wrong

        Click here to read more
      
    
      



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.
      

      

      

      
  
      Your Sample Database Is Called   "regnowoutlookorders.zip"
      
Sample database is suited to all versions of Access

      

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

      

 

      Click here to read the same article 
      for SWReg  
      




      


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


Other Pages at VB123.com You Might Like To Read
Stop Those Annoying Outlook 
Warning Messages 

Automate Your Email Using 
Access and Exchange/Outlook

Taking Outlook and XML to 
Task in MS Access 

Remote Queries In 
Microsoft Access 

Pages Outside VB123.com
      

      


Send Email Without A Security Warning in Outlook








 





Click on the following button 

to jump to the next page in the document loop.