?
Solved

How to program a mailmarge from Access 2007

Posted on 2010-09-04
5
Medium Priority
?
479 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 1500 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
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

765 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