Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท Data Import, Export and Linking

Automating Excel, Word and Outlook from Access

Control other Office programs from Access VBA. Learn late and early binding, creating an Excel report, filling a Word template, sending Outlook email and cleaning up objects.

Access stores and organizes data; the rest of Office presents it. Automation means one program driving another through its object model: Access VBA can create an Excel workbook full of query results, fill a Word letter template for each customer, or send an Outlook email with a PDF invoice attached. This is how Access reports become polished documents without anyone copying and pasting.

Automation needs the other program installed on the same PC, so it works on desktops but not in web or server contexts. The code blocks below are VBA for Access; the stand-ins on this page show the data preparation, which is the part that is the same everywhere.

Early and late binding

To control Excel you need a reference to its object library (Tools > References > Microsoft Excel xx.0 Object Library). With the reference you declare typed variables (early binding) and get IntelliSense and constants such as xlCenter. Without it you use CreateObject (late binding) and declare variables As Object:

VBA
' Early binding: needs the reference, faster, with IntelliSense
Dim xl As Excel.Application
Set xl = New Excel.Application

' Late binding: no reference, works with whichever version is installed
Dim xl As Object
Set xl = CreateObject("Excel.Application")

A good workflow: develop with early binding, then switch to late binding before deploying, replacing named constants with their numeric values. This avoids "missing reference" errors on PCs with a different Office version.

Excel: a report from a query

VBA
Public Sub ExportToExcel()
    Dim xl As Object, wb As Object, ws As Object
    Dim rs As DAO.Recordset
    Dim col As Long

    On Error GoTo ErrHandler
    Set rs = CurrentDb.OpenRecordset("qryOrdersForAccounting", dbOpenSnapshot)
    If rs.EOF Then MsgBox "Nothing to export.": GoTo CleanExit

    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Add
    Set ws = wb.Worksheets(1)
    ws.Name = "Orders"

    For col = 0 To rs.Fields.Count - 1          ' headers
        ws.Cells(1, col + 1).Value = rs.Fields(col).Name
        ws.Cells(1, col + 1).Font.Bold = True
    Next col
    ws.Range("A2").CopyFromRecordset rs         ' all rows in one call (fast)
    ws.Columns.AutoFit
    ws.Range("A1").AutoFilter

    xl.Visible = True
    wb.SaveAs Environ("USERPROFILE") & "\Documents\Orders.xlsx"

CleanExit:
    On Error Resume Next
    rs.Close
    Set ws = Nothing: Set wb = Nothing: Set xl = Nothing
    Exit Sub
ErrHandler:
    MsgBox "Excel export failed: " & Err.Description
    If Not xl Is Nothing Then xl.Quit
    Resume CleanExit
End Sub

CopyFromRecordset pastes a whole recordset in one call; writing cell by cell in a loop is hundreds of times slower. Two rules keep Excel from lingering as an invisible ghost process: release every object (Set x = Nothing) and call xl.Quit if you do not leave it open for the user.

Often a summary is the product, not the raw rows. Prepare it in SQL first, then send the already aggregated result:

SQL Stand-in: summarize in SQL before sending to Excel

Press Run (or Ctrl + Enter) to see the result here.

Word: filling a template

Create a Word template with bookmarks (Insert > Bookmark) named CustomerName, OrderTotal and so on, then fill them from the current record:

VBA
Public Sub CreateLetter(lngCustomerId As Long)
    Dim wd As Object, doc As Object, rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT Name, Address, Balance FROM Customers " & _
                                     "WHERE CustomerID = " & lngCustomerId, dbOpenSnapshot)
    If rs.EOF Then Exit Sub

    Set wd = CreateObject("Word.Application")
    Set doc = wd.Documents.Add(CurrentProject.Path & "\Templates\Letter.dotx")

    doc.Bookmarks("CustomerName").Range.Text = rs!Name
    doc.Bookmarks("Address").Range.Text = Nz(rs!Address, "")
    doc.Bookmarks("Balance").Range.Text = Format(rs!Balance, "Currency")

    wd.Visible = True
    doc.SaveAs2 CurrentProject.Path & "\Letters\Letter-" & lngCustomerId & ".docx"
    rs.Close
End Sub

For one letter per customer from a list, Mail Merge is built in: use External Data > Export > Word Merge to create or link a merge document, and Word reads the query as its data source. Use VBA when you need per-record logic or file names.

Outlook: sending email

VBA
Public Sub EmailInvoice(strTo As String, strPdfPath As String)
    Dim ol As Object, mail As Object
    Set ol = CreateObject("Outlook.Application")
    Set mail = ol.CreateItem(0)          ' 0 = olMailItem
    With mail
        .To = strTo
        .Subject = "Your invoice"
        .Body = "Hello," & vbCrLf & vbCrLf & "Your invoice is attached." & vbCrLf & "Thank you."
        .Attachments.Add strPdfPath
        .Display                         ' shows the message; use .Send to send directly
    End With
End Sub

Use .Display while testing and .Send only when you are sure: a loop with .Send can mail hundreds of customers in seconds, with no undo. Newer versions of Outlook may show a security prompt for programmatic sending; consider DoCmd.SendObject for simple cases (it emails an Access object using the default mail program) or the built-in CDO/Graph API for server-side sending.

The mail body for a list of customers is typically built from a query. The stand-in below shows how to produce one message per customer using SQL, so your VBA loop only formats and sends:

SQL Stand-in: one message per customer from SQL

Press Run (or Ctrl + Enter) to see the result here.

Notice that the query already excludes customers without an email address; that kind of check belongs in the data, not scattered through VBA.

Housekeeping rules for automation

  1. Always release objects. A missed Quit leaves invisible EXCEL.EXE processes in memory.
  2. Make visible only at the end, and turn off xl.ScreenUpdating for big jobs.
  3. Use late binding in distributed applications.
  4. Handle errors: the user may close the window mid-run or the other program may be missing; see error handling.
  5. Never hard-code paths; build them from CurrentProject.Path or Environ.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is late binding?

    Late binding resolves the object at run time, so it works with any installed Office version.
  2. 2. Which Excel method pastes an entire DAO recordset in one call?

    CopyFromRecordset is far faster than writing cell by cell.
  3. 3. Why should you use .Display instead of .Send while testing Outlook code?

    A mistaken loop with .Send could mail many customers immediately.
  4. 4. How do you fill specific places in a Word template from VBA?

    Bookmarks mark spots in the template; code sets doc.Bookmarks(name).Range.Text.

Summary

  • Automation drives Excel, Word and Outlook through their object models from Access VBA.
  • Use early binding to develop and late binding (CreateObject) to deploy.
  • CopyFromRecordset for Excel, bookmarks for Word templates, .Display before .Send for Outlook.
  • Prepare and summarize data in SQL first, release every object and handle errors.

Next: The database design process