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
Solved

Access 2007 mail merge to word

Posted on 2012-03-13
10
809 Views
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 :
TxtCompany
TxtDate
TxtTitle

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

Thank you!
0
Comment
Question by:Amour22015
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37718151
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
 

Author Comment

by:Amour22015
ID: 37720564
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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...
Access-Simulate-Mail-MergeFormRe.mdb
0
 
LVL 76

Accepted Solution

by:
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.
0
 
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.

;-)

Jeff
0
 
LVL 76

Expert Comment

by:GrahamSkan
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
        .Execute
    End With
    wdDoc.Close wdDoNotSaveChanges
    wdApp.Quit
End Sub

Open in new window

0
 

Author Comment

by:Amour22015
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

Thanks
0
 
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:

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
 

Author Closing Comment

by:Amour22015
ID: 37758311
Thanks all for helping!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

860 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