Solved

Access 2007 mail merge to word

Posted on 2012-03-13
10
795 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
Comment Utility
<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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks all for helping!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 …
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

762 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

11 Experts available now in Live!

Get 1:1 Help Now