Chapter 20: Interacting with Other Applications

IN THIS CHAPTER

• Starting or activating another application from Excel

• Displaying Windows Control Panel dialog boxes

• Using Automation to control another application

• Using SendKeys as a last resort

Starting an Application from Excel

Launching another application from Excel is often useful. For example, you might want to execute another Microsoft Office application or even a DOS batch file from Excel. Or, as an application developer, you may want to make it easy for a user to access the Windows Control Panel to adjust system settings.

Using the VBA Shell function

The VBA Shell function makes launching other programs relatively easy. Following is an example of VBA code that launches the Windows Calculator application.

Sub StartCalc()

Dim Program As String

Dim TaskID As Double

On Error Resume Next

Program = “calc.exe”

TaskID = Shell(Program, 1)

If Err <> 0 Then

MsgBox “Cannot start “ & Program, vbCritical, “Error”

End If

End Sub

You'll probably recognize the application that this procedure launches in Figure 20-1.

FIGURE 20-1: Running the Windows Calculator program from Excel.

The Shell function returns a task identification number for the application specified in the first argument. You can use this number later to activate the task. The second argument for the Shell function determines how the application is displayed. (1 is the code for a normal-size window, with the focus.) Refer to the Help system for other values for this argument.

If the Shell function isn't successful, it generates an error. Therefore, this procedure uses an On Error statement to display a message if the executable file can't be found or if some other error occurs.

It's important to understand that your VBA code doesn't pause while the application that was started with the Shell function is running. In other words, the Shell function runs the application asynchronously. If the procedure has more instructions after the Shell function is executed, these instructions are executed concurrently with the newly loaded program. If any instruction requires user intervention (for example, displaying a message box), Excel's title bar flashes while the other application is active.

In some cases, you may want to launch an application with the Shell function, but you need your VBA code to pause until the application is closed. For example, the launched application might generate a file that is used later in your code. Although you can't pause the execution of your code, you can create a loop that does nothing except monitor the application's status. The example that follows displays a message box when the application launched by the Shell function has ended:

Declare PtrSafe Function OpenProcess Lib “kernel32” _

(ByVal dwDesiredAccess As Long, _

ByVal bInheritHandle As Long, _

ByVal dwProcessId As Long) As Long

Declare PtrSafe Function GetExitCodeProcess Lib “kernel32” _

(ByVal hProcess As Long, _

lpExitCode As Long) As Long

Sub StartCalc2()

Dim TaskID As Long

Dim hProc As Long

Dim lExitCode As Long

Dim ACCESS_TYPE As Integer, STILL_ACTIVE As Integer

Dim Program As String

ACCESS_TYPE = &H400

STILL_ACTIVE = &H103

Program = “Calc.exe”

On Error Resume Next

‘ Shell the task

TaskID = Shell(Program, 1)

‘ Get the process handle

hProc = OpenProcess(ACCESS_TYPE, False, TaskID)

If Err <> 0 Then

MsgBox “Cannot start “ & Program, vbCritical, “Error”

Exit Sub

End If

Do ‘Loop continuously

‘ Check on the process

GetExitCodeProcess hProc, lExitCode

‘ Allow event processing

DoEvents

Loop While lExitCode = STILL_ACTIVE

‘ Task is finished, so show message

MsgBox Program & “ was closed”

End Sub

While the launched program is running, this procedure continually calls the GetExitCodeProcess function from within a Do-Loop structure, testing for its returned value (lExitCode). When the program is finished, lExitCode returns a different value, the loop ends, and the VBA code resumes executing.

Both of the preceding examples are available on the companion CD-ROM. The filename is start calculator.xlsm.

Using the Windows ShellExecute API function

ShellExecute is a Windows Application Programming Interface (API) function that is useful for starting other applications. Importantly, this function can start an application only if an associated filename is known (assuming that the file type is registered with Windows). For example, you can use ShellExecute to display a Web document by starting the default Web browser. Or you can use an e-mail address to start the default e-mail client.

The API declaration follows (this code works only with Excel 2010):

Private Declare PtrSafe Function ShellExecute Lib “shell32.dll” _

