Access 2007 mail merge to word

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 :
TxtCompany
TxtDate
TxtTitle

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

Thank you!
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
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?
0
Jeffrey CoachmanMIS LiasonCommented:
Amour22015,

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
Access-Simulate-Mail-MergeFormRe.mdb
0
Amour22015Author Commented:
Ok,

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?
GrahamSkan
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?
boag2000
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!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
<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...
Access-Simulate-Mail-MergeFormRe.mdb
0
GrahamSkanRetiredCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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.

;-)

Jeff
0
GrahamSkanRetiredCommented:
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
        .Execute
    End With
    wdDoc.Close wdDoNotSaveChanges
    wdApp.Quit
End Sub

Open in new window

0
Amour22015Author Commented:
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

Thanks
0
Helen FeddemaCommented:
See my Four Ways to Merge to Word code sample:

http://www.helenfeddema.com/Files/code24.zip

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.
0
Amour22015Author Commented:
Thanks all for helping!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.