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

re: Opening a mail merge document from Access 2000

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_davis@westnet.com.au> wrote in message
news:42a2a75b@quokka.wn.com.au...[color=blue]
> I have set up a mail merge document in Word 2003 which gets its data from[/color]
my[color=blue]
> 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[/color]
on[color=blue]
> its own and this works ok, so obviously I think I'm missing something in[/color]
my[color=blue]
> 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
>
>[/color]

re: Opening a mail merge document from Access 2000

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" <Leigh@NotThis.DatabaseDevelopment.co.uk> wrote in message
news:d7uiv2$f4e$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
> 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_davis@westnet.com.au> wrote in message
> news:42a2a75b@quokka.wn.com.au...[color=green]
>> I have set up a mail merge document in Word 2003 which gets its data from[/color]
> my[color=green]
>> 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[/color]
> on[color=green]
>> its own and this works ok, so obviously I think I'm missing something in[/color]
> my[color=green]
>> 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
>>
>>[/color]
>
>[/color]

re: Opening a mail merge document from Access 2000

Hi again
[color=blue]
> 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 *[/color]
FROM[color=blue]
> '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[/color]
a[color=blue]
> new file and print off if required. When I run my code in Access to open[/color]
the[color=blue]
> 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.[/color]
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.
[color=blue]
>
> 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.[/color]
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