|
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.Com, the visual basic code illustrated could be applied to any
web form or software system that produces text (e-mail) in a consistent
format. If you wish to try this Outlook demonstration, you will need a copy of either Outlook 2000 or 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 9.0 or 10.0 Object Library Microsoft DAO 3.51 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. 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( _
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 Digibuy.com.
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"
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, _ "Customer Name: ") UnitPrice = ExtractToCR_FX(emailContents, _ "Unit Price: ")
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("OrderNumber") = ExtractToCR_FX( _ emailContents, "Order Number: ") rstSoftOrders("SeqNumber") = ExtractToCR_FX( _ emailContents, "Seq. Number: ") UserEmail = ExtractToCR_FX(emailContents, _ "Email Address: ") rstSoftOrders("Email") = UserEmail rstSoftOrders("City") = ExtractToCR_FX( _
emailContents,
"City: ") emailContents, "State: ")
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 Toolshed from GR-FX", _ "Greetings " & UserName & "," & vbCrLf & vbCrLf _ & TextFileToString_FX(GetDBPath_FX & "SA News.txt")
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 & " " & _ 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.
Useful Further Reading and Resources Smart Access magazine Articles Of Relevance Access to Automated Email - March, 1999 Taking Outlook and XML to Task – July, 2002 Get Access Into Outlook – October, 2000 Jet 4 Exchange/Outlook ISAM – August, 2000
Author
Bio. By Garry Robinson and Scott McManus
|