Alias “ShellExecuteA” (ByVal hWnd As Long, _

ByVal lpOperation As String, ByVal lpFile As String, _

ByVal lpParameters As String, ByVal lpDirectory As String, _

ByVal nShowCmd As Long) As Long

The following procedure demonstrates how to call the ShellExecute function. In this example, it opens a graphics file by using the graphics program that's set up to handle JPG files. If the result returned by the function is less than 32, then an error occurred.

Sub ShowGraphic()

Dim FileName As String

Dim Result As Long

FileName = ThisWorkbook.Path & “flower.jpg”

Result = ShellExecute(0&, vbNullString, FileName, _

vbNullString, vbNullString, vbNormalFocus)

If Result < 32 Then MsgBox “Error”

End Sub

The next procedure opens a text file, using the default text file program:

Sub OpenTextFile()

Dim FileName As String

Dim Result As Long

FileName = ThisWorkbook.Path & “ extfile.txt”

Result = ShellExecute(0&, vbNullString, FileName, _

vbNullString, vbNullString, vbNormalFocus)

If Result < 32 Then MsgBox “Error”

End Sub

The following example is similar, but it opens a Web URL by using the default browser:

Sub OpenURL()

Dim URL As String

Dim Result As Long

URL = “http://spreadsheetpage.com”

Result = ShellExecute(0&, vbNullString, URL, _

vbNullString, vbNullString, vbNormalFocus)

If Result < 32 Then MsgBox “Error”

End Sub

You can also use this technique with an e-mail address. The following example opens the default e-mail client (if one exists) and then addresses an e-mail to the recipient:

Sub StartEmail()

Dim Addr As String

Dim Result As Long

Addr = “mailto:[email protected]”

Result = ShellExecute(0&, vbNullString, Addr, _

vbNullString, vbNullString, vbNormalFocus)

If Result < 32 Then MsgBox “Error”

End Sub

These examples are available on the companion CD-ROM in a file named shellexecute examples.xlsm. This file uses API declarations that are compatible with all versions of Excel.

Activating an Application with Excel

In the previous section, I discuss various ways to start an application. You may find that if an application is already running, using the Shell function may start another instance of it. In most cases, however, you want to activate the instance that's running — not start another instance of it.

Using AppActivate

The following StartCalculator procedure uses the AppActivate statement to activate an application if it's already running (in this case, the Windows Calculator). The argument for AppActivate is the caption of the application's title bar. If the AppActivate statement generates an error, it indicates that the Calculator is not running. Therefore, the routine starts the application.

Sub StartCalculator()

Dim AppFile As String

Dim CalcTaskID As Double

AppFile = “Calc.exe”

On Error Resume Next

AppActivate “Calculator”

If Err <> 0 Then

Err = 0

CalcTaskID = Shell(AppFile, 1)

If Err <> 0 Then MsgBox “Can't start Calculator”

End If

End Sub

This example is available on the companion CD-ROM. The filename is start calculator.xlsm.

Activating a Microsoft Office application

If the application that you want to start is one of several Microsoft applications, you can use the ActivateMicrosoftApp method of the Application object. For example, the following procedure starts Word:

Sub StartWord()

Application.ActivateMicrosoftApp xlMicrosoftWord

End Sub

If Word is already running when the preceding procedure is executed, it is activated. The other constants available for this method are:

• xlMicrosoftPowerPoint

• xlMicrosoftMail (activates Outlook)

• xlMicrosoftAccess

• xlMicrosoftFoxPro

• xlMicrosoftProject

• xlMicrosoftSchedulePlus (an obsolete Microsoft Office time-management program)

Running Control Panel Dialog Boxes

Windows provides quite a few system dialog boxes and wizards, most of which are accessible from the Windows Control Panel. You might need to display one or more of these from your Excel application. For example, you might want to display the Windows Date and Time dialog box, shown in Figure 20-2.

FIGURE 20-2: Use VBA to display a Control Panel dialog box.

The key to running other system dialog boxes is to execute the rundll32.exe application by using the VBA Shell function.

The following procedure displays the Date and Time dialog box:

Sub ShowDateTimeDlg()

Dim Arg As String

Dim TaskID As Double

Arg = “rundll32.exe shell32.dll,Control_RunDLL timedate.cpl”

On Error Resume Next

TaskID = Shell(Arg)

If Err <> 0 Then

MsgBox (“Cannot start the application.”)

End If

End Sub

Following is the general format for the rundll32.exe application:

rundll32.exe shell32.dll,Control_RunDLL filename.cpl, n,t

• filename.cpl: The name of one of the Control Panel *.CPL files.

• n: The zero-based number of the applet within the *.CPL file.

• t: The number of the tab (for multi-tabbed applets).

A workbook that displays12 additional Control Panel applets, depicted in Figure 20-3, is available on the companion CD-ROM. The filename is control panel dialogs.xlsm.

FIGURE 20-3: The workbook that displays this dialog box demonstrates how to run system dialog boxes from Excel.

Using Automation in Excel

You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, the Excel macro will control Word's automation server. In such circumstances, Excel is the client application, and Word is the server application. Or you can write a VBA application in Word to control Excel. The process of one application's controlling another is sometimes known as Object Linking and Embedding (OLE), or simply automation.

The concept behind automation is quite appealing. A developer who needs to generate a chart, for example, can just reach into another application's grab bag of objects, fetch a Chart object, and then manipulate its properties and use its methods. Automation, in a sense, blurs the boundaries between applications. An end user may be working with an Access object and not even realize it.

Some applications, such as Excel, can function as either a client application or a server application. Other applications can function only as client applications or only as server applications.

In this section, I demonstrate how to use VBA to access and manipulate the objects exposed by other applications. The examples use Microsoft Word, but the concepts apply to any application that exposes its objects for automation — which accounts for an increasing number of applications.

Working with foreign objects using automation

As you may know, you can use Excel's Insert⇒Text⇒Object command to embed an object, such as a Word document, in a worksheet. In addition, you can create an object and manipulate it with VBA. (This action is the heart of Automation.) When you do so, you usually have full access to the object. For developers, this technique is generally more beneficial than embedding the object in a worksheet. When an object is embedded, the user must know how to use the automation object's application. But when you use VBA to work with the object, you can program the object so that the user can manipulate it by an action as simple as a button click.

Early versus late binding

Before you can work with an external object, you must create an instance of the object. You can do so in either of two ways: early binding or late binding. Binding refers to matching the function calls written by the programmer to the actual code that implements the function.

Early binding

To use early binding, create a reference to the object library by choosing the Tools⇒References command in the Visual Basic Editor (VBE), which brings up the dialog box shown in Figure 20-4. Then put a check mark next to the object library you need to reference.

After the reference to the object library is established, you can use the Object Browser, shown in Figure 20-5, to view the object names, methods, and properties. To access the Object Browser, press F2 in the VBE.

When you use early binding, you must establish a reference to a version-specific object library. For example, you can specify Microsoft Word 10.0 Object Library (for Word 2002), Microsoft Word 11.0 Object Library (for Word 2003), Microsoft Word 12.0 Object Library (for Word 2007), or Microsoft Word 14.0 Object Library (for Word 2010). Then you use a statement like the following to create the object:

Dim WordApp As New Word.Application

Using early binding to create the object by setting a reference to the object library usually is more efficient and also often yields better performance. Early binding is an option, however, only if the object that you're controlling has a separate type library or object library file. You also need to ensure that the user of the application actually has a copy of the specific library installed.

Another advantage of early binding is that you can use constants that are defined in the object library. For example, Word (like Excel) contains many predefined constants that you can use in your VBA code. If you use early binding, you can use the constants in your code. If you use late binding, you'll need to use the actual value rather than the constant.

FIGURE 20-4: Adding a reference to an object library file.

FIGURE 20-5: Use the Object Browser to learn about the objects in a referenced library.

Still another benefit of using early binding is that you can take advantage of the VBE Object Browser and Auto List Members option to make it easier to access properties and methods; this feature doesn't work when you use late binding because the type of the object is known only at runtime.

Late binding

At runtime, you use either the CreateObject function to create the object or the GetObject function to obtain a saved instance of the object. Such an object is declared as a generic Object type, and its object reference is resolved at runtime.

You can use late binding even when you don't know which version of the application is installed on the user's system. For example, the following code, which works with Word 97 and later, creates a Word object:

Dim WordApp As Object

Set WordApp = CreateObject(“Word.Application”)

