Automating Word, Outlook and other Office apps

Drive other applications with CreateObject and GetObject, choose early or late binding, and release object references properly.

Early versus late binding

Option Explicit

' LATE BINDING: no reference needed, resolved at run time.
' Slower, no IntelliSense, but it works on any machine that has the app,
' and it does not break when a different Office version is installed.
Sub LateBound()
    Dim app As Object
    Set app = CreateObject("Word.Application")
    app.Visible = False
    Debug.Print app.Version
    app.Quit
    Set app = Nothing
End Sub

' EARLY BINDING: add the reference in Tools, References first.
' Faster, checked at compile time, IntelliSense works.
' The cost is that the reference is to a specific version and can break
' on a machine with a different one, and a MISSING reference shows as
' a compile error the user cannot fix.
' Sub EarlyBound()
'     Dim app As Word.Application
'     Set app = New Word.Application
'     app.Visible = False
'     app.Quit
'     Set app = Nothing
' End Sub

' GetObject attaches to something that is already running.
Sub AttachOrCreate()
    Dim app As Object
    On Error Resume Next
    Set app = GetObject(, "Word.Application")
    On Error GoTo 0
    If app Is Nothing Then Set app = CreateObject("Word.Application")
    Debug.Print app.Name
    app.Quit
    Set app = Nothing
End Sub

Prefer late binding in anything you distribute. A missing type library reference produces a compile error that names a GUID, which the user cannot diagnose, while a missing ProgID produces a run-time error your own handler can explain.

Generating Word documents

Sub MakeLetter()
    Dim word As Object, doc As Object
    Set word = CreateObject("Word.Application")
    word.Visible = False
    word.DisplayAlerts = 0                 ' do not prompt to overwrite

    On Error GoTo CleanFail

    Set doc = word.Documents.Add
    doc.Content.Text = "Dear customer," & vbCrLf & vbCrLf

    Dim para As Object
    Set para = doc.Content.Paragraphs.Add
    para.Range.Text = "Your order has shipped."
    para.Range.Font.Bold = True
    para.Range.InsertParagraphAfter

    ' a table built row by row through the COM interface
    Dim tbl As Object
    Set tbl = doc.Tables.Add(doc.Range, 3, 2)
    tbl.Cell(1, 1).Range.Text = "Item"
    tbl.Cell(1, 2).Range.Text = "Qty"
    tbl.Cell(2, 1).Range.Text = "ABC-1"
    tbl.Cell(2, 2).Range.Text = "2"
    tbl.Borders.Enable = True

    ' save as PDF without needing Word to be visible
    Dim outPath As String
    outPath = ThisWorkbook.Path & "\letter.pdf"
    doc.ExportAsFixedFormat outPath, 17     ' 17 = wdExportFormatPDF

CleanExit:
    doc.Close 0                             ' 0 = do not save
    word.Quit
    Set doc = Nothing
    Set word = Nothing
    Exit Sub

CleanFail:
    Debug.Print "Word failed: " & Err.Description
    Resume CleanExit
End Sub

' A template with placeholders is faster and easier to maintain than
' building the layout in code: open the .dotx and replace the markers.
Sub FromTemplate()
    Dim word As Object, doc As Object
    Set word = CreateObject("Word.Application")
    word.Visible = False

    Set doc = word.Documents.Add(ThisWorkbook.Path & "\letter.dotx")
    doc.Content.Find.Execute FindText:="<<NAME>>", ReplaceWith:="ada", _
                             Replace:=2      ' 2 = wdReplaceAll
    doc.ExportAsFixedFormat ThisWorkbook.Path & "\out.pdf", 17
    doc.Close 0
    word.Quit
    Set doc = Nothing
    Set word = Nothing
End Sub

• word.Visible = False is what makes the automation usable in a batch. A visible instance flashes windows and steals focus, which breaks any other script the user is running.
• word.DisplayAlerts = 0 stops the modal prompt that would otherwise block an unattended run forever.
• A document opened for automation is not tracked by Word's normal recovery. Save early and explicitly, because a crash in your macro loses the document.
• Building a layout through COM properties is slow and brittle. A template with named placeholders keeps the design with the designer.

Outlook: mail, meetings and cleaning up

' IMPORTANT: Outlook macros must live in Outlook's own VBA project.
' If you drive Outlook from Excel, Outlook will raise a security prompt
' unless the user has granted programmatic access, and some corporate
' policies block it entirely (the object model guard).

Sub SendMailFromOutlook()
    Dim app As Object, mail As Object
    Set app = CreateObject("Outlook.Application")     ' early binding: New Outlook.Application
    Set mail = app.CreateItem(0)                      ' 0 = olMailItem

    On Error GoTo CleanFail

    With mail
        .To = "[email protected]"
        .CC = "[email protected]"
        .Subject = "Order shipped"
        .Body = "Your order ABC-1 has shipped." & vbCrLf & "Regards,"
        .Attachments.Add ThisWorkbook.FullName
        ' .Display shows it for the user to check; .Send sends immediately
        .Display
        ' .Send
    End With

CleanExit:
    Set mail = Nothing
    Set app = Nothing
    Exit Sub
CleanFail:
    Debug.Print "Outlook failed: " & Err.Description
    Resume CleanExit
End Sub

' Creating a meeting request
Sub AddMeeting()
    Dim app As Object, appt As Object
    Set app = CreateObject("Outlook.Application")
    Set appt = app.CreateItem(1)                      ' 1 = olAppointmentItem
    With appt
        .Subject = "Review"
        .Start = DateAdd("d", 1, DateSerial(2026, 9, 18) + TimeSerial(10, 0, 0))
        .Duration = 60
        .Location = "Room 2"
        .MeetingStatus = 1                            ' 1 = a meeting request
        .RequiredAttendees = "[email protected]"
        .Display
    End With
    Set appt = Nothing
    Set app = Nothing
End Sub

' TRAP: a message parked in Outlook's Outbox by a previous failed run is
' still there and will send on the next synchronisation. Check and clean:
Sub ClearOutbox()
    Dim ns As Object, outbox As Object, item As Object
    Dim app As Object
    Set app = CreateObject("Outlook.Application")
    Set ns = app.GetNamespace("MAPI")
    Set outbox = ns.GetDefaultFolder(4)               ' 4 = olFolderOutbox
    For Each item In outbox.Items
        Debug.Print "stuck: " & item.Subject
        ' item.Delete
    Next item
    Set item = Nothing
    Set outbox = Nothing
    Set ns = Nothing
    Set app = Nothing
End Sub

FAQ

Related

Files and folders: FileSystemObject and TextStream Distributing macros: add-ins, signatures and security

Last refreshed 2026-09-18.