Enhancing an Application with Windows Desktop Search and Office Outlook Integration

Article
02/25/2008

Sales Support: A Hybrid Visual Basic 6.0 & Visual Basic 2005 Application, Part 1

Scott Swigart

February 2008

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.

Introduction

The Sales Support Application

Windows Desktop Search Enhancement

Application Architecture

Visual Basic 2005 Implementation

Visual Basic 6.0 Implementation

Conclusion

Additional Resources

Introduction

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.

The Sales Support Application

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

Installation

The Sales Support installer (https://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.

Operation

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.

Windows Desktop Search Enhancement

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.

Application Architecture

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

Visual Basic 2005 Implementation

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.

Visual Basic 6.0 Implementation

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

Conclusion

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.

Additional Resources

Visual Basic Developer Center
Microsoft Interop Forms Toolkit 2.0
MSDN Library
"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 scott@swigartconsulting.com, or check out his latest musings at blog.swigartconsulting.com.

Enhancing an Application with Windows Desktop Search and Office Outlook Integration

Contents

Introduction

The Sales Support Application

Installation

Operation

Windows Desktop Search Enhancement

Application Architecture

Visual Basic 2005 Implementation

Visual Basic 6.0 Implementation

Conclusion

Additional Resources

About the author

Additional resources