<%dim crumb crumb="Mail Merge" Crumb2="VBA Mail Merge Method" crumb1="Access Mail Merge" %> VBA Mail Merge
Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

<%if len(crumb2)>2 then response.write crumb2 else response.write ">
<% response.write crumb1 %>

Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions

Visual Basic Function Examples

Access Mail Merge Example

Using Access Visual Basic to perform Microsoft Word mail merge give you the ultimate control and alleviates the need to jump to Microsoft Word to perform the mail merge. To accomplish this task you'll need to create a Word template document with bookmarks inserted in the database field locations.  These bookmarks will represent the variable part of the document. Next create an Access form with a button called Envelope.  And, create an on click event as shown below.  You'll need to customize the PathDocu line to match you particular setup.

Private Sub Enveloppe_Click()

Dim MyWord As Word.Application
Dim PathDocu As String

If Me.NomPers <> "" And Me.Prnom <> "" Then
    Set MyWord = New Word.Application
    PathDocu = "C:\Documents and Settings\BENOIT\Mes                           documents\Word\IFFC\BaseDeDonne\Documents\"

With MyWord
    .Documents.Open (PathDocu & "Env22x10.doc")
    '.ActiveDocument.Bookmarks("date").Range.Text = & _
        Format(Date, "dd mmmm yyyy")
    .ActiveDocument.Bookmarks("nom").Range.Text = Me.NomPers
    .ActiveDocument.Bookmarks("prnom").Range.Text = Me.Pr�nom
    If Me.Adresse <> "" Then
        MyWord.ActiveDocument.Bookmarks("adresse").Range.Text = & _
    If Me.Adresse2 <> "" Then
        MyWord.ActiveDocument.Bookmarks("adresse2").Range.Text & _
        = Me.Adresse2
    If Me.CodePostal <> "" Then
        MyWord.ActiveDocument.Bookmarks("codepostal").Range.Text &_
        = Me.CodePostal
    If Me.Ville <> "" Then MyWord.ActiveDocument.Bookmarks("ville").Range.Text =Me.Ville
    If Me.Pays <> "" Then MyWord.ActiveDocument.Bookmarks("pays").Range.Text = Me.Pays
' si vous dsirez utiliser plusieurs fois les mmes donnes, vous devez
' crer des signets diff�rents

.Visible = True
End With
Set MyWord = Nothing
End If
On Error GoTo Err_Enveloppe_Click

Exit Sub

MsgBox Err.Description
Resume Exit_Enveloppe_Click

End Sub

Contributed by Benoit Mann

Access Mail Merge

Using VBA Only to Create a Mail Merge Report

Access mail merge to a database or spreadsheet is a very common in the RFPs we receive.  For one or two page letters we recommend using Microsoft Access Visual Basic totally - bypassing the activation of Microsoft Word and performing the merge operation directly in Access.

The end result of using Access directly to mail merge is that you can provide a much simpler user interface and don't have the complications of linking to the Access database from Word and don't have problems having to filter the records or trying to figure out table names, query names and field names to place into the mail merge Word document. Steps are fairly simple:

1) Create a Microsoft Word document (two pages max) and format it exactly as you want the resulting letter or document to appear.

2) In Word pick the Edit menu and then choose 'Select All', then Edit again and select Copy.  This will place of copy of the document into your clipboard

3) Open up Microsoft Access and create a new blank report.

4) With the Edit menu choose Paste.  This will place a copy of the Word document into the Access report.  The document will retain the formatting and other characteristics since it is still a Word Document.  In the Access report the document will be an OLE field and will likely be called OLE1

5) You can then assign the record source for the report using either a table or query.

6) After the record source is defined you then can select the database fields and simply drop them on top of the text in the Word Document.  Setting the Background field property to Normal will completely hide the Word document text underneath the database field.

7) After aligning and sizing the database fields appropriately you are ready to run the report.

Usually you want to filter to select some subset of your database table.  We use a little parameter form and filter a query or the report itself.

Use the Site Search  menu choice at the top of the page to locate examples of Using Form Fields to Filter Reports.  To see an advanced technique try search for Using Global Variable as Parameters.


Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>

Microsoft Access 2007, 2010, 2013 & 2016