magine that you need to develop an application for a sales force that constantly queries your database to retrieve or update inventory information. In addition, imagine that the most widely used form of communication in your organization is e-mail. Under ordinary circumstances, you would design a Web interface using ASP and COM to query the database. However, while you're in the process of building the Web application, your sales force has no access to the data.
      In this article, we will show you a fast and easy way to provide this flexible access using e-mail. In the scenario we'll present here, a salesman on the road can query the database for information on furniture and other various items. You can limit users to the company's sales representatives, or you can include department buyers and other employees who need to view and edit data. The model used in this example is a simple company which sells many products. For each product, a price, cost, and quantity-on-hand are stored in the database. Figure 1 shows the definition for the simple table that holds this information.


Figure 1 Inventory Table

      The table, called tblInventory, has six columns. ID is an autonumber field and is used as the primary key. The Description, Department, Price, and Retail Cost columns are self-explanatory. Finally, Count is the number of items on hand. We will show you how to manipulate the contents of this table using e-mail so your users can have easy access to your inventory system.

Architecture

      Figure 2 provides an overview of the application architecture. There are six steps required for the salesman or the user to complete a transaction. In Step 1, the user sends the request via an e-mail client to a mailbox on your Microsoft® Exchange Server. The user's request is embedded inside the subject and body of the e-mail. In Step 2, the Microsoft Outlook® client receives the e-mail and by hooking the Application_NewMail event, an automatic event is fired using Visual Basic® for Applications (VBA), which processes the new e-mail.


Figure 2 Application Architecture

      Although we could perform all the work in VBA, we decided to encapsulate the rest of the functionality in a COM object. We will use VBA to initialize the COM object and finish processing the request. We refer to the VBA section that does this work as the main driver since this is the code that will get the process started.
      In Step 3, the main driver validates the user by implementing the IsUserValid method from within the COM object (see Figure 3). In step 4, the COM object evaluates the message and constructs the SQL statement from queries that have been stored in the database. In Step 5, the response message is constructed and results are passed to the e-mail sender. Finally, in Step 6, the e-mail sender sends the response back to the user. We will describe each of the six steps in detail.
      To implement all six steps we used Microsoft Exchange Server 5.5, Outlook 2000, VBA, COM, and Microsoft Access. We will describe how to configure Exchange and Outlook so your server can receive the e-mails sent by your users. We will also show you how Outlook and VBA are used to intercept each new message that contains the processing request.
      Finally, we will describe each method within the COM component that parses and translates the message into a SQL statement, returns the requested information from the database, embeds the data in an e-mail message, and sends the message to the person who made the request.

Configuration

      The first thing you'll need to do is configure and create a mailbox in Exchange Server. This is the e-mail address where your users will send all database queries. Your network administrator can set this up for you. Follow these steps to create a mailbox called rdmsdn@yourDomain.com:

1. In Exchange Administrator, Select File | New Mailbox (or Ctrl+M).
2. Enter the first and last name (see Figure 4). An alias will be created from the first initial plus the last name. (This alias can be changed to whatever you need.) This string will make up the e-mail address.
   
   
   Figure 4 Setting up an Account
   
   
3. Enter the display name (this name is shown in the contacts list and is used as the person's displayed name).
4. Select the Primary Windows NT® Account at the bottom of the dialog and select your account (or the name of the person who will be the primary user for the account).
5. To allow others to have permission to the mailbox, select the Permissions tab and click the Add button in the "Windows NT accounts with permissions" section. The default permission level is User, which should be adequate.
6. If you want the mailbox not to be visible in the Global Address list, select the Advanced tab and check "Hide from address book" (see Figure 5). Do this after the mailbox has been opened in the client's computer in Outlook or the user will not be able to connect to it.
   

Figure 5 Address Book Options

      Choosing the default settings will make this mailbox part of the yourDomain.com domain and the rest of the default settings should be adequate.
      In order for the messages to be processed, the Outlook client will have to be configured. To connect to a new mailbox in Outlook 2000, go to Tools | Services and highlight "Microsoft Exchange Services." Click on Properties and select the Advanced tab. Finally, click the Add button and enter the alias for the mailbox.

Implementing the Main Driver Macro

      Now we will show you how to implement the main driver, which is executed when new mail arrives. This is written in VBA code within the Outlook client. To implement the main driver from within your Outlook client, go to Tools | Macro | Visual Basic Editor. Next, go to the ThisOutlookSession module and paste in the main driver VBA code shown in Figure 6.
      In the VBA code that implements the main driver, the Outlook application object (shown in Figure 6) is ultimately used to access the Inbox. Each time a new e-mail message arrives in the RDMSDN mailbox (the alias used in our application scenario), the Application_NewMail event is run. If you have created a different alias in your Exchange Server, then you will have to modify the constant variable strMailBoxName that's shown in Figure 6.
      The main driver is based on three objects which help us work with Outlook and the folders within Outlook. These objects consist of types MailItem, NameSpace, and MAPIFolder. The Outlook object, which provides us with access to these objects, is an application object that is used when we need automation to control Outlook from another application. This object is available only when Outlook is running. However, the Outlook object can be created when Outlook is closed by calling CreateObject with the object name "Outlook.Application". Before we explain the details of the function Application_NewMail, we will describe the MailItem, MAPIFolder, and NameSpace objects.
      Objects of type MailItem contain all of the necessary methods and properties needed to work with e-mail messages. We will use this object to retrieve the subject, body, name of sender, and name of recipient. This object will also allow us to edit, create, and send e-mail messages.
      The MAPIFolder object provides access to the actual folder we are trying to work from—in this case, the Inbox. However the MAPIFolder can take on any form to give you access to Contacts, Calendar, Journal, Notes, and so on. We will use the MAPIFolder object to select the e-mail we are searching for because you can think of this object in the same way you think of the Inbox.
      The GetNamespace method can be used to return a reference to a set of Outlook folders. If you are using Outlook with an Exchange Server, the NameSpace object corresponds to Outlook's MAPI profile, which specifies the set of folders that are available in Outlook's folder list. This set of folders would typically be your Exchange mailbox, Exchange public folders, and any personal folders you may be using.
      The NameSpace object also gives us the ability to work with more than just the default mailbox. So we decided not to use the default mailbox, and instead we have implemented a solution that would demonstrate how to access other mailboxes including the RDMSDN mailbox.
      Once you have access to the NameSpace object, you can access any of the folders that are available. These are MAPIFolder objects, and each MAPIFolder object can contain other MAPIFolder objects or other types of items. In this case we are working with an Inbox and will be accessing e-mail messages, which are MailItem objects. To learn more about the Outlook objects we've mentioned, check the links provided at the beginning of this article.
      Now let's see how to put these objects to work. We begin by declaring the necessary variables:

  Dim msg As Outlook.MailItem
Dim strSubject As String
Dim fldInbox As Outlook.MAPIFolder
Dim gnspNameSpace As Outlook.Namespace
  

We must first set gnspNameSpace to a MAPI Namespace, and then we can specify which folder we would like to access.

  Set gnspNameSpace = Outlook.GetNamespace("MAPI")
  

      It is easy to see how we get a reference to the RDMSDN mailbox. In case you didn't notice, strMailBoxName was a constant declared at the very beginning of the VBA module with the value of RDMSDN. So we can access the Inbox by using the Folders collection and specifying top-level folder, in this case "Mailbox-RDMSDN," and then the Inbox folder.

  gnspNameSpace.Folders(strMailBoxName).Folders("Inbox").EntryID
  

to return a reference to the RDMSDN Inbox. We accomplished this in the VBA module with:

  Set fldInbox =  _
    gnspNameSpace.Folders(strMailBoxName).Folders("Inbox")
  

      Now that we have a reference to the Inbox, we can simply iterate through the messages it contains, inspecting each mail item to find out if the item should be processed. In an attempt to stick with a simple approach, we decided that every mail item that has not been read, has the keyword "QUERY" in the subject, and whose body length is greater then zero should be considered for processing. At this point we create an instance of the COM object, which in our case looks like this:

  Dim objFurniture As Object
Set objFurniture = CreateObject("Outlook.Class1")
  

      We will go over the details of the COM object (better known as objFurniture) shortly, but for now all you must know is that it offers two public functions: IsUserValid and ProcessEmail. IsUserValid takes SenderName as a parameter, and ProcessEmail takes msg as a parameter. Note that txtUserNameInOutlook, which you saw in Figure 3, is actually stored in tblUsers, shown here:

      The rest of the magic happens within the object. It is important to note that the COM object is not a requirement, but a preference. If you prefer to do so, all of the COM functionality can be placed within the VBA module. If you have trouble understanding the objects we are working with, we recommend that you familiarize yourself with the Outlook object model using the appropriate documentation from the MSDN® Library (Microsoft Office 2000 Developer Object Model Guide).

Data Access Component

      The data access component has three purposes:

• Provide validation for the user.
• Parse the e-mail body, construct the SQL statement, and retrieve results based upon the e-mail message.
• Send the e-mail message to the user.

      As you can see, these three purposes correspond to Steps 4, 5, and 6, respectively. You saw them in the process diagram in Figure 2. The data access component is the COM component that we have been referring to; it's implemented using Visual Basic 6.0. Since COM is language-independent, a similar component can be created using other languages such as C++.
      The component is called DataAccessComponent and has a single class module called clsEmailProcessor, whose interface provides two public methods: IsUserValid and ProcessEmail. Within the clsEmailProcessor we declare five constant variables and nine private functions that will be described in the following sections.

Constants

      First, let's look at the constants we used and the purpose they serve in querying the data source.

  Const strDBPath = "C:\MSDN\MSDN.mdb;"
Const strActiveConnection = "PROVIDER=MSDASQL;" & _
        "DRIVER={Microsoft Access Driver (*.mdb)};" & _
        "DBQ=" & strDBPath &  "UID=admin;PWD=;"
Const strQueryNameMarker = "Query Name:"
Const strParamBeginMarker = "Parameter Begin:"
Const strParamEndMarker = "Parameter End:"
  

      The strDBPath constant stores the path where the sample .mdb file is located. As is true in the rest of the project, the database is very simple. It contains four tables: tblInventory, tblQueries, tblUsers, and tblQueryType, all of which will be discussed later. Notice that this variable is a hardcoded disk path; it should be modified to point to the path of the database you are going to use.
      The constant strActiveConnection is the database active connection string. If you're using Microsoft Access, the value that we have provided should suffice.
      The next three constants help define the layout of the e-mail message with respect to the body. StrQueryNameMarker defines the line before the query that the user is requesting. We can search the query for the value of StrQueryNameMarker, and once we find it we will know that the line following it will contain the name of the query that the user would like to execute. In our case, the constant strQueryNameMarker is "Query Name."
      The StrParamBeginMarker and strParamEndMarker constants work together to mark the beginning and ending of the parameters needed to execute the query. If you count the lines between these two markers, you will know exactly how many parameters the user has given you, and you can later compare that to the number of parameters the query expects.
      We will finish discussing the message format a little later in the article, but hopefully you get the gist of it. Now let's discuss all the constant variables used in the component.

Functions

      ExecuteSQL is the only function to connect directly to the database. Inside this function, we access the database, execute the SQL string provided by the caller, and return the results (if there are any). It is a very straightforward function and needs little explanation. We create an ADO connection like so:

  Set cnx1 = New ADODB.Connection 
  

Then we create an ADO recordset.

  Set rst1 = New ADODB.Recordset
  

Next, we open the connection using the strActiveConnection.

  cnx1.Open strActiveConnection  'Set connection string
  

      After the connection is open, we open the recordset using the connection and the SQL.

  rst1.Open strSQL,cnx1,adOpenStatic,adLockReadOnly 'Open Recordset
  

Finally, we return the results of opening the recordset.

  Set ExecuteSQL = rst1
  

If you have spent any time at all working with ADO, this should be easy for you.
      IsUserValid was the first function we called from the VBA module and is the function we use to ensure that the e-mail sender is a user of the system. Like everything else so far, it is very simple and straightforward. The only thing about this function we will discuss is the actual query string.

  StrSQL = "Select * From tblUsers Where txtUserNameInOutlook = '"
& eMailAddress & "';"
  

      In this case, we expect to receive the sender's name as it appears in the FROM section of the e-mail. For example, if the sender's name is Bob Smith and he sends an e-mail where the FROM section contains Bsmith@hotmail.com, then his txtUserNameInOutlook must be Bsmith@hotmail.com.
      However, if he sends an e-mail where the FROM section looks like Bob Smith <Bsmith@hotmail.com>, then his txtUserNameInOutlook must be Bob Smith. This just allows the user to have a client that can map the sender to the right name in the database regardless of whether it fills in your name for you in the function.
      ProcessEmail is the brains of the entire application (see Figure 7). This function only has one parameter, and that is a reference to the msg MailItem object that we mentioned earlier.
      When we process the e-mail, we break the message down into two parts. One part is the Subject and the other is the Body. To be processed, the Subject must contain the word "Query" and the message body must be formatted in the correct way. When we talk about the format, we are referring to the layout of the e-mail and the location in the e-mail where the name of the request and the parameters for that request can be found. Since the name of the request is basically the name of a query, we decided that the name of the request would be found directly after the constant strQueryNameMarker, which in our case is "Query Name:".
      The parameters for the request will be located between the strQueryBeginMarker and the strQueryEndMarker. In our case, these values are "Begin Parameter:" and "End Parameter:". The following is an example of how the different e-mail parts will look in any given e-mail.

  Email Address: RDMSDN@YourDomain.COM

Subject: QUERY

Email Body: (each entry must be in separate line) 
Query Name:
NameOfTheQuery (must be same as txtQueryName in tblQureries)

Begin Parameter:
Value1
Value2
•••
End Parameter:
  

      Processing the e-mail involves four major tasks: parsing the e-mail, constructing the SQL (request) statement, executing the SQL statements, and sending the e-mail back to the user.
      In the next sections we will take a look at each of these four steps and identify all of the private functions within the COM object.

Parsing the E-mail

      To parse the e-mail, we need to split the e-mail into an array, get the query name, query parameters, and query type.
      The SplitEmailBody function splits the e-mail into an array (see Figure 8). This function receives the e-mail body as a parameter and returns an array where each element of the array is a line for the e-mail. If a query takes three parameters, then this array might look like the code sample shown here:

  SplitEmailBody(1) = "Query Name:"
SplitEmailBody(2) = "NameOfTheQuery"
SplitEmailBody(3) = "Begin Parameter:"
SplitEmailBody(4) = "Value1"
SplitEmailBody(5) = "Value2"
SplitEmailBody(6) = "Value3"
SplitEmailBody(7) = "End Parameter:"
  

      The SplitEmailBody array will look like the following if the user wants to get a listing of all available valid queries stored in the database.

  SplitEmailBody(1) = "Query Name:"
SplitEmailBody(2) = "HELP"
  

      Through the resulting array, arrEmailBody, we will access each element of the request. Once the e-mail body is transferred into the arrays, the array is used to retrieve the query name, query parameters, and query type. These next three steps involve worker functions that prepare the data such that it will allow us to construct the SQL statement from the query stored in the database. These functions will let us construct the SQL statements so we can execute them easily.
      Next, we need to get the query name. We can extract the name of the e-mail from the array we created by splitting the e-mail. This is done inside the function ReturnQueryName that receives an array of strings and returns a single string, which is the query name. Basically this function will search each item in the array until it gets to the line that matches strQueryNameMarker. Once the marker is found, this function will return the following line of the array. If this line does not exist or it is blank then the function will return a -1, indicating that an error has occurred.
      To get the query parameters, the function ReturnQueryParams works in the same way that the ReturnQueryName function works, except that this function has to return an array of values since there can be anywhere from zero to x number of parameters.
      Instead of searching for the strQueryNameMarker, this function searches for the strQueryBeginMarker and strQueryEndMarker in order to find the beginning and the end of the parameter section. Once it finds the beginning of the section it will start storing the values of the body one-by-one into an array.
      Finally, to get the query type, the ReturnQueryType function validates that the query requested is of type SELECT, INSERT, DELETE, or UPDATE. This is done by cross-referencing the intQueryType field in the tblQueries and the intQueryType field in the tblQueryType. The reason we need to know the query type is to better respond to the user. For example, the response for the SELECT statement will be different than the response for UPDATE.

Constructing the SQL

       The next step in the process is to construct the SQL statement based upon the query name and parameters provided in the body of the e-mail. Note that we have already parsed the e-mail into an array. We know the query name that the user wants to execute. We also know all the parameters that the query requires. The variable that holds the query name is strQueryName, and additional parameters are stored in arrQryParams. To construct the SQL statement from these parameters, we call the ConstructSQL function shown in Figure 9.
      Essentially, we retrieve the query based upon the query name from the tblQueries, as shown in Figure 10. Once the query is retrieved, the "?" in the query text is replaced by the parameters stored in the arrParam. In case of an error, the function returns -1. Thus the query is now a functional SQL statement and is ready to be executed.
      Let's see how this e-mail example will be executed:

  Email Address: RDMSDN@YourDomain.COM

Subject: QUERY

Email Body: 
Query Name:
Select_Inventory_Department
Begin Parameter:
3
End Parameter:
  

The variable strQueryName will have the value of "Select_Inventory_Department." The array arrQryParams will have an index with the value, arrQryParams(1) = 3. When ConstructSQL is called with the previously mentioned parameters, it returns the following string:

  ConstructSQL = Select * From tblInventory Where txtDepartment='3'
  

If the ConstructSQL function does not return an error, then the following three functions are called to finish the transaction:

  Set rstResult = ExecuteSQL(strQry)          
Call FormatEMailResponse(strQryName,intQueryType,rstResult,eMail)
eMail.Send  
  

Executing the SQL Request

      StrQry is the SQL statement that was returned by the ConstructSQL function. The ExecuteSQL function uses ADO to connect to the database using the constant variable strActiveConnection. It returns an ADO recordset as a variant because while SELECT returns a recordset, UPDATE, DELETE, and INSERT do not. The results of ExecuteSQL functions are passed to the rstResult variable. Before sending the e-mail to the user with a response, the e-mail is formatted using the subroutine FormatEmailResponse. This routine appends the string "*PROCESSED*" to the original subject, indicating that the query has been processed correctly.

Formats and Send Response

      As you can imagine, the fact that SQL SELECT statements return data to users makes it important to handle them differently. In order to implement this distinction, we make use of the intQueryType variable described earlier. The SELECT query's results are formatted in the FormatSelectResponse subroutine. This routine iterates through the rstResult recordset and converts its content into HTML.
      In addition to the HTML response, we also send the result as an attachment. This attachment is a tab-separated text file and its name is the same as the query plus a random number with a .txt extension. If the recordset contains more than 50 rows, we do not include the HTML in the message body, but the .txt file is always attached. The email.Save method is used to save changes made to the message body and the subject.
      Finally, the email.Send command allows us to send the message back to the user, thus completing the whole cycle.

Sample E-mails and Results

      Here are a few examples of what an e-mail will look like and the results you can expect. In Figure 11 and Figure 12 we send a request for the inventory item where the ID is 3.


Figure 11 Request Item 3


Figure 12 E-mail Query

Figure 13 and Figure 14 show examples of the results.


Figure 13 Results


Figure 14 Results of Help Query

Using the Sample Application

      Now that you know the nuts and bolts of the application architecture, use the following seven steps to configure our sample application for your own use:

1. Create a mailbox on your Exchange Server.
2. On an application server, configure the Outlook client as described in the Configuration section.
3. Copy the VBA main driver code and change the constant variable strMailBoxName to the alias you created in Step 1.
4. Copy the database (MSDN.MDB) into a directory (C:\MSDN\) on your application server.
5. Change the variable const strDBPath to C:\MSDN\MSDN.MDB.
6. Compile the DataAccessComponent.DLL.
7. Send a query e-mail to the mailbox that you created in Step 1.

      You should certainly feel free to explore the sample code and change it according to your needs. We have used a simple data model and simple query statements. However, you can use a complex data model along with complex SQL statements and the application/concept will still work as long as all e-mail messages are in the format that we described earlier in the article. You can even scale this concept for use with a SQL Server™ database.