Solved

How to program a mailmarge from Access 2007

Posted on 2010-09-04
5
478 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
[X]
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
  • 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 48

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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 …

726 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