If multiple versions of Word are installed, you can create an object for a specific version. The following statement, for example, uses Word 2003:

Set WordApp = CreateObject(“Word.Application.11”)

The Registry key for Word's Automation object and the reference to the Application object in VBA just happen to be the same: Word.Application. They do not, however, refer to the same thing. When you declare an object As Word.Application or As New Word.Application, the term refers to the Application object in the Word library. But when you invoke the function CreateObject(“Word.Application”), the term refers to the moniker by which the latest version of Word is known in the Windows System Registry. This isn't the case for all automation objects, although it is true for the main Office 2010 components. If the user replaces Word 2003 with Word 2010, CreateObject(“Word.Application”) will continue to work properly, referring to the new application. if Word 2010 is removed, however, CreateObject(“Word.Application.14”), which uses the alternate version-specific name for Word 2010, will fail to work.

The CreateObject function used on an automation object such as Word.Application or Excel.Application always creates a new instance of that automation object. That is, it starts up a new and separate copy of the automation part of the program. Even if an instance of the automation object is already running, a new instance is started, and then an object of the specified type is created.

To use the current instance or to start the application and have it load a file, use the GetObject function.

If you need to automate an Office application, it is recommended that you use early binding and reference the earliest version of the product that you expect could be installed on your client's system. For example, if you need to be able to automate Word 2003, Word 2007, and Word 2010, you should use the type library for Word 2003 to maintain compatibility with all three versions. This approach, of course, will mean that you can't use features found only in the later version of Word.

A simple example of late binding

The following example demonstrates how to create a Word object by using late binding. This procedure creates the object, displays the version number, closes the Word application, and then destroys the object (thus freeing the memory that it used):

Sub GetWordVersion()

Dim WordApp As Object

Set WordApp = CreateObject(“Word.Application”)

MsgBox WordApp.Version

WordApp.Quit

Set WordApp = Nothing

End Sub

The Word object that's created in this procedure is invisible. If you'd like to see the object's window while it's being manipulated, set its Visible property to True, as follows:

WordApp.Visible = True

This example can also be programmed using early binding. Before doing so, choose Tools⇒References to set a reference to the Word object library. Then you can use the following code:

Sub GetWordVersion()

Dim WordApp As New Word.Application

MsgBox WordApp.Version

WordApp.Quit

Set WordApp = Nothing

End Sub

Controlling Word from Excel

The example in this section demonstrates Automation by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each document to a file. The information used to create the memos is stored in a worksheet, as shown in Figure 20-6.

FIGURE 20-6: Word automatically generates three memos based on this Excel data.

The MakeMemos procedure starts by creating an object called WordApp. The routine cycles through the three rows of data in Sheet1 and uses Word's properties and methods to create each memo and save it to disk. A range named Message (in cell E6) contains the text used in the memo. All the action occurs behind the scenes: That is, Word isn't visible.

Sub MakeMemos()

‘ Creates memos in word using Automation

Dim WordApp As Object

Dim Data As Range, message As String

Dim Records As Integer, i As Integer

Dim Region As String, SalesAmt As String, SalesNum As String

Dim SaveAsName As String

‘ Start Word and create an object (late binding)

Set WordApp = CreateObject(“Word.Application”)

‘ Information from worksheet

Set Data = Sheets(“Sheet1”).Range(“A1”)

Message = Sheets(“Sheet1”).Range(“Message”)

‘ Cycle through all records in Sheet1

Records = Application.CountA(Sheets(“Sheet1”).Range(“A:A”))

For i = 1 To Records

‘ Update status bar progress message

Application.StatusBar = “Processing Record “ & i

‘ Assign current data to variables

Region = Data.Cells(i, 1).Value

SalesNum = Data.Cells(i, 2).Value

SalesAmt = Format(Data.Cells(i, 3).Value, “#,000”)

‘ Determine the filename

SaveAsName = Application.DefaultFilePath & _

“” & Region & “.docx”

‘ Send commands to Word

With WordApp

.Documents.Add

With .Selection

.Font.Size = 14

.Font.Bold = True

.ParagraphFormat.Alignment = 1

.TypeText Text:=”M E M O R A N D U M”

.TypeParagraph

.TypeParagraph

.Font.Size = 12

.ParagraphFormat.Alignment = 0

.Font.Bold = False

.TypeText Text:=”Date:” & vbTab & _

Format(Date, “mmmm d, yyyy”)

.TypeParagraph

.TypeText Text:=”To:” & vbTab & Region & _

“ Manager”

.TypeParagraph

.TypeText Text:=”From:” & vbTab & _

Application.UserName

.TypeParagraph

.TypeParagraph

.TypeText Message

.TypeParagraph

.TypeParagraph

.TypeText Text:=”Units Sold:” & vbTab & _

SalesNum

.TypeParagraph

.TypeText Text:=”Amount:” & vbTab & _

Format(SalesAmt, “$#,##0”)

End With

.ActiveDocument.SaveAs FileName:=SaveAsName

End With

Next i

‘ Kill the object

WordApp.Quit

Set WordApp = Nothing

‘ Reset status bar

Application.StatusBar = “”

MsgBox Records & “ memos were created and saved in “ & _

Application.DefaultFilePath

End Sub

Figure 20-7 shows one of the documents created by the MakeMemos procedure.

This workbook, named make memos.xlsm, is available on the companion CD-ROM.

Creating this macro involved several steps. I started by recording a macro in Word. I recorded my actions while creating a new document, adding and formatting some text, and saving the file. That Word macro provided the information that I needed about the appropriate properties and methods. I then copied the macro to an Excel module. Notice that I used With-End With. I added a dot before each instruction between With and End With. For example, the original Word macro contained (among others) the following instruction:

Documents.Add

I modified the macro as follows:

With WordApp

.Documents.Add

‘ more instructions here

End With

The macro that I recorded in Word used a few of Word's built-in constants. Because this example uses late binding, I had to substitute actual values for those constants. I was able to learn the values by using the Immediate window in Word's VBE.

FIGURE 20-7: An Excel procedure created this Word document.

Controlling Excel from another application

You can, of course, also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you may want to perform some calculations in Excel and return the result to a Word document.

You can create any of the following Excel objects with the adjacent functions:

• Application object: CreateObject(“Excel.Application”)

• Workbook object: CreateObject(“Excel.Sheet”)

• Chart object: CreateObject(“Excel.Chart”)

The code that follows is a procedure that is located in a VBA module in a Word 2010 document. This procedure creates an Excel Worksheet object (whose moniker is “Excel.Sheet”) from an existing workbook and pastes it into the Word file.

Sub MakeLoanTable()

Dim XLSheet As Object

Dim LoanAmt

Dim Wbook As String

‘ Prompt for values

LoanAmt = InputBox(“Loan Amount?”)

If LoanAmt = “” Then Exit Sub

‘ Clear the document

ThisDocument.Content.Delete

‘ Create Sheet object

Wbook = ThisDocument.Path & “mortgagecalcs.xlsx”

Set XLSheet = GetObject(Wbook, “Excel.Sheet”).ActiveSheet

‘ Put values in sheet

XLSheet.Range(“LoanAmount”) = LoanAmt

XLSheet.Calculate

‘ Insert page heading

Selection.Style = “Title”

Selection.TypeText “Loan Amount: “ & _

Format(LoanAmt, “$#,##0”)

Selection.TypeParagraph

Selection.TypeParagraph

‘ Copy data from sheet & paste to document

XLSheet.Range(“DataTable”).Copy

Selection.Paste

Selection.TypeParagraph

Selection.TypeParagraph

‘ Copy chart and paste to document

XLSheet.ChartObjects(1).Copy

Selection.PasteSpecial _

Link:=False, _

DataType:=wdPasteMetafilePicture, _

Placement:=wdInLine

‘ Kill the object

Set XLSheet = Nothing

End Sub

This example is available on the companion CD-ROM. The Word document is named automate excel.docm, and the Excel workbook is named mortgagecalcs.xlsx. When you open the Word file, execute the MakeLoanTable macro by choosing Insert⇒Mortgage⇒Get Mortgage Amount.

The Excel worksheet used by this Word procedure is shown in Figure 20-8. The MakeLoanTable procedure prompts the user for a loan amount and inserts the value into cell C7 (named LoanAmount).

FIGURE 20-8: a VBA procedure in Word uses this worksheet.

Recalculating the worksheet updates a data table in range F2:I12 (named DataTable) and also updates the chart. The DataTable range and the chart are then copied from the Excel object and pasted into the Word document. The result is shown in Figure 20-9.

FIGURE 20-9: The Word VBA procedure uses Excel to create this document.

Sending Personalized E-Mail via Outlook

The example in this section demonstrates automation with Microsoft Outlook.

Figure 20-10 shows a worksheet that contains data used in the e-mail messages: name, e-mail address, and bonus amount. The SendMail procedure loops through the rows in the worksheet, retrieves the data, and creates an individualized message (stored in the Msg variable).

FIGURE 20-10: This information is used in the Outlook e-mail messages.

Sub SendEmail()

‘Uses early binding

‘Requires a reference to the Outlook Object Library

Dim OutlookApp As Outlook.Application

Dim MItem As Outlook.MailItem

Dim cell As Range

Dim Subj As String

Dim EmailAddr As String

Dim Recipient As String

Dim Bonus As String

Dim Msg As String

‘Create Outlook object

Set OutlookApp = New Outlook.Application

‘Loop through the rows

For Each cell In Columns(“B”).Cells.SpecialCells(xlCellTypeConstants)

If cell.Value Like “*@*” Then

‘Get the data

Subj = “Your Annual Bonus”

Recipient = cell.Offset(0, -1).Value

EmailAddr = cell.Value

Bonus = Format(cell.Offset(0, 1).Value, “$0,000.”)

‘Compose message

Msg = “Dear “ & Recipient & vbCrLf & vbCrLf

Msg = Msg & “I am pleased to inform you that your annual bonus is “

Msg = Msg & Bonus & vbCrLf & vbCrLf

Msg = Msg & “William Rose” & vbCrLf

Msg = Msg & “President”

‘Create Mail Item and send it

Set MItem = OutlookApp.CreateItem(olMailItem)

With MItem

.To = EmailAddr

.Subject = Subj

.Body = Msg

.Send

End With

End If

Next

End Sub

Figure 20-11 shows one of the e-mail messages displayed in Outlook.

FIGURE 20-11: An Outlook e-mail message created by Excel.

This example uses early binding, so it requires a reference to the Outlook Object Library. Notice that two objects are involved: an Outlook object and a MailItem object. The Outlook object is created with this statement:

Set OutlookApp = New Outlook.Application

The MailItem object is created with this statement:

Set MItem = OutlookApp.CreateItem(olMailItem)

The code sets the To, Subject, and Body properties and then uses the Send method to send each message.

To save the messages in your Draft folder (rather than send them), use the Save method instead of the Send method. This change is particularly useful while you're testing and debugging the code.

Unless you've changed your security settings, you'll probably see the dialog box shown in Figure 20-12 for each message that's sent. To eliminate this dialog box, activate Outlook, choose Office⇒Outlook Options⇒Trust Center, and click the Trust Center Settings button. In the Trust Center dialog box, click the Programmatic Access tab and choose the option labeled Never Warn Me about Suspicious Activity (Not Recommended). But do this at your own risk.

FIGURE 20-12: Using Excel to send e-mail via Outlook normally causes a warning message from Outlook.

This example, named personalized email - outlook.xlsm, is available on the companion CD-ROM. You must have Microsoft Outlook installed. The CD also contains a slightly modified version that uses late binding: personalized email - outlook (late binding).xlsm.

Subsequent sections in this chapter describe other ways of sending e-mail through Excel. See “Sending E-Mail Attachments from Excel” and “Using SendKeys.”

Sending E-Mail Attachments from Excel

As you probably know, Excel has the ability to send a workbook via e-mail as an attachment. And, of course, you can use VBA to automate these types of tasks. The following procedure uses the SendMail method to send the active workbook (as an attachment) to [email protected], using the default e-mail client (if any). The e-mail message has the subject My Workbook.

Sub SendWorkbook()

ActiveWorkbook.SendMail “[email protected]”, “My Workbook”

End Sub

The SendMail method uses the default e-mail client.

If you'd like to e-mail only a single sheet from a workbook, you need to copy the sheet to a new (temporary) workbook, send that workbook as an attachment, and then close the temporary file. Here's an example that sends Sheet1 from the active workbook, attached to an e-mail with the subject, My Workbook. Note that the copied sheet becomes the active workbook.

Sub Sendasheet()

ActiveWorkbook.Worksheets(“sheet1”).Copy

ActiveWorkbook.SendMail “[email protected]”, “My Workbook”

ActiveWorkbook.Close False

End Sub

In the preceding example, the file will have the default workbook name (for example, Book2.xlsx). If you'd like to give the single-sheet workbook attachment a more meaningful name, you need to save the temporary workbook and then delete it after it's sent. The following procedure saves Sheet1 to a file named my file.xlsx. After sending this temporary workbook as an e-mail attachment, the code uses VBA's Kill statement to delete the file.

Sub SendOneSheet()

Dim Filename As String

Filename = “my file.xlsx”

ActiveWorkbook.Worksheets(“sheet1”).Copy

ActiveWorkbook.SaveAs Filename

ActiveWorkbook.SendMail “[email protected]”, “My Workbook”

ActiveWorkbook.Close False

Kill Filename

End Sub

Unfortunately, Excel doesn't provide a way to automate saving a workbook as a PDF file and sending it as an attachment. You can, however, automate part of the process. The following SendSheetAsPDF procedure saves the active sheet as a PDF file and then displays the compose message window from your default e-mail client (with the PDF file attached) so that you can fill in the recipient's name and click Send:

Sub SendSheetAsPDF()

CommandBars.ExecuteMso (“FileEmailAsPdfEmailAttachment”)

End Sub

When Excel is lacking powers, it's time to call on Outlook. The procedure that follows saves the active workbook as a PDF file and automates Outlook to create an e-mail message with the PDF file as an attachment.

Sub SendAsPDF()

‘ Uses early binding

‘ Requires a reference to the Outlook Object Library

Dim OutlookApp As Outlook.Application

Dim MItem As Object

Dim Recipient As String, Subj As String

Dim Msg As String, Fname As String

‘ Message details

Recipient = “[email protected]”

Subj = “Sales figures”

Msg = “Hey boss, here's the PDF file you wanted.”

Msg = Msg & vbNewLine & vbNewLine & “-Frank”

Fname = Application.DefaultFilePath & “” & _

ActiveWorkbook.Name & “.pdf”

‘ Create the attachment

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:=Fname

‘ Create Outlook object

Set OutlookApp = New Outlook.Application

‘ Create Mail Item and send it

Set MItem = OutlookApp.CreateItem(olMailItem)

With MItem

.To = Recipient

.Subject = Subj

.Body = Msg

.Attachments.Add Fname

.Save ‘to Drafts folder

‘.Send

End With

Set OutlookApp = Nothing

‘ Delete the file

Kill Fname

End Sub

This example, named send pdf via outlook.xlsm, is available on the companion CD-ROM.

Using SendKeys

Not all applications support Automation. In some cases, you can still control some aspects of the application even if it doesn't support Automation. You can use Excel's SendKeys method to send keystrokes to an application, simulating actions that a live human might perform.

Although using the SendKeys method may seem like a good solution, you'll find that it can be very tricky and not completely reliable. In fact, it may not work at all. A potential problem is that it relies on a specific user interface. If a later version of the program that you're sending keystrokes to has a different user interface, your application might no longer work. Consequently, you should use SendKeys only as a last resort.

Following is a very simple example. This procedure runs the Windows Calculator program and displays its Scientific mode: That is, it executes the View⇒Scientific command.

Sub TestKeys()

Shell “calc.Exe”, vbNormalFocus

Application.SendKeys “%vs”

End Sub

In this example, the code sends out Alt+V (the percent sign represents the Alt key) followed by S. SendKeys is documented in the Help system, which describes how to send nonstandard keystrokes, such as Alt and Ctrl key combinations.

As I was finalizing this chapter, I tried the TestKeys procedure on a system with Windows 7 installed. Although the Windows 7 calculator uses the same menu accelerator key, the procedure did not work. After a bit of research, I learned that Windows 7 supports SendKeys only if User Account Control (a security feature) is turned off. That's a good example of why you should use SendKeys only as a last resort.