Solved

How to program a mailmarge from Access 2007

Posted on 2010-09-04
5
456 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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