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.
Sub SendReport()
Dim olApp As Object, mail As Object
Set olApp = CreateObject("Outlook.Application")
Set mail = olApp.CreateItem(0) ' 0 = olMailItem
With mail
.To = "[email protected]"
.CC = "[email protected]"
.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 SubGenerating 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.
Practice what you learned
1. What does CreateObject("Outlook.Application") do that GetObject(, "Outlook.Application") does not?
2. In late-bound Outlook code, why write CreateItem(0) instead of CreateItem(olMailItem)?
3. Which MailItem property sets rich, formatted email content?
4. What is a reliable way to inject values into a Word document from VBA?
5. Why should you call olApp.Quit and set objects to Nothing after automation?
Was this page helpful?
You May Also Like
VBA and ADO Database Access
Using ActiveX Data Objects (ADO) from VBA to connect to databases, run parameterized queries, and read results into a worksheet or recordset.
Reading and Writing Cell Data
How to read from and write to worksheet cells in VBA using the Range and Cells objects, and how to move data efficiently between the sheet and VBA arrays.
Dictionaries and Collections in VBA
Comparing VBA's built-in Collection with the Scripting.Dictionary for storing keyed data, and choosing the right one for lookups, de-duplication, and counting.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics