Automating Outlook and Word from VBA

Analogy categoryAnalogies

Cross-Application Automation

Because every Office app exposes a COM object model, VBA in one application can control another. From Excel you obtain the other app's Application object — either by early binding (a reference to the Microsoft Outlook or Word Object Library plus New Outlook.Application) or late binding (CreateObject("Outlook.Application")). With Outlook you create MailItem objects; with Word you open Document objects. GetObject can attach to an already-running instance instead of launching a new one.

🏏

Cricket analogy: Cross-app automation is a captain signalling the twelfth man to fetch gear: Excel is the captain and Outlook the runner. CreateObject calls up a fresh player, while GetObject waves over one already on the field.

Sending Email With Outlook

The typical email flow is: get the Outlook.Application, call .CreateItem(olMailItem) to make a MailItem, set To, CC, Subject, and either Body (plain text) or HTMLBody (rich HTML), optionally .Attachments.Add a file, then call .Send to dispatch or .Display to open the draft for review. Note that olMailItem equals 0; with late binding you must supply the numeric constant because the named enum isn't available without a reference.

🏏

Cricket analogy: Composing a MailItem is setting your field before the ball: To and CC are the fielders, Subject is the plan. .Display is a practice ball in the nets; .Send is the real delivery to the batsman.

vba

Sub SendReport()
    Dim olApp As Object, mail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.CreateItem(0)   ' 0 = olMailItem

    With mail
        .To = "team@example.com"
        .CC = "manager@example.com"
        .Subject = "Monthly Sales Report"
        .HTMLBody = "<p>Hi team,</p><p>Please find the report attached.</p>"
        .Attachments.Add ThisWorkbook.FullName
        .Display   ' use .Send to dispatch without review
    End With

    Set mail = Nothing: Set olApp = Nothing
End Sub

Generating Word Documents

For Word, get the Word.Application, set .Visible = True if you want to watch it, and open a template or blank doc with Documents.Add or Documents.Open. A robust mail-merge-style pattern is to design a .dotx template containing bookmarks or content controls, then in VBA loop through doc.Bookmarks("CustomerName").Range.Text = ... to inject values. You can also drive the Selection object to type text, apply styles, and insert tables, finishing with doc.SaveAs2 to a chosen path and format.

🏏

Cricket analogy: A Word template with bookmarks is a team sheet with named blanks; filling bookmarks is writing each player into their slot, like a scorer completing the batting order before the innings.

GetObject(, "Outlook.Application") attaches to a running Outlook instance and errors if none exists; CreateObject always starts (or reuses) an instance. A common robust pattern wraps GetObject in error handling and falls back to CreateObject.

When automating another app, quit and release the objects you created (olApp.Quit / wordApp.Quit, then Set = Nothing), or invisible instances linger in Task Manager and hold file locks. Also, using .Send bypasses the security prompt only in trusted setups; on locked-down machines Outlook may still warn or block programmatic sends.

Any Office app can drive another through its COM object model via early or late binding.
CreateObject launches or reuses an instance; GetObject attaches to one already running.
Outlook email uses CreateItem(olMailItem/0), then To/CC/Subject/Body or HTMLBody, Attachments.Add, and Send or Display.
With late binding you must use numeric constants (e.g. 0 for olMailItem) since named enums are unavailable.
Word automation opens templates and fills bookmarks or content controls, then SaveAs2 to output.
Always Quit and release automated app objects to avoid orphaned instances and file locks.