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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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!
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
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

by:Helen Feddema
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month5 days, 21 hours left to enroll

626 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