Enhancing an Application with Windows Desktop Search and Office Outlook Integration
Sales Support: A Hybrid Visual Basic 6.0 & Visual Basic 2005 Application, Part 1
Summary: This article, the first in a four-part series, shows that it is not necessary to migrate a Microsoft Visual Basic 6.0 application to Microsoft Visual Basic 2005 in order to take advantage of .NET functionality. (15 printed pages)
Click here to download the application installer.
Click here to download the application source code.
Business and other organizations have written billions of lines of code in Microsoft Visual Basic 6.0 since its introduction in 1998. Considering how expensive and time-consuming it can be to upgrade important—even mission-critical—Visual Basic 6.0 applications to Microsoft Visual Basic 2005, it is extremely common for organizations to continue maintaining and even enhancing existing Visual Basic 6.0 applications.
Many developers and business have the impression that porting an application to the Microsoft .NET Framework is a prerequisite to using the .NET Framework for application enhancements. This is not the case. In fact, you can extend existing Visual Basic 6.0 applications by building new components in Visual Basic 2005. There is no need to port the entire application to Visual Basic 2005.
This series of articles covers different ways to extend an existing Visual Basic 6.0 application—in this case, a sample sales-force automation application—by using Visual Basic 2005 and the .NET Framework.
"Sales Support" is a very simple sales-force support application that allows the user to look up accounts, contacts, and forecasted sales. The application is not intended to be a complete sales-force automation solution. Instead, it is designed to be a representative placeholder for an existing line-of-business Visual Basic 6.0 application. Also, it serves as the anchor for a number of logical application enhancements that are best implemented by using Visual Basic 2005.
The Sales Support installer (http://download.microsoft.com/download/A/5/6/A56DD645-14AF-4CC2-BEC6-EEBE5B284167/installer.03.zip) has been tested on Microsoft Windows XP SP2 and on Windows Vista, and likely would work on older operating systems, too. Because this is a hybrid Visual Basic 6.0 and Visual Basic 2005 application, the Sales Support installer will install the.NET Framework 2.0 as a prerequisite. This application also stores data in a back-end database. For this reason, the installer will install Microsoft SQL Server 2005 Express Edition, which replaces Microsoft SQL Server 2000 Desktop Engine (MSDE 2000).
If you do not have these prerequisites installed, the installer will download them for you automatically. If they are already installed, the installer will detect their presence and skip to the application installation.
The installation of prerequisites is something that Microsoft Visual Studio 2005 greatly simplifies. You can just select application prerequisites, as shown in Figure 1.
Figure 1. Making application prerequisites with Visual Studio 2005
When you create an installer for a hybrid application, the best strategy is to create a new Visual Studio 2005 Setup Project, which will install any prerequisites, such as the .NET Framework, and any Visual Basic 2005 extensions for your application. This installer will launch the existing installer for your Visual Basic 6.0 application, too, after the prerequisites and Visual Basic 2005 extensions are in place.
You continue to build your Visual Basic 6.0 installer as you always have: by using the Visual Studio 6.0 Package and Deployment wizard, or any of the excellent third-party deployment products. The result for users is that they need only launch a single installer to have your application—and everything on which it depends—be installed.
After the application is installed, you can launch it from the Start menu. Look for the Sales Support8_3 entry. The application will initially prompt you to log on. To simplify use, the user name and password fields default to working values, as shown in Figure 2. You can just click the Login button to continue.
Figure 2. The Sales Support Login form
After you have logged on, the main application window appears, as shown in Figure 3. The application provides three different views of the sales information. The Accounts tab provides the first view and shows each account and associated sales information. This view can be sorted differently by clicking on a column heading. The Contacts tab provides the second view and shows more detailed information about each individual contact. The Forecast tab provides the third view and displays future sales-forecast information.
Figure 3. The main application window
Again, the Sales Support application is not designed to be a working sales-force solution. Instead, it serves only as a basis for realistic application enhancements.
Sales-force applications must track the flow of communications to the client. A logical extension for the Sales Support application would be better integration with e-mail. It would be especially useful to be able to retrieve all of the communication to and from a particular contact, and to do so from within the application itself.
Microsoft provides this base functionality through the free Microsoft Windows Desktop Search (WDS). WDS typically runs as its own separate application; however, for the best user experience, it would be better to surface the WDS functionality directly as part of the Sales Support user interface.
The first extension to Sales Support does exactly that. It includes a new form, implemented in Visual Basic 2005 that retrieves all e-mail messages to and from a client by using WDS. The client database contains a set of fictitious e-mail addresses. To use the WDS functionality, it's recommended that you edit one of the clients to contain an e-mail address with which you have communicated.
To configure a real e-mail address:
Run the Sales Support application.
Click the Contacts tab. You will see contacts listed, as shown in Figure 4.
Figure 4. The Contacts tab
Double-click the name of a contact, to edit that contact.
Figure 5. Editing a contact
Change the e-mail address of the contact to your e-mail address, your boss's e-mail address, or some other e-mail address with which you have communicated, and then click OK.
The search functionality is accessed through a new Communications button on the Contacts tab, as shown in Figure 6.
Figure 6. Windows Desktop Search enhancement
When the Communications button is clicked, the Visual Basic 6.0 application calls out to a form that was built by using Visual Basic 2005. This form calls into WDS to retrieve the e-mail communications with the selected contact, as shown in Figure 7.
Figure 7. New Visual Basic .NET form with search results
Because Visual Basic 2005 was used for this enhancement, this form gets a number of features for free. Firstly, the splitter bar between the grid and text area just works and requires zero code, because Visual Basic 2005 includes a SplitPanel control. Secondly, the form and its controls resize intelligently, because .NET Framework controls support docking: The controls just fill whatever space is available to them.
The core of the application is a Visual Basic 6.0 application that communicates with a SQL Server 2005 Express database. When you click the Communications button on the Contacts form, the e-mail address of the contact is passed to the Visual Basic 2005 form. As soon as the Visual Basic 2005 form has the e-mail address, it calls into Windows Desktop Search to retrieve the list of communications, and then calls into Microsoft Office Outlook to retrieve the e-mail contents.
Figure 8. Application data flow
You can create forms that are callable from Visual Basic 6.0 by installing Visual Studio 2005 and the Interop Forms Toolkit 2.0. By using the Interop Forms Toolkit 2.0, you can create Visual Basic 2005 forms that appear to Visual Basic 6.0 as regular COM objects.
The first steps are to install the Interop Forms Toolkit 2.0, start Visual Studio 2005, and create a new Visual Basic 6.0 InteropForm Library project, as shown in Figure 9.
Figure 9. Creating an InteropForm Library project
The new project contains an InteropForm item. This item is a regular Visual Basic 2005 form that contains additional information that indicates that a COM wrapper should be generated, making it easy for Visual Basic 6.0 to call into this form. If you want your component to contain multiple forms, you can just add additional InteropForm project items, as shown in Figure 10.
Figure 10. Adding additional InteropForms to the project
In the InteropForm, you can easily mark various properties and methods as being COM-callable. For properties, you just use the InteropFormProperty attribute, as shown in Listing 1.
<InteropFormProperty()> _ Public Property EmailAddress() As String Get Return m_emailAddress End Get Set(ByVal value As String) If value <> m_emailAddress Then m_emailAddress = value End If End Set End Property
Listing 1. Exposing Visual Basic 2005 form properties to Visual Basic 6.0
This property will allow the Visual Basic 6.0 application to specify the e-mail address for which communications should be found. The actual searching is performed by the Search function that is shown in Listing 2.
<InteropFormMethod()> _ Public Sub Search() LabelSearching.Visible = True Application.DoEvents() Dim cn As New OleDbConnection( _ "Provider=Search.CollatorDSO;" & _ "Extended Properties='Application=Windows';") Try cn.Open() Catch MessageBox.Show("Search functionality is dependent " & _ "on Windows Desktop Search 3.0 or later, " & _ "which is not installed.") Me.Close() Return End Try Dim da As New OleDbDataAdapter("Select System.Title, " & _ "System.ItemURL, " & _ "System.Message.SenderName, " & _ "System.Message.SenderAddress, " & _ "System.Message.ToAddress, " & _ "System.Message.DateSent " & _ "from systemindex " & _ "where (System.Message.SenderAddress = '" & _ m_emailAddress & "' " & _ "or CONTAINS(System.Message.ToAddress, '" & _ m_emailAddress & "')) " & _ "and CONTAINS(System.Message.MessageClass, 'MAPI')" & _ "order by System.Message.DateSent desc", cn) Dim dt As New DataTable da.Fill(dt) dt.Columns.Add("To", GetType(String)) For Each dr As DataRow In dt.Rows Dim toAddress As New StringBuilder For Each address As String In dr("System.Message.ToAddress") If toAddress.Length = 0 Then toAddress.Append(address) Else toAddress.Append(", ").Append(address) End If Next dr("To") = toAddress.ToString() Next bs.DataSource = dt WdsResultsDataGridView.AutoGenerateColumns = False WdsResultsDataGridView.DataSource = bs bs.ResetBindings(False) LabelSearching.Visible = False cn.Close() End Sub
Listing 2. Visual Basic 2005 Search function
You can see that this function is marked with the InteropFormMethod attribute, which exposes the Search function to Visual Basic 6.0 applications.
WDS exposes an OLE DB provider, which allows you to query WDS by using SQL statements. Here, you can see that a select statement is retrieving the sender name, "to" address, date sent, and other information. The results are limited to e-mail messages to or from the contact, and are sorted by date. The details of forming a WDS query can be a little complex, as WDS supports many fields and search options. (For links to WDS queries, see the Additional Resources section.)
An e-mail can be sent to multiple reciepients, so that additional code is used to generate a derived column that contains a comma-separated list of "to" addresses. Finally, a DataGridView control is bound to the results.
In Figure 5, you can see that the form shows also a preview of the actual message. This information is not available from WDS, which is just an index into e-mail messages and files. Instead, the form must communicate with Office Outlook to get the e-mail contents.
Every time that the user clicks on a different e-mail message in the grid, an event fires on the underlying BindingSource object. The BindingSource object is responsible for keeping track of the current record (much as the Recordset object in Visual Basic 6.0) and firing events whenever something changes. The PositionChanged event of the binding source is a good place to retrieve the contents for the currently selected e-mail item, as shown in Listing 3.
Private Sub bs_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bs.PositionChanged If outlookNs IsNot Nothing Then Dim dr As DataRow = bs.Item(bs.Position).row Dim s As String = EIDFromEncodedStringWDS30(dr("system.itemurl")) Dim mailItem As Object = outlookNs.GetItemFromID(s, Nothing) If mailItem IsNot Nothing Then RichTextBox1.Text = mailItem.body Else RichTextBox1.Text = "Unable to show preview." End If Else RichTextBox1.Text = "Outlook is not installed. " & _ "Unable to show preview" End If End Sub
Listing 3. Getting the message contents from Office Outlook
Items are retrieved from Office Outlook by using an item ID. WDS returns the item ID as a portion of the ItemURL field. The application extracts the ID from the ItemURL field, and then uses the Office Outlook object model to request the item. As soon as the item is retrieved, its contents are displayed in a rich-text box.
As you can see, creating the new form in Visual Basic 2005 is relatively straightforward and simple. Calling the new form from Visual Basic 6.0 also is straightforward and simple. When you compile the Visual Basic 2005 code, Visual Studio automatically registers it as a COM object. From Visual Basic 6.0, it just shows up in the References dialog box, as shown in Figure 11.
Figure 11. Referencing the Visual Basic 2005 extension from Visual Basic 6.0
As soon as the project has been referenced, you can call into the Visual Basic 2005 code as you would any other COM object. The code to display the search form is shown in Listing 4.
Private Sub cmdCommunications_Click() Dim frmSearch As VBNET_Extensions_WdsForm Set frmSearch = New VBNET_Extensions_WdsForm frmSearch.Show frmSearch.EmailAddress = rsContacts("Email") DoEvents frmSearch.Search End Sub
Listing 4. Calling into the Visual Basic 2005 extension from Visual Basic 6.0
In this example, you can see that it is not complicated to create Visual Basic 2005 extensions to existing Visual Basic 6.0 application; in fact, in many cases, it might well be the best course of action for many legacy applications. Extending existing applications by using Visual Basic 2005 allows you to leverage your existing investment and ramp up on Visual Basic 2005 as you go. It's a very low-risk approach (compared with migrating the entire application to Visual Basic 2005) and can allow you to make enhancements to your application very quickly.
Visual Basic Developer Center
Microsoft Interop Forms Toolkit 2.0
"Handy Tasks Using Microsoft Office Outlook 2003 and Visual Basic .NET"
"Overview of the Search SQL Syntax"
About the author
Scott Swigart spends his time consulting, authoring, and speaking about converging and emerging technologies. With development experience going back over 15 years, and by staying in constant contact with future software-development technologies, Scott is able to help organizations get the most out of today's technology while they prepare to leverage the technology of tomorrow. Scott is also the author of several .NET books, a certified Microsoft trainer (MCT) and developer (MCSD), and a Microsoft MVP. Feel free to contact the Scott at firstname.lastname@example.org, or check out his latest musings at blog.swigartconsulting.com.