April 1999

Glue Together Office 2000 Apps Using VBA

Use VBA6 in Outlook 2000 and Excel 2000 to build a personalized e-mail merge utility.

by Scott Emigh

Reprinted with permission from Visual Basic Programmer's Journal, Apr 1999, Volume 9, Issue 4, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com.

What you need: Outlook 2000 and Excel 2000 Note: I used the Beta 2 release of Outlook 2000 to develop the samples for this article, so what you see in the released version of Outlook 2000 might be somewhat different.

As many developers discovered, it was possible, but certainly not easy, to customize earlier versions of Outlook with user-defined forms and VBScript. This gave Outlook users a lot of flexibility if they were willing to struggle through the rather awkward mechanism for hooking up events and user-defined form fields. For more sophisticated applications, VB developers could automate Outlook by referencing the MS Outlook 97 executable component and creating an object reference to Outlook.Application. This required the overhead of object instantiation, unnecessary with VBA, and resulted in an out-of-process solution much slower than the in-process VBA solution.

Outlook 2000 now includes the VBA development environment, which allows developers to use VBA to customize each of the major applications in the Microsoft Office 2000 suite: Word, Excel, PowerPoint, Access, and Outlook. VBScript also enables developers to customize Outlook 2000, but VBA6 provides Outlook developers with many advantages over VBScript (see Table 1).

Why Use VBA?
VBA is composed of a set of DLLs. An application becomes a VBA host by using the Component Object Model (COM) APIs exposed by these DLLs. VBA therefore runs in process with the host and responds directly to events generated by the host application. Every VBA-enabled application, whether it comes from Microsoft or one of the 100+ ISVs who license VBA from Microsoft, exposes its programmable object model through COM. This means you can use every VBA-enabled application as a server from a VB program. VBA provides tremendous value, beyond simply scripting the host application from an external VB program, because VBA allows the developer to leverage and extend the functionality of the host application without starting from scratch.

Imagine writing a simple VB6 application that opens a Word template and populates it with data from a Microsoft SQL Server database. You would have to add a reference to the Word object library, instantiate a document object (perhaps using WithEvents), open the template, insert the data, save the new document, and make sure you close all the files properly. Then, after debugging, you would have to compile this application, test it again, and finally build setup files for distribution. Keep in mind that your VB6 application is calling across a process boundary when using the Word object model, an expensive operation in terms of performance.

With VBA you already have direct access to the Word object model and your code is running from the template document. You can put your data in access/populate, save the routine in the Template_Open event handler that Word provides to you automatically, and you're done. Because the code is saved in the Word template, distribution is a matter of sending the user a single Word file. Lastly, your code is running in process with Word, so you'll see clear performance improvements.

VBA is an excellent tool for adding customized functionality to a host application's UI. VBA allows you to add your own methods for handling repetitive tasks or to build user forms for custom data entry. However, one of VBA's most practical uses is tying applications together so developers can share application data and functionality within a common environment. Choosing Outlook's VBA as your development tool will save time and simplify distribution of your e-mail merge utility.

Understand Outlook 2000's Object Model
The key to productive development using Outlook 2000 is a solid understanding of the methods, properties, and events that make Outlook programmable—in other words, you need to understand Outlook's object model. As a VB developer already familiar with the language syntax, you can leverage your VB expertise in the VBA environment. You'll be working with both Excel and Outlook's object models. Although you'll find some changes in Excel's object model in Office 2000 (11 new objects for Web authoring, OLAP PivotTable, and OLE DB support), I'll assume you already understand how to program the Excel object model or have access to one of the many excellent resources on developing with Excel. However, Outlook 2000 has undergone significant change and warrants a brief introduction.

	Figure 1 Outlook 2000 Partial Object Model Diagram. Click here

If you've done any Office development, you already know that all Office applications expose an Application object at the top level of their object models. Outlook 2000 is no different (see Figure 1). The Application object provides global properties, methods, and events that are available to you from VB code without any variable declarations. Another standard that applies across the Office applications is the shortcut for displaying the VBA IDE: Press Alt+F11 to display the VBA IDE from Outlook. The VBA6 IDE looks virtually identical to the VB6 IDE, so you should feel right at home. Despite the fact that the VBA IDE is a top-level window, it is running in process with Outlook. The Project Explorer displays a VBAProject item that contains a host project item called ThisOutlookSession. This is VBA's way of exposing common application events such as Startup and NewMail. In the Project Explorer within VBA, double-click on ThisOutlookSession to open the code window, then select the Application object to see a complete listing of events on Outlook's Application object. For example, this code uses the Application object's ItemSend event to stamp any outgoing mail with the current date and time and the Outlook version number:

