Access 2007 mail merge to word

Posted on 2012-03-13
Last Modified: 2012-03-23
Thank you all for helping

I have a Form, after user enters in (textbox's) information user then opens a word document by clicking on an open button.

I want the textbox fields to mail merge into the opened word document.  So I want the current record from access to mail merge into the word document.

Textbox fields :

I will need to know how to set this up in Access (VBA) and also within Word?

Thank you!
Question by:Amour22015
  • 3
  • 3
  • 3
  • +1
LVL 76

Expert Comment

ID: 37717804
Mail Merge is designed for 'batch' processing, so it needs a table or a recordset as input.

It works by designing a Main document in Word. There is a wizard which walks you through the process. An early step is to specify the datasource (e.g. an Access table or stored query). There is then a list of fields from the table to insert in the Main document. The document is then saved for reuse later (like a template).

So for your purpose, you could have a stored query in your database, modify it to choose only the current record and then initiate the Word mail merge. This would need some VBA code, of course.

Alternatively you could have a Word template with form fields, and then use the VBA code to create a new document and to fill the form fields one-by-one. This would not be a Mail Merge in the Word sense, but would achieve the same end.

How are your coding skills?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37718151

Just so you know (if your needs from Mail Merge are not that complex), you can simulate a Mail Merge in Access fairly easily.
Sample attached

Author Comment

ID: 37720564

So I need some step-by-step on how to accomplish what I am looking for?

I would like to set this up so all I have to do is click on a button and it: opens, merges, and brings up email automaticaly.

Right now for just this post I am dealing with Mail Merges, but I also have to look into the fact that I want to email.

keep in mind that I only want this to happen from the form (all textbox Me.txtbox)

So at this point I can do 2 things from Access 2007?
1.) I can use mailmerge to Word and I think I can attach to the outgoing email on outlook?
    in this case I would have to set up some queries?
2.) I can create a report in Access (in this case I would not need mailmerge).  Set it up to only send the one record from the Form (me.txtbox) and use something like:
DoCmd.SendObject acSendReport, "Reportname", "Rich Text Format" , , , , "Request ", "bodyOfTextToSend", True

Thank you!
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 37721011
<I can create a report in Access... >
Or did you mean:
<Can I create a report in Access... >

Sure, but if the question is now shifting to "Email" then it mecomes A LOT more complex....

a very simple sample is attached, see the notes in the code...
LVL 76

Accepted Solution

GrahamSkan earned 200 total points
ID: 37721220
Part 1. Setting up the mail merge.
In Access ensure that you have a table or a query (no parameters) that contains all the data, including the email addresses of the potential recipients.

In Word, Mailings tab, click Start Mail Merge, and choose Step-by-Step Mail Merge wizard. This open a task pane on the right where you can choose E-mail message, then  a choice of document. In step three you can browse to your database, and the select the query or table.

Step four you can add or modify the text and insert the fields that you need. Next step is a preview. You can go backwards and forwards between individual records and see the results.

It the final step, you can choose which field contains the email address.

You could now click on the Finish and Merge button to produce a document with all the records, print them or, indeed send them.
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37721509
To be fair, either method will work.

If you are more comfortable with Word, then go with what GrahamSkan posted.

If you prefer MS Access, then what I posted would be another option.


LVL 76

Expert Comment

ID: 37721661
Part 2

You will need some code like this called from a button on the form. Note that it uses early binding, so there should be a reference to the Microsoft Word Object Library in your Access VBA.

Option Explicit

Sub MergeRecord()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim strSQL As String
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open("C:\MYFiles\MyMailMergeMain.Doc")
    strSQL = wdDoc.MailMerge.DataSource.QueryString
    wdDoc.MailMerge.DataSource.QueryString = strSQL & " WHERE REcipientID = " & txtID
    With wdDoc.MailMerge
        .Destination = wdSendToEmail
    End With
    wdDoc.Close wdDoNotSaveChanges
End Sub

Open in new window


Author Comment

ID: 37721673
If you prefer MS Access, then what I posted would be another option.

If I do this method can I send the report as (word doc or docx)?  I see a rft format but that would not work?

So can I change the format "Rich Text Format" to a doc/docx format?

Also can I attach a basic cover letter to the "bodyOfTextToSend"?

DoCmd.SendObject acSendReport, "Reportname", "Rich Text Format" , , , , "Request ", "bodyOfTextToSend", True

I would be attaching 3 documents/reports
1) Cover letter for the "bodyOfTextToSend"
2) Resume
3) CoverLetter

LVL 31

Assisted Solution

Helen_Feddema earned 100 total points
ID: 37721741
See my Four Ways to Merge to Word code sample:

One of the forms lets you create a single Word document for the current record, which seems to be what you wanted in your original post.  There are other forms for batch exports of Access data to Word, using different methods.

That code sample is about 10 years old; for a more recent treament, see my Working with Word ebook.

Author Closing Comment

ID: 37758311
Thanks all for helping!

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now