• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

How to program a mailmarge from Access 2007

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
gplana
Asked:
gplana
  • 2
  • 2
1 Solution
 
fabalouCommented:
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
 
gplanaAuthor Commented:
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
 
fabalouCommented:
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
 
Dale FyeCommented:
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
 
gplanaAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now