Opening Word Document with Mail Merge from Access (Krissco)
HBH
that I was looking for (see below). However, I'm getting some
unusual results and I'm not sure if there's some configuration
or versioning issues. I'm running Office 2002, though my
database is configured as Access 2000 format.
The first issue is that when I do the
.OpenDatasource
.Execute
I get prompted with a "Select Table" window. I wasn't expecting
this because I specify the Connection with "Query " & qryName.
(This is my biggest concern because I don't want the user to have
to select the query).
The second issue is that when the Word document appears, there
are two windows. One is the "Merge All Records" (which is fine).
The second is the original 'pre-merge' document. However there are no
toolbars, not even the Standard toolbar! I need the 'pre-merge'
window with Toolbars to allow the user to modify the document and
save his changes.
Can anyone help me with this?
TIA
On Aug 21, 6:12 am, krissco <kris...@gmail.com> wrote:
>
> Try this function. It has the option of displaying ONLY the merged
> document (and not the before and after like a "do-it-yourself" merge.
> Some of the code is application specific, but I'm sure you'll be able
> to weed it out.
>
> 'Open the awards report with the parameter name, query, and option to
> hide or show original document
> Public Function openAwardsReport(reportName As String, qryName As String, Optional hideOriginal As Boolean = True)
> On Error GoTo ExitError
>
> 'Display status to user
> SysCmd acSysCmdSetStatus, "Automating Microsoft Word . . ."
> DoCmd.Hourglass True
>
> 'Declare variables
> Dim wApp As New Word.Application
> Dim wDoc As Word.Document
> Dim wMerge As Word.MailMerge
>
> 'Open the word document
> Set wDoc = wApp.Documents.Open(getAwardsPath() & reportName & ".doc", , True, False, , , True, , , , , True)
>
> 'Modify merge properties to this database, using the query specified.
> Set wMerge = wDoc.MailMerge
> With wMerge
> .OpenDataSource CurrentProject.FullName, , , False, True, False, , , True, , , "Query " & qryName
> .Execute
> End With
>
> 'There is no need to view the original merge document, close it without saving
> If hideOriginal Then wDoc.Close wdDoNotSaveChanges
>
> 'Make word visible
> wApp.Visible = True
>
> ExitNormal:
> SysCmd acSysCmdClearStatus
> DoCmd.Hourglass False
> Exit Function
>
> ExitError:
> Select Case Err.Number
> Case 5631 'No records - merge failed
> MsgBox "No Award Winners For Report " & reportName & ".", vbExclamation, "No Winners"
> wApp.Quit wdDoNotSaveChanges
> Set wApp = Nothing
> Case Else
> MsgBox Err.Number & " unhandled error " & Err.description
> End Select
> Resume ExitNormal
> End Function
>
> 'The place where we store our .doc files
> Public Function getAwardsPath() As String
> getAwardsPath = CurrentProject.Path & "\AwardsReports\"
> End Function
>
> -Kris
Albert D. Kallal
word.
The sample I have can be found here:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....
Thus, each time you build a new form, you can word merge enable it with
great ease.
Make sure you read the instructions from above, and you should eventually
get to the follwoing page
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html
Note that the merge can also use a query, and thus you don't have to merge
just "one" record..
After the merge occurs, you get a plain document WITHOUT any merge fields,
and this allows the end user to save, edit, or even email the document
(since the merge fields are gone after the merge occurs).
Give the above a try...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
HBH
I looked at your code before running and at first I was taken
aback that you were creating a temporary csv file. I read your
description of not allowing Word attach to a running mdb. Although
currently it's a single user, eventually it will be multi-user
and I don't know the ramifications with setting security.
But security issues aside, your code does everything I want
to do.
I do like prompting the user for the word file (something I was
planning on writing). The one thing I might want to do is
present the user with both the pre-merge and the post-merge
Word windows, so I might tweak that part of your code.
I truly appreciate your assistance. I've been frustrated
for a couple of weeks trying to figure a way to cleanly do
the merge without having to leave the confines of Access.
I can now see the light at the end of the tunnel!
Thank you, thank you, thank you!!!
Regards,
HBH
HBH
The code provided by Albert was working fine up until recently
when I was trying to do a merge using fields containing
Japanese/IME characters. In the text file they were coming
up as "?".
Knowing that Word Mail Merge can connect with XLS spreadsheets
I then discovered the DoCmd.TransferSpreadsheet function which
replaced all the code that generated the text file from the
query. However when I tried to do the merge, for some reason
Word always prompts the user to "Select Table". Crap...
So I thought "What file format can handle Japanese characters?".
Ah... RTF files! I used DoCmd.OutputTo to generate the RTF:
DoCmd.OutputTo acOutputQuery, qryName, acFormatRTF, filepath, False
and the merge worked! The RTF file is a bit big as it is formats
the data as a spreadsheet. But heck... it works!
I'm just found this solution last night and did some
testing this morning, but hopefully I won't find any
more gotchas.
HBH