473,372 Members | 848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Opening a mail merge document from Access 2000

I have set up a mail merge document in Word 2003 which gets its data from my
Access 2000 database. I want to set up a button on a form that:
1. runs the query to provide the dat for the merge document in Word;
2. opens the document and runs the merge process for the new data.

I have managed to write the code to perform step 1 ok, but I'm having
trouble with step 2. It opens the word document fine but does not perform
the mail merge of the newly created data in step 1. I've included my code
for step 2 below. I've tested the merge process of new data through Word on
its own and this works ok, so obviously I think I'm missing something in my
code. Do I need to include someting in my code to perform the mail merge?

Dim appWord As Object

' Word.Application data type established from reference to Word's object
library
Set appWord = CreateObject("Word.Application")
' 'make Word visible
appWord.Visible = True

'Open Word document
With appWord
.Documents.Add "C:\directory\GolfCoast\settlement_lett.doc"
End With

Exit_SettLett_Word_Click:
Exit Sub

Err_SettLett_Word_Click:
MsgBox Err.Description
Resume Exit_SettLett_Word_Click

End Sub
Many thanks in advance

Dawn Davis
Nov 13 '05 #1
3 5554
Hi Dawn

I'm not totally sure what you're doing.
When you say you first run a query to provide the data - are you running
some sort of append query to a table - and that table is the source of the
merge?

And personally - I probably wouldn't be using a document as a template to
create a new word doc as you do - I prefer to copy the master merge document
locally and then open and carry out actions on that copy.
And you might want to check for open instances of Word before creating a new
one (as a whole bunch of Word applications open might get a bit OTT)

But hey - it should work still.

Perhaps the discontinuity that you're seeing is related to whatever data
operations you're performing before the merge.

What end result are you expecting?
The document opened with the merge fields displayed? Or the merge fields
not shown - and the records displayed to scroll through - or the document
merged out into distinct documents for each record?

Any more info might help.

BFN
"Andy Davis" <ad******@westnet.com.au> wrote in message
news:42******@quokka.wn.com.au...
I have set up a mail merge document in Word 2003 which gets its data from my Access 2000 database. I want to set up a button on a form that:
1. runs the query to provide the dat for the merge document in Word;
2. opens the document and runs the merge process for the new data.

I have managed to write the code to perform step 1 ok, but I'm having
trouble with step 2. It opens the word document fine but does not perform
the mail merge of the newly created data in step 1. I've included my code
for step 2 below. I've tested the merge process of new data through Word on its own and this works ok, so obviously I think I'm missing something in my code. Do I need to include someting in my code to perform the mail merge?

Dim appWord As Object

' Word.Application data type established from reference to Word's object
library
Set appWord = CreateObject("Word.Application")
' 'make Word visible
appWord.Visible = True

'Open Word document
With appWord
.Documents.Add "C:\directory\GolfCoast\settlement_lett.doc"
End With

Exit_SettLett_Word_Click:
Exit Sub

Err_SettLett_Word_Click:
MsgBox Err.Description
Resume Exit_SettLett_Word_Click

End Sub
Many thanks in advance

Dawn Davis

Nov 13 '05 #2
Many thanks for your prompt response. In answer to your questions, I first
run an append query to a table which is then used as the source for my merge
document in Word.

If I just open the document (i.e. a letter displaying address and contact
details) in Word (after running my append query in Access) the document
opens with the data displayed in the appropriate merged fields from the
table of source data in Access. I also get a message when i open the
document in Word saying:
"Opening this document will run the following SQL command: SELECT * FROM
'Filename'. Do you want to continue?"

I then proceed with the "Yes" prompt to display my data in the document.
This document can then be merged to a new document for the user to save as a
new file and print off if required. When I run my code in Access to open the
letter I do not recieve the message that I mentioned above, which makes me
wonder if I have missed out some code to tell Access to perform the mail
merge.

Basically my users want to be able to have an automated process of opening
various letters in Word from Access, with the data being sourced from the
database.

Hope this info provides you without a better insight to my problem.

Regards

Dawn

"LeighP" <Le***@NotThis.DatabaseDevelopment.co.uk> wrote in message
news:d7**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi Dawn

I'm not totally sure what you're doing.
When you say you first run a query to provide the data - are you running
some sort of append query to a table - and that table is the source of the
merge?

And personally - I probably wouldn't be using a document as a template to
create a new word doc as you do - I prefer to copy the master merge
document
locally and then open and carry out actions on that copy.
And you might want to check for open instances of Word before creating a
new
one (as a whole bunch of Word applications open might get a bit OTT)

But hey - it should work still.

Perhaps the discontinuity that you're seeing is related to whatever data
operations you're performing before the merge.

What end result are you expecting?
The document opened with the merge fields displayed? Or the merge fields
not shown - and the records displayed to scroll through - or the document
merged out into distinct documents for each record?

Any more info might help.

BFN
"Andy Davis" <ad******@westnet.com.au> wrote in message
news:42******@quokka.wn.com.au...
I have set up a mail merge document in Word 2003 which gets its data from

my
Access 2000 database. I want to set up a button on a form that:
1. runs the query to provide the dat for the merge document in Word;
2. opens the document and runs the merge process for the new data.

I have managed to write the code to perform step 1 ok, but I'm having
trouble with step 2. It opens the word document fine but does not perform
the mail merge of the newly created data in step 1. I've included my code
for step 2 below. I've tested the merge process of new data through Word

on
its own and this works ok, so obviously I think I'm missing something in

my
code. Do I need to include someting in my code to perform the mail merge?

Dim appWord As Object

' Word.Application data type established from reference to Word's object
library
Set appWord = CreateObject("Word.Application")
' 'make Word visible
appWord.Visible = True

'Open Word document
With appWord
.Documents.Add "C:\directory\GolfCoast\settlement_lett.doc"
End With

Exit_SettLett_Word_Click:
Exit Sub

Err_SettLett_Word_Click:
MsgBox Err.Description
Resume Exit_SettLett_Word_Click

End Sub
Many thanks in advance

Dawn Davis


Nov 13 '05 #3
Hi again
If I just open the document (i.e. a letter displaying address and contact
details) in Word (after running my append query in Access) the document
opens with the data displayed in the appropriate merged fields from the
table of source data in Access. I also get a message when i open the
document in Word saying:
"Opening this document will run the following SQL command: SELECT * FROM 'Filename'. Do you want to continue?"

I then proceed with the "Yes" prompt to display my data in the document.
This document can then be merged to a new document for the user to save as a new file and print off if required. When I run my code in Access to open the letter I do not recieve the message that I mentioned above, which makes me
wonder if I have missed out some code to tell Access to perform the mail
merge. Oh I've had all sorts of 'fun' with Word 2003 finding old merge header info
in (supposedly) long since cleared out documents that no longer use standard
mail merge - but were still prompted by an annoying confirmation message.

Basically my users want to be able to have an automated process of opening
various letters in Word from Access, with the data being sourced from the
database.

Yes - that's totally fair enough. But you'll need to have a clear imagine
in your mind of the end point you want to reach.
What state do you want the documents to be in at the end of it all?
If you look in the cut down function I've pasted below - there are three
different ways you could go with it at the end.
See which is right for you.

Setting the source during the merge might also provide us with some more
information of where things might be going awray.
Just enter your file path and table name as appropriate.
Function TestMerge()

Dim objWord As Object
Dim objDoc As Object
Dim strFullPath As String

'Do your data update
fMergeDataUpdate 'or whatever it's called

'Making sure it's up to date for a second can't hurt
DoEvents

Const cstrMergeTable = "" 'Your merge table name

strFullPath = "" 'Your document file

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err Then
Set objWord = CreateObject("Word.Application")
End If

On Error GoTo errHere

Set objDoc = objWord.Documents.Open(strFullPath)

With objDoc.MailMerge
fWriteOut "Assigning merge source"
.OpenDataSource Name:=CurrentDb.Name, LinkToSource:=True,
AddToRecentFiles:=False, _
Format:=wdOpenFormatAuto, Connection:="TABLE " &
cstrMergeTable, _
SQLStatement:="SELECT * FROM " & cstrMergeTable

'If you want data displayed instead of the merge fields
'.ViewMailMergeFieldCodes = False

'If you want to see the merge fields
'.ViewMailMergeFieldCodes = True

'if you want it merged into a new document - no merge fields at all
.Execute

End With

objWord.Visible = True

exitHere:
Set objDoc = Nothing
Set objWord = Nothing
Exit Function

errHere:
MsgBox Err.Description, vbExclamation
Resume exitHere

End Function
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: William Wisnieski | last post by:
Hi Everyone, Access 2000 I have some code behind a button that performs a word merge with a query data source. The merge works fine. But what I'd like to do somehow is after the merge is...
5
by: Tom Keane | last post by:
Okay, so the deal is, I am doing a mail merge document in order to print invoices. The field I get the amount of money is "invAmount". What I would like to do is be able to manipulate this number...
0
by: Chris | last post by:
I am trying to do a mail merge from Access 2000/2002 through VBA to a Word document. It all works fine on all Windows 2000 machines (regardless of if it is Office 2000 or 2002) but it doesn't work...
9
by: Neil Ginsberg | last post by:
I have a strange situation using Access to automate a Word mail merge. Using Access 2000 and Word 2000, the code opens Word, opens the document in Word, sets a table in the calling Access...
0
by: Linda Cacina | last post by:
Hello all you fine folks, Here is some code I am using to merge data from a single record Access 2K3 table into a NEW word document based on a pre-defined Word merge template doc. All I want to...
4
by: lesperancer | last post by:
I have 3 tables (office97) tblQuote quoteNbr tblDetails ( quote : 1 <-> M: quoteDetails) quoteNbr detailLine product value
1
by: John Welch | last post by:
I'm trying to use the Mergit() function from the MS Knowledge base article to merge data from a query or table in my Access 2000 mdb into an existing word merge document. Every time it runs it...
2
by: Colin Halliday | last post by:
I have a Word 2003 mail merge main document (form letter) that is linked to another Word document data source for the mail merge. If I open this doc using the Word GUI, it first asks me to...
1
by: Esther Lane | last post by:
Hello! First off, many many thanks to Albert who wrote the Mail Merge code for MS Access I am using. It has been working beautifully for a few years. However, my client just (without notice!)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.