Solved

How to program a mailmarge from Access 2007

Posted on 2010-09-04
5
463 Views
Last Modified: 2012-05-10
Dear Expert:

I have created an application in Access 2007 which has forms, reports, modules, etc... The data is in an .mdb file.

I want to put a button in a form that, when clicked, generate a mailmerge based on an SQL query which has a parameter (related to the record selected on the actual form).

When I try to execute my code, it keeps locked on the call to mailMerge.OpenDataSource because word is asking which connection method I want to use (OLE-DB, ODBC, etc...).

I want to avoid these word messages. Is that possible ? Can you provide me a code example for doing this MailMerge ?

Many thanks.
0
Comment
Question by:gplana
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:fabalou
ID: 33602844
Wouldnt it be easier to make an access report that formats it how you want and then export that to a RTF file, which can then be opened in word.

The report could be filtered to a specific record, based on your current selection and outputted to a specific file all in one go.

 DoCmd.OpenReport "reportname", acViewNormal, , "ID=" & Me.idfield
 DoCmd.OutputTo acOutputQuery, "report name", acFormatRTF, "filename"
 DoCmd.Close acReport, "reportname"

Word mail merge and word mailmerge automation is flaky at best. If you really want to go down that route then its best to write a word automation script that will create the mailmerge for you.
0
 
LVL 15

Author Comment

by:gplana
ID: 33602907
I'm sorry, but I don't want an access report because the result should be based on a word document (used as the template) and user can change this word document on the future.
Also I would like to put the button in Access because the application is in there.
Is it really so difficult to make a simple mailmarge from Access VBA ?
0
 
LVL 3

Accepted Solution

by:
fabalou earned 500 total points
ID: 33602939
Frankly yes, which is why I dont use it. I have users create word documents and put [[FieldName]] into the document and then have access open the document using automation and replace the [[Fieldname]] parts with whatever I need it to.

I gave up on trying to automate words mail merge after a few days and just ended up writing my own code.

snippet to give you some ideas



Set WrdRange = wrddoc.StoryRanges(wdMainTextStory)
Do While WrdRange.Find.Execute("\[*\]", , , True) = True
fldname = WrdRange.Text
fldname = Mid(fldname, 2, Len(fldname) - 2)
Select Case LCase(fldname)
'Depending on the merge field replace text 
Case "sal", "salutation"
ThisRst.MoveFirst
WrdRange.Text = Nz(ThisRst!Sal, "")
Case "first", "firstname", "fname"
ThisRst.MoveFirst
WrdRange.Text = ThisRst!FirstName
Case "last", "surname", "lastname", "lname"
ThisRst.MoveFirst
WrdRange.Text = ThisRst!LastName

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33603002
Understand the reasoning for not creating the report in Access and exporting an RTF file.  When you do so, it inserts hard carraige returns into the rtf file which are a pain to get rid of when/if you decide to edit it.

I've not used Word mailmerge in quite a while either.  I generally use the technique mentioned by fabalou above.  I've enclose the code I use for one such report.  Don't have time right now to pull together a database that contains this report and all of the forms and tables associated with it. But if you need it, I could cludge that together later today.

WordAutomation.accdb
0
 
LVL 15

Author Closing Comment

by:gplana
ID: 33603049
Thank you. This is not exactly what I want butit give me the idea of what I have to do.

Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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 article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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 …
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 …

856 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