Private Sub Application_ItemSend _
   (ByVal Item As Object, _
   Cancel As Boolean)
   Item.Body = Item.Body & Date & _
      " " & Time & Application _
      .Session.CurrentUser.Name
End Sub

The NameSpace object is an abstract root for Outlook data sources. Currently, the only data source supported is "MAPI," which allows access to all Outlook data stored in the user's mail files. The Application's Session property returns the MAPI NameSpace object. You'll also use the Folders collection, accessible through the NameSpace object. The Folders collection is the equivalent of a file system for Outlook and, as such, provides access to all Outlook item types including mail, contacts, schedules, tasks, and notes.

Build the Email Merge Utility
Similar in operation to Word's e-mail merge mechanism, your e-mail merge utility will merge the data from a list saved in Excel with a draft Outlook message to generate a new e-mail message for each row in the Excel list. You'll tag the e-mail draft (which Outlook 2000 stores as an unsent item in the Drafts folder) with simple string tokens such as <<Column1>>, and then write VBA code to replace these tokens with values found in the Excel sheet containing your contact list.

A single form called frmEMailMerge acts as your Email Merge utility's focal point. A class called clsEMailMerge holds the required data and merge logic. To complete an e-mail merge you need an Excel workbook and a data range from that workbook, the number of the column that supplies the e-mail addresses, and an e-mail template. Use the frmEMailMerge form to indicate the state of this data collection. Once all four pieces of data have been supplied, the user can kick off the merge by clicking on the Send button. The user can cancel the operation at any time by clicking on the Cancel button.

You can change the name of the current VBAProject by right-clicking on the VBAProject icon in the Project Explorer window and selecting the VBA Project Properties menu item. This brings up a dialog that allows you to rename the project; I've renamed it EmailMergeProject. Unlike the other Office applications, Outlook stores VBA project data in an OTM file in the ..\Application Data\Outlook directory. You can back up your project code by copying this file.

Before continuing, you need to perform one more project maintenance task. To work effectively with the Excel object model from within your Outlook VBA code, you need to add a reference to the Excel object library. This improves the runtime performance of your VBA code while increasing your productivity. It also enables you to use the IntelliSense features when using the Excel object model. From the Tools menu select References, then check Microsoft Excel 9.0 Object Library and click on OK in the References dialog.

Figure 2 Email Merge Object Creation. Click here

Next, add a new UserForm to your VBAProject. Choose UserForm from the Insert menu in the VBA menu (see the "Microsoft Forms 2.0" sidebar, and Table 2 and Figure 2 for details of frmEMailMerge). Use the lblStatus labels to notify the user as each step is completed. The txtWorkbook and txtDataRange are for display only. Inhibit keypresses in these controls by setting the KeyCode = 0 in the KeyDown event, to force users to work through the Open and Select command buttons.

The frmEMailMerge form is your UI and it relies on the clsEMailMerge class, which encapsulates all the data and real functionality. You'll build this class before adding the form code.

Create an Email Merge Class
Add a class module to your project through the Insert menu. Call it clsEMailMerge and set its Instancing property to 2-PublicNotCreatable, which allows you to control the proper usage of this class from within your project.

Because the code in your clsEmailMerge class will work directly with Excel objects, and because your VBA code will need to respond to events within the open Workbook, such as SheetSelectionChange, you must add an Excel Application object declaration using WithEvents:

Public WithEvents XL As Excel.Application

In the Class_Initialize method, set the XL object reference to a new instance of Excel with:

Set XL = New Excel.Application

Likewise, in the Class_Terminate method, close the open Workbook (if there is one) and quit the running instance of Excel:

If Not xlWorkbook Is Nothing Then xlWorkbook.Close
If Not XL Is Nothing Then XL.Quit

Use the Is Nothing conditionals to verify that you have an object before you access it. If your object references are inadvertently lost, which would happen in this instance if the user shut down Excel while your VBA code was still executing, you wouldn't generate an error.

Now you have a class that connects to Excel and closes gracefully on termination. Add a form-level clsEMailMerge variable. In the UserForm_Initialize event handler, create a new instance of the EmailMerge class, and in the UserForm_Terminate event handler set the EmailMerge class equal to Nothing to allow the form to close and clean the Excel references (see Figure 2 for a diagram of the object creation scenario).

Before you can execute the merge process, your EmailMerge class must have the necessary data: an Excel Workbook object (Private xlWorkbook as Excel.Workbook), an Excel (data) Range object (Public DataRange as Excel.Range), the column containing the e-mail addresses (Public AddressCol as Integer), and a reference to the Email template draft (Public Template as Outlook.MailItem). You need to provide a property on the class to track the state of the class's required data. The State property returns an integer (expressed through an Enum) value from 0 to 4; 0 represents the initial state, and 4 represents the state of being ready to merge. The State property code tests the values of each of the required data variables and reports the furthest point in the sequence for which you have valid data. The complete code for this utility is available here.

In the form, use the State property to switch a select statement and adjust the color and enabled properties of the form controls to help guide the user through the process of supplying the required information. The SetState method prevents the user from attempting to choose an Excel data range before selecting a Workbook. You'll use the SetState method to initialize the form upon load and to refresh it after each piece of required data is supplied.

Don't Reinvent the Wheel
The first piece of information required is the Excel Workbook that contains the merge data. You could build your own Open dialog using Microsoft Forms, but why do that when you can reuse code already present in the Excel object model? From the form's Open… command button, invoke the Excel File | Open command and let the users select their desired Workbook. All Microsoft Office applications expose their UIs directly through the CommandBars collection. CommandBars refer to both the menu bar and any additional toolbars. CommandBars contain controls that are menu items or toolbar buttons. As with any collection, you can navigate CommandBars either through index or tag. Fire Excel's File | Open command with this line:

EmailMerge.XL.CommandBars("Standard") _
   .Controls("Open").Execute

The Execute method on a Control object is the same as clicking on that button or menu item in the UI. You've given users a convenient way to make their Workbook selection, but you need a way to determine when they're done with their selection and then grab a reference to it. By declaring your reference to Excel WithEvents, you solve both problems. The Excel Application's WorkbookOpen event fires upon completion of the open operation and passes a reference to the new workbook. Inside your class module, handle this event like this:

Private Sub XL_WorkbookOpen(ByVal Wb _
   As Excel.Workbook)
   Set xlWorkbook = Wb
   'Set txtWorkbook with the full path
   'of the newly opened Workbook
   frmEmailMerge.txtWorkbook = _
      WorkbookName
   'Get selected range from Workbook
   Set DataRange = XL.Selection
   If Not DataRange Is Nothing Then _
      frmEmailMerge.txtDataRange = _
      DataRange.Address
End Sub

The WorkbookName property of your class uses the FullName property of the Workbook object to return a full path name.

Next, you need the desired Excel data range selection, which defines what columns and how many rows you'll use to generate the e-mail merge. As a default, you can see from the XL_WorkbookOpen event handler that I used the Selection property of the Excel Application object. This property returns a Range object composed of the currently selected cells. (For more information on the Excel object model, see Microsoft Excel 2000's VBA Help.) Set the form's txtDataRange text property to the DataRange's Address property, which displays the range selection in Excel format.

Because this data range is a default, the user can click on the Select button to manually select a range:

Private Sub cmdDataRangeSelect_Click()
   MsgBox "Select the cells you " & _
      "wish to use in your merge."
   EMailMerge.XL.Visible = True
   EMailMerge.XL.Cells(1).Activate
End Sub

Pop up a message box to let users know they should be selecting data in Excel, then force the Excel window up by setting the Excel application's Visible property to True. This is much the same as setting the Visible property on a VB form in that it doesn't affect the load state of the application. Also clear any previous selection by forcing a select of cell A1 through the Activate method on the first cell in the Cells collection. At this point you have delegated some user action to Excel. You need to know when the user has finished the task of selecting a data range. Once again, rely on the event hookup between Outlook and Excel that you created with your WithEvents declaration of the Excel Application object. This time, wait for the application's SheetSelectionChange. This useful event is fired whenever a range selection changes on any sheet on any open workbook. The event handler not only gets the Worksheet reference, but also the new selected range:

Private Sub xl_SheetSelectionChange _
   (ByVal Sh As Object, ByVal Target As _
   Excel.Range)
   Dim nResult As Integer
    
   XL.Visible = False
   nResult = MsgBox("Is this the " & _
      "data selection you wish " & _
      "to use?", vbYesNo, _
      "Use this Selection?")
   If nResult = vbYes Then
      Set DataRange = Target
      frmEmailMerge.txtDataRange = _
         DataRange.Address
   Else
      XL.Visible = True
      Exit Sub
   End If
    
End Sub

Bring focus back to Outlook by hiding the Excel window. Then verify that the user made his selection correctly. If so, set the DataRange based on the Target object passed into the event handler and update the form's txtDataRange textbox. If the user isn't sure about his selection, then the Excel window is displayed again and you wait for the next selection change.

Once you have the data range, populate the cmbAddressCol drop-down list with the data range column options. The user selects the "email address" column from this list. Your list-populate routine is located in the txtDataRange_Change event handler, so every time the data range changes your list is updated. To populate the list, loop through the Columns collection of the DataRange object and call cmbAddressCol.AddItem with both the column index and name (derived from the first row of each column). From the cmbAddressCol_Click event handler, set the EmailMerge.AddressCol to the selected column index.

Get Default Folders
The last piece of information required, the Email template, comes from Outlook, not Excel. Upon form initialization, populate the cmbDrafts drop-down listbox with the Subject line text from each mail item in the Drafts folder of the user's message stores:

Sub RefreshDraftList()
   Dim olMailItem As Outlook.MailItem
   Dim olFolder As Outlook.MAPIFolder

   cmbDrafts.Clear
   
   Set olFolder = _
      Session.GetDefaultFolder _
      (olFolderDrafts)
   If olFolder.Items.Count = 0 _
      Then Exit Sub
   For Each olMailItem In _
      olFolder.Items
      cmbDrafts.AddItem _
         olMailItem.Subject
   Next olMailItem
End Sub

Get a reference to the Drafts folder by invoking the GetDefaultFolder function of the NameSpace object. (Recall you use the application's Session property to return a reference to the current NameSpace object.) GetDefaultFolder takes an olDefaultFolder enum. This useful function quickly obtains fundamental MAPI folders, even if they've been moved around in the message store. Other folders you can retrieve with GetDefaultFolder include Inbox, Calendar, Contacts, Tasks, Notes, and Outbox.

If the Drafts folder contains any draft e-mails, perform a For…Each loop on mail items in the Drafts folder and for each one add its Subject line text to the cmbDrafts listbox. When the user selects one of these drafts from the listbox, assign a reference to your EmailMerge class with this line:

Set EMailMerge.Template = Session. _
   GetDefaultFolder(olFolderDrafts). _
   Items(cmbDrafts.List _
   (cmbDrafts.ListIndex))

Let 'er Rip
You now have everything you need to execute your merge. EmailMerge.State is 4 (ReadyToMerge) and the form's Send button has been enabled. The cmdSend_Click event handler calls EmailMerge.SendMail.

SendMail is fairly straightforward. Using the Count property of the DataRange's Rows collection, create a loop for each row of data. Within this loop get a row of data from the Excel data range. Use the Rows collection of the Range object to grab the individual rows based on the loop index:

Set xlDataRow = DataRange.Rows(nCount)

Create a new mail item and use the Outlook Application object's CreateItem function to return a new mail item. The CreateItem function, like the GetDefaultFolder function, takes an olItemType enum and can return a variety of Outlook items including mail items, task items, and appointment items. Use olMailItem to return a mail item:

Set olMail = CreateItem(olMailItem)

Merge the subject line. Set the new mail item's Subject property to set the subject line text to a merged version of the draft e-mail's subject line text. Use a simple search/replace routine called ParseString to provide the merged text:

olMailItem.Subject = ParseString _
   (Template.Subject, xlDataRow)

Merge the body text. As with the subject line, use the Body property of the mail item:

olMailItem.Body = ParseString _
   (Template.Body, xlDataRow)

Copy any recipients the cc or bcc of the template mail might specify. Loop through the Recipients collection of the Template e-mail and use the Name property from each Recipient object to create a new Recipient object attached to the new mail item. Set the new Recipient object's Type property (olCC or olBCC) equal to the corresponding Template Recipient's Type property:

Set olRecipient = olMail.Recipients. _
   Add(olTemplateRecipient.Name)
olRecipient.Type = _
   olTemplateRecipient.Type

Set the To address from the Excel data range address column. The mail item has a To property you can set with a string e-mail address:

olMail.To = xlDataRow.Cells(1, _
   AddressCol).Value

Send the mail. Use the mail item's Send method to post it to the Outbox:

olMail.Send

Attach to Outlook's Toolbar
To kick off your e-mail merge utility, provide a submethod that you can attach to an Outlook toolbar button or the Macro dialog. Add a (code) module to the VBA project. You only need this code to activate your utility:

Public Sub ActivateMerge()
   frmEmailMerge.Show
End Sub

To attach this sub to an Outlook toolbar button, right-click on any toolbar or the menu bar and select Customize. From the Customize dialog, select the Commands tab and choose Macros from the Categories. In the Commands list, you should see the ActivateMerge sub listed. Drag this label onto any toolbar to attach it. You can change the caption of this new button by right-clicking on it and selecting Properties.

VBA excels at user interface customization and event-driven scripting, but it's also a powerful platform for ultra-rapid application development. VBA's approach to project storage cooperation with the host application's object model allows VBA to eliminate and simplify distribution and version control. In your future projects, remember VBA's capability to streamline application development and simplify distribution. For example, you can distribute this e-mail merge utility simply by providing the single OTM file and placing it into the \Windows\Application Data\ Microsoft\Outlook directory.


Scott Emigh is a VBA integration consultant for Summit Software in Dallas, Texas. He works with ISVs who license and integrate VBA into their applications. Reach him through e-mail at scotte@summsoft.com.