Office Automation and Digital Certificates Demonstration

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: In this column, you will learn how to add digital certificates to Excel and Outlook, as well as how to automate Excel from Outlook in order to retrieve stock ticker data based on a specific trigger. (8 printed pages)

Frank C. Rice, Microsoft Corporation

May 16, 2003

Applies to: Microsoft Excel 2002, Microsoft Outlook 2002

Download the odc_olxlauto.exe sample file.

Contents

  • Create the Digital Signature Certificate

  • Testing the Application

  • Conclusion

Overview

Automation is defined as running code from one host application in order to use the functionality of another application. For example, as you'll see in this article, you can run Visual Basic for Applications (VBA) code from Microsoft Outlook that opens an instance of Microsoft Excel, processes data in an Excel workbook, closes the instance of the workbook, and then uses the Excel output, all from inside Outlook. In this scenario, the controlling application (Outlook) works with the controlled application (Excel) by manipulating its exposed properties, methods, and objects. To accomplish this, Outlook just needs a reference to Excel's object library in order to create an instance of the workbook application.

Since we are talking about running code within and across different applications, the issue of security and trust comes into play. How can Outlook trust the data that it receives from Excel, and likewise how can Excel trust that any data it receives from Outlook is trustworthy? The answer is through certificates using digital signatures. A digital signature is an electronic, cryptographic-based, secure stamp of authentication. This signature confirms that the VBA macro project originated from the certificate signer and has not been altered. When you add a digital signature to a VBA macro project (also known as code signing), you are supplying a verifiable signature that can vouch for the authenticity and integrity of the VBA macro project.

To digitally sign VBA macro projects, you must first obtain a digital certificate for software publishing. There are three ways of getting a digital certificate:

  1. Create a digital certificate for your own use. Such a certificate would only be used to sign macros for your own use or for sharing within a small workgroup.

  2. Obtain a digital certificate from your organization's internal certification authority. Some organizations and corporations may choose to have a security administrator or group act as its own certification.

  3. Obtain a digital certificate from a commercial certification authority, such as VeriSign, Inc.

In this demonstration, you will create a digital certification for your own use. For more information about code signing see the article Code Signing Office XP Visual Basic for Applications Macro Projects.

Create the Digital Signature Certificate

To create a digital certificate for your own use, you run the Create Digital Certificate utility (Selfcert.exe) that comes with Microsoft Office 2000 and later versions and enter information about yourself that is stored in the certificate. Because a digital certificate created in this fashion isn't issued by a formal certification authority, a digital certificate created this way is called a self-signed certificate and VBA projects signed by using such a certificate are referred to as self-signed projects.

Installing the Create Digital Certificate Utility

The file SelfCert.exe is not installed in a typical installation of Office. To install the Create Digital Certificate utility:

  1. In the Control Panel, double-click Add/Remove Programs.

  2. On the Install/Uninstall tab, click Microsoft Office XP, and then click Add/Remove.

  3. In the Microsoft Office XP Setup dialog box, click the Add or Remove Features button.

  4. Expand Office Tools and set Digital Signature for VBA Projects to Run from My Computer.

  5. Click Update Now.

Selfcert.exe will be installed in the same folder as the Office XP applications, which by default is the C:\Program Files\Microsoft Office\Office10 folder. For Microsoft Office 2002, the default installation is C:\Program Files\Microsoft Office\Office.

Creating the Certificate

  1. From "C:\Program Files\Microsoft Office\Office10", run SelfCert.exe.

  2. Enter your name (see Figure 1) and click OK. This will create a certificate for you to use only on your PC.

    Figure 1. The Create Digital Certificate form

Applying the Certificate to the Excel Macro

  1. Create a folder named C:\Demos\ OfficePowerUser.

  2. Copy MSFT.xls to C:\Demos\OfficePowerUser.

  3. Open C:\Demos\OfficePowerUser\MSFT.xls.

  4. From Excel, click Macro on the Tools menu, and then click Visual Basic Editor.

  5. From Excel's Visual Basic Editor, click Digital Signature on the Tools menu.

  6. In the Digital Signature dialog box (Figure 2), click the Choose button.

    Figure 2. The Digital Signature dialog box in Excel

  7. Select the certificate you just created, as shown in Figure 3.

    Figure 3. The Select Certificate dialog box

  8. Press OK twice.

  9. From Excel's Visual Basic Editor, on the File menu select Save MSFT.xls.

  10. Exit the Visual Basic Editor and Excel.

Adding a Certificate to Outlook

  1. Start Outlook.

  2. From Outlook, click Tools, click Macros, and then click Visual Basic Editor.

  3. Click Digital Signature on the Tools menu, and then click the Choose button.

  4. Select the certificate you just created.

  5. Press OK twice.

  6. On the File menu, select Save VbaProject.OTM.

Adding VBA Code to Outlook

  1. While still in Outlook's Visual Basic Editor, click References on the Tools menu, and then add Microsoft Excel 10.0 Object Library for Office XP (Microsoft Excel 11.0 Object Library for Office 2003) to the available references.

  2. Double-click ThisOutlookSession and copy then paste the following code (also available in the Code_for_ThisOutlookSession.txt included with the download for this column). This is the code that is called from the Rules Wizard when a message is received by Outlook that contains "MSFT" in the subject line.

    Public Sub Get_MSFT_Price_Rule(objMailItem As MailItem)
    ' This is a script that that can be used from the Rules Wizard.
    ' Create a rule that runs this script when the subject equals 
    ' MSFT. The message gets removed and replaced with one with a
    ' subject of "MSFT = " $stock price

    ' Create the variables.
    Dim xlApp       As Excel.Application
    Dim xlBook      As Excel.Workbook
    Dim xlSheet     As Excel.Worksheet
    Dim NewSubject  As String
    Dim myNS        As NameSpace
    Dim myInbox     As MAPIFolder
    Dim msg         As Object

    ' Set the variables.
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(FileName:="C:\Demos\OfficePowerUser\MSFT.xls")
    Set xlSheet = xlBook.Sheets("Sheet1")
    Set myNS = GetNamespace("MAPI")
    Set myInbox = myNS.GetDefaultFolder(olFolderInbox)

    ' Make Excel visible so we can see what is happening.
'    xlApp.Visible = True

    ' Select the cell named MSFT and set it to the NewSubject variable.
    NewSubject = xlSheet.Range("MSFT")
    NewSubject = "MSFT = " + NewSubject

   'We are done with Excel, so close it and destroy object variables.
    xlBook.Close savechanges:=True
    Set xlSheet = Nothing
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    ' Now do something with the data gathered from Excel.
    For Each msg In myNS.GetDefaultFolder(olFolderInbox).Items
        If msg.Subject = "MSFT" Then
            msg.Reply
            msg.Subject = NewSubject
            msg.Save
        End If
    Next

End Sub

Looking at this subroutine, first an instance of Excel is created, the worksheet MSFT.xls is loaded, and then a reference is set to Sheet1. Next, a Namespace object is created in order to access the Outlook data stored in the Messaging Application Programming Interface (MAPI) folders in the user's mailbox. Then, a reference is set to the user's Inbox folder and the workbook is made visible.

The actual stock quote value, as stored in the MSFT named range in the Excel worksheet, is retrieved and stored in the NewSubject variable. Since no further information is needed from Excel, the workbook and application are closed and the variables are freed up.

Finally, the code walks through each message in the Inbox and if the Subject field is equal to "MSFT", then the String value representing the stock quote price is substituted for the subject line.

  1. On the File menu, click Save VbaProject.OTM.

  2. On the File menu, click Close and Return to Microsoft Outlook.

Setting Up the Outlook Rule

  1. In Outlook, click Rules Wizard (Rules and Alerts in Outlook 2003) on the Tools menu.

  2. Click New (New Rule in Outlook 2003).

  3. Select Start from blank rule and Check messages when they arrive, and then click Next.

  4. Select With specific words in the subject.

  5. In the Rules description section, click specific words and add MSFT to the Search List and then click Add.

  6. Click OK, and then click Next.

  7. Under What do you want to do with the message, select run a script.

  8. In the Rules Description section, click on script and select the Get_MSFT_Price_Rule script. You can probably only see part of the text though, as shown if Figure 4 below.

    Figure 4. The Select Script dialog box

  9. Click OK, then Finish, then OK (it will only run client-side), then OK to close out of the Rules Wizard.

    The end result should be a rule that looks like similar to the "MSFT (client-only)" rule as shown in Figure 5.

    Figure 5. The Rules and Alerts dialog box

Testing the Application

Send yourself a piece of e-mail with MSFT in the subject line. Give it a minute and the subject line will change to "MSFT = $25.00" - or whatever the current stock price for Microsoft is, as shown in Figure6.

Figure 6. A resulting e-mail with the MSFT stock quote

Conclusion

In this column, we looked at the different types of certificates and how you can digitally sign a certificate for use in small workgroup. We also demonstrated how we can control one Office application from inside another application. This technology, known as automation, is a powerful way to combine the functionality of multiple applications inside your own Office solutions.

About the Author

Frank Rice is a programmer writer for the Microsoft Office Developer Center, where he adds his teaspoon of content to the flood of developer documents. In previous incarnations, he was an Access application developer and Access support engineer.