Chapter 18: 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

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 18-1.

Figure 18-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 that has 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 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 previous examples are available on the book's website. The filename is start calculator.xlsm.

Another way to launch an app is to create a hyperlink in a cell (VBA not required). For example, this formula creates a hyperlink in a cell that, when clicked, runs the Windows Calculator program:

=HYPERLINK(“C:WindowsSystem32calc.exe”,”Windows Calculator”)

You need to make sure that the link points to the correct location. And you'll probably get at least one security warning when you click the link. This technique works also for files, and loads the file into the default application for the file type. For example, clicking the hyperlink created by the following formula loads the file into the default app for text files:

=HYPERLINK(“C:filesdata.txt”,”Open the data file”)

Displaying a folder window

The Shell function is handy also if you need to display a particular directory using Windows Explorer. For example, the statement that follows displays the folder of the active workbook (but only if the workbook has been saved):

If ActiveWorkbook.Path <> “” Then _

  Shell “explorer.exe “ & ActiveWorkbook.Path, vbNormalFocus

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 or later):

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, 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 use this technique also 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 book's website 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 preceding 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 (in this case, the Windows Calculator) if it's already running. The argument for AppActivate is the caption of the application's title bar. If the AppActivate statement generates an error, Calculator is not running and 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 book's website. 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, the application is activated. The other constants available for this method are

• xlMicrosoftPowerPoint

• xlMicrosoftMail (activates Outlook)

• xlMicrosoftAccess

• xlMicrosoftFoxPro

• xlMicrosoftProject

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 18-2.

Figure 18-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

where:

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

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

• t: The number of the tab (for multitabbed applets)

A workbook that displays 12 additional Control Panel applets, depicted in Figure 18-3, is available on the book's website. The filename is control panel dialogs.xlsm.

Figure 18-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 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 Visual Basic Editor (VBE) to display the dialog box shown in Figure 18-4. Then put a check mark next to the object library you need to reference.

Figure 18-4: Adding a reference to an object library file.

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

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

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), Microsoft Word 14.0 Object Library (for Word 2010), or Microsoft Word 15.0 Object Library (for Word 2013). 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 is usually 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.

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 2010:

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

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 2013 components. If the user replaces Word 2010 with Word 2013, CreateObject(“Word.Application”) will continue to work properly, referring to the new application. if Word 2013 is removed, however, CreateObject(“Word.Application.15”), which uses the alternate version-specific name for Word 2013, 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 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 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 2007, Word 2010, and Word 2013, you should use the type library for Word 2007 to maintain compatibility with all three versions. With this approach, 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 be programmed also 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

Figure 18-6 shows how Auto List Members works with early binding. When using late binding, you get no assistance when writing code.

Figure 18-6: Using early binding enables Auto List Members.

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

Figure 18-7: 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 18-8 shows one of the documents created by the MakeMemos procedure.

Figure 18-8: an Excel procedure created this Word document.

This workbook, named make memos.xlsm, is available on the book's website.

Creating this macro involved several steps. I started by recording my actions in Word 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. Note that I used With-End With, adding 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 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.

Controlling Excel from another application

You can 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 in a VBA module in a Word 2013 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 book's website. 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 18-9. The MakeLoanTable procedure prompts the user for a loan amount and inserts the value into cell C7 (named LoanAmount).

Figure 18-9: 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 18-10.

Figure 18-10: 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 18-11 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 18-11: 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,  EmailAddr As String,  Recipient As String

  Dim Bonus As String, 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

        .Save

      End With

    End If

  Next

End Sub

Figure 18-12 shows one of the e-mail messages displayed in Outlook.

Figure 18-12: An Outlook e-mail message created by Excel.

This example uses early binding, so it requires a reference to the Outlook Object Library. Note 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.

The example uses the Save method, which places the messages in Outlook's Draft folder. To send the messages immediately, use the Send method. Using the Save method is particularly useful while you're testing and debugging the code.

This example, named personalized email - outlook.xlsm, is available on the book's website. You must have Microsoft Outlook installed. A slightly modified version that uses late binding is also available in personalized email - outlook (late binding).xlsm.

Sending E-Mail Attachments from Excel

As you probably know, Excel can send a workbook by e-mail as an attachment. And, of course, you can use VBA to automate this type of task. 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 the VBA 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 book's website.