Mail merge problem

[Squirrel]

Hi everyone,

I've created a mail merge Word doc. (using Office XP) , the data source is
an Access query.

Functionality I'm attempting to set up is:
User sets a boolean field [Merge] to true for each person for whom a mail
merge letter is desired.
The query reads address info from the table for each record where [Merge] is
true.

When I open the doc via Word everything is fine, the data is loaded and the
Mail Merge toolbar is active and I can select "Merge to New Document".

I want to open this Word doc. from Access and perform the "Merge to New
Document" rather than have the user work separately in Word. I can open the
doc. successfully but the mailmerge capability isn't recognized.

Here the part of the code with the problem.

If CreateWordObj Then
gobjWord.Visible = True
gobjWord.Documents.Open CurrentProject.Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.ActiveDocument.MailMerge.Destination = wdSendToNewDocument
gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es = True
gobjWord.ActiveDocument.MailMerge.Execute
gobjWord.ActiveDocument.PrintPreview
gobjWord.Visible = True
End If

If I remove the three lines which reference
gobjWord.ActiveDocument.MailMerge then the document opens in print preview
with the mail merge toolbar greyed out so 'merge to new document' is not
available. It's as though the mail merge capability of the document isn't
recognized.

thx for any insight anyone can offer.

Linda

[Bob Darlington]

Linda,
I use the following code, where strDotpath is the Word template I am using,
and strDataFileName is the data source (text file). I prefer to export the
query to a text file for the merge.

Set WordObj = WordApp.Documents.Open(strDotPath)
With WordObj.MailMerge
.OpenDataSource Name:=strDataFileName
.Destination = wdSendtoNewDocument
.Execute
End With

The line with 'OpenDataSource' is the one missing from your code. I can't
say if that is your problem, but its worth a look.

If you want, I can email the entire code, but it is a bit much to post here.

--
Bob Darlington
Brisbane

"Squirrel" <wi*****@covad.net> wrote in message
news:82***************************@msgid.meganewss ervers.com...

Hi everyone,

I've created a mail merge Word doc. (using Office XP) , the data source is an Access query.

Functionality I'm attempting to set up is:
User sets a boolean field [Merge] to true for each person for whom a mail
merge letter is desired.
The query reads address info from the table for each record where [Merge] is true.

When I open the doc via Word everything is fine, the data is loaded and the Mail Merge toolbar is active and I can select "Merge to New Document".

I want to open this Word doc. from Access and perform the "Merge to New
Document" rather than have the user work separately in Word. I can open the doc. successfully but the mailmerge capability isn't recognized.

Here the part of the code with the problem.

If CreateWordObj Then
gobjWord.Visible = True
gobjWord.Documents.Open CurrentProject.Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.ActiveDocument.MailMerge.Destination = wdSendToNewDocument gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es = True
gobjWord.ActiveDocument.MailMerge.Execute
gobjWord.ActiveDocument.PrintPreview
gobjWord.Visible = True
End If

If I remove the three lines which reference
gobjWord.ActiveDocument.MailMerge then the document opens in print preview
with the mail merge toolbar greyed out so 'merge to new document' is not
available. It's as though the mail merge capability of the document isn't
recognized.

thx for any insight anyone can offer.

Linda

[Albert D. Kallal]

I have a working example that "you" can use. it is automatic..

The code you need is:

With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=True
End With

However, with my "example" super word merge, the code you need is:

dim strSql as string

strSql = "select * from tblCustomer where CheckBox = True ORDER BY LastName"

MergeAllWord (strSql)

The above is all you need. So, give my sample word merge a try...as it will
save you a lot of work, you find it here:
http://www.attcanada.net/~kallal.msn.../msaccess.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Steve]

Bob,

I have the same probvlem with a mail merge. It seems that when you try to
use a query with criteria in one of the fields for the data source, mail
merge won't accept the query. I haven't tried Albert's "super mail merge"
yet to see if it works with the query with a criteria in one of the fields.

Steve

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...

Linda,
I use the following code, where strDotpath is the Word template I am using, and strDataFileName is the data source (text file). I prefer to export the
query to a text file for the merge.

Set WordObj = WordApp.Documents.Open(strDotPath)
With WordObj.MailMerge
.OpenDataSource Name:=strDataFileName
.Destination = wdSendtoNewDocument
.Execute
End With

The line with 'OpenDataSource' is the one missing from your code. I can't
say if that is your problem, but its worth a look.

If you want, I can email the entire code, but it is a bit much to post here.
--
Bob Darlington
Brisbane

"Squirrel" <wi*****@covad.net> wrote in message
news:82***************************@msgid.meganewss ervers.com...

Hi everyone,

I've created a mail merge Word doc. (using Office XP) , the data source is

an Access query.

Functionality I'm attempting to set up is:
User sets a boolean field [Merge] to true for each person for whom a mail merge letter is desired.
The query reads address info from the table for each record where [Merge] is

true.

When I open the doc via Word everything is fine, the data is loaded and

the

Mail Merge toolbar is active and I can select "Merge to New Document".

I want to open this Word doc. from Access and perform the "Merge to New
Document" rather than have the user work separately in Word. I can open

the

doc. successfully but the mailmerge capability isn't recognized.

Here the part of the code with the problem.

If CreateWordObj Then
gobjWord.Visible = True
gobjWord.Documents.Open CurrentProject.Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.ActiveDocument.MailMerge.Destination =

wdSendToNewDocument

gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es = True
gobjWord.ActiveDocument.MailMerge.Execute
gobjWord.ActiveDocument.PrintPreview
gobjWord.Visible = True
End If

If I remove the three lines which reference
gobjWord.ActiveDocument.MailMerge then the document opens in print

preview with the mail merge toolbar greyed out so 'merge to new document' is not
available. It's as though the mail merge capability of the document isn't recognized.

thx for any insight anyone can offer.

Linda

[Bob Darlington]

Steve,
That is one of the reasons I export the query to a text file, and use that
as my data source.
I use
DoCmd.TransferText acExportMerge, , queryname, "datafile.txt"
then rename the file with a .doc extension.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam.spam> wrote in message
news:39*****************@newsread2.news.atl.earthl ink.net...

Bob,

I have the same probvlem with a mail merge. It seems that when you try to
use a query with criteria in one of the fields for the data source, mail
merge won't accept the query. I haven't tried Albert's "super mail merge"
yet to see if it works with the query with a criteria in one of the fields.
Steve

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...

Linda,
I use the following code, where strDotpath is the Word template I am

using,

and strDataFileName is the data source (text file). I prefer to export the

query to a text file for the merge.

Set WordObj = WordApp.Documents.Open(strDotPath)
With WordObj.MailMerge
.OpenDataSource Name:=strDataFileName
.Destination = wdSendtoNewDocument
.Execute
End With

The line with 'OpenDataSource' is the one missing from your code. I can't say if that is your problem, but its worth a look.

If you want, I can email the entire code, but it is a bit much to post

here.

--
Bob Darlington
Brisbane

"Squirrel" <wi*****@covad.net> wrote in message
news:82***************************@msgid.meganewss ervers.com...

Hi everyone,

I've created a mail merge Word doc. (using Office XP) , the data source

is

an Access query.

Functionality I'm attempting to set up is:
User sets a boolean field [Merge] to true for each person for whom a mail merge letter is desired.
The query reads address info from the table for each record where [Merge]

is

true.

When I open the doc via Word everything is fine, the data is loaded

and the

Mail Merge toolbar is active and I can select "Merge to New Document".

I want to open this Word doc. from Access and perform the "Merge to

New Document" rather than have the user work separately in Word. I can open the

doc. successfully but the mailmerge capability isn't recognized.

Here the part of the code with the problem.

If CreateWordObj Then
gobjWord.Visible = True
gobjWord.Documents.Open CurrentProject.Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.ActiveDocument.MailMerge.Destination =

wdSendToNewDocument

gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es = True
gobjWord.ActiveDocument.MailMerge.Execute
gobjWord.ActiveDocument.PrintPreview
gobjWord.Visible = True
End If

If I remove the three lines which reference
gobjWord.ActiveDocument.MailMerge then the document opens in print

preview with the mail merge toolbar greyed out so 'merge to new document' is

not available. It's as though the mail merge capability of the document

isn't recognized.

thx for any insight anyone can offer.

Linda

[Steve]

Bob,

Thanks for the info about exporting the query to a text file! When you do
that, what delimiter if any do you use?

Thanks,

Steve
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...

Steve,
That is one of the reasons I export the query to a text file, and use that
as my data source.
I use
DoCmd.TransferText acExportMerge, , queryname, "datafile.txt"
then rename the file with a .doc extension.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam.spam> wrote in message
news:39*****************@newsread2.news.atl.earthl ink.net...

Bob,

I have the same probvlem with a mail merge. It seems that when you try to

use a query with criteria in one of the fields for the data source, mail
merge won't accept the query. I haven't tried Albert's "super mail merge" yet to see if it works with the query with a criteria in one of the

fields.

Steve

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...

Linda,
I use the following code, where strDotpath is the Word template I am

using,

and strDataFileName is the data source (text file). I prefer to export

the query to a text file for the merge.

Set WordObj = WordApp.Documents.Open(strDotPath)
With WordObj.MailMerge
.OpenDataSource Name:=strDataFileName
.Destination = wdSendtoNewDocument
.Execute
End With

The line with 'OpenDataSource' is the one missing from your code. I can't say if that is your problem, but its worth a look.

If you want, I can email the entire code, but it is a bit much to post

here.

--
Bob Darlington
Brisbane

"Squirrel" <wi*****@covad.net> wrote in message
news:82***************************@msgid.meganewss ervers.com...
> Hi everyone,
>
> I've created a mail merge Word doc. (using Office XP) , the data source is
> an Access query.
>
> Functionality I'm attempting to set up is:
> User sets a boolean field [Merge] to true for each person for whom a

mail

> merge letter is desired.
> The query reads address info from the table for each record where

[Merge]

is
> true.
>
> When I open the doc via Word everything is fine, the data is loaded and the
> Mail Merge toolbar is active and I can select "Merge to New Document". >
> I want to open this Word doc. from Access and perform the "Merge to New > Document" rather than have the user work separately in Word. I can open the
> doc. successfully but the mailmerge capability isn't recognized.
>
> Here the part of the code with the problem.
>
> If CreateWordObj Then
> gobjWord.Visible = True
> gobjWord.Documents.Open CurrentProject.Path & "\" & DocName
> DoEvents
>
> ' NEXT LINE FAILS with err.number = 5852 and err.description = > "requested object is not available"
> gobjWord.ActiveDocument.MailMerge.Destination =
wdSendToNewDocument
> gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es = True
> gobjWord.ActiveDocument.MailMerge.Execute
> gobjWord.ActiveDocument.PrintPreview
> gobjWord.Visible = True
> End If
>
> If I remove the three lines which reference
> gobjWord.ActiveDocument.MailMerge then the document opens in print

preview

> with the mail merge toolbar greyed out so 'merge to new document' is not > available. It's as though the mail merge capability of the document

isn't

> recognized.
>
> thx for any insight anyone can offer.
>
> Linda
>
>
>

[Bob Darlington]

Steve,
Run TransferText after selecting records and before running the merge.
TransferText creates a tab limited .txt file, but you can probably specify
another format.
Before running TransferText, I delete any existing text files created during
previous merges (using Kill) , and one called schema.ini which gets created
automatically. Before I started deleting this one, I was getting errors
after the first document was generated (but I can't remember what the error
was).
For some reason, later versions of Access won't allow the .doc extension to
be used in TransferText, and later versions of Word don't like using .txt as
a data source.
So I just rename the .txt to .doc and they both seem to be as happy as a
couple of pigs in.

By using the text file as the data source (instead of the query), you also
avoid problems in a secured db, where you can get a password prompt when you
try to open the data source.

Hope this is of some help.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam.spam> wrote in message
news:gZ*****************@newsread2.news.atl.earthl ink.net...

Bob,

Thanks for the info about exporting the query to a text file! When you do
that, what delimiter if any do you use?

Thanks,

Steve
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...

Steve,
That is one of the reasons I export the query to a text file, and use that

as my data source.
I use
DoCmd.TransferText acExportMerge, , queryname, "datafile.txt"
then rename the file with a .doc extension.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam.spam> wrote in message
news:39*****************@newsread2.news.atl.earthl ink.net...

Bob,

I have the same probvlem with a mail merge. It seems that when you try to use a query with criteria in one of the fields for the data source, mail merge won't accept the query. I haven't tried Albert's "super mail merge" yet to see if it works with the query with a criteria in one of the fields.

Steve

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
> Linda,
> I use the following code, where strDotpath is the Word template I am
using,
> and strDataFileName is the data source (text file). I prefer to export the

> query to a text file for the merge.
>
> Set WordObj = WordApp.Documents.Open(strDotPath)
> With WordObj.MailMerge
> .OpenDataSource Name:=strDataFileName
> .Destination = wdSendtoNewDocument
> .Execute
> End With
>
> The line with 'OpenDataSource' is the one missing from your code. I

can't

> say if that is your problem, but its worth a look.
>
> If you want, I can email the entire code, but it is a bit much to

post here.
>
> --
> Bob Darlington
> Brisbane
>
> "Squirrel" <wi*****@covad.net> wrote in message
> news:82***************************@msgid.meganewss ervers.com...
> > Hi everyone,
> >
> > I've created a mail merge Word doc. (using Office XP) , the data

source

> is
> > an Access query.
> >
> > Functionality I'm attempting to set up is:
> > User sets a boolean field [Merge] to true for each person for whom a mail
> > merge letter is desired.
> > The query reads address info from the table for each record where
[Merge]
> is
> > true.
> >
> > When I open the doc via Word everything is fine, the data is loaded

and

> the
> > Mail Merge toolbar is active and I can select "Merge to New Document". > >
> > I want to open this Word doc. from Access and perform the "Merge

to New

> > Document" rather than have the user work separately in Word. I

can open

> the
> > doc. successfully but the mailmerge capability isn't recognized.
> >
> > Here the part of the code with the problem.
> >
> > If CreateWordObj Then
> > gobjWord.Visible = True
> > gobjWord.Documents.Open CurrentProject.Path & "\" &

DocName > > DoEvents
> >
> > ' NEXT LINE FAILS with err.number = 5852 and err.description = > > "requested object is not available"
> > gobjWord.ActiveDocument.MailMerge.Destination =
> wdSendToNewDocument
> > gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es =

True > > gobjWord.ActiveDocument.MailMerge.Execute
> > gobjWord.ActiveDocument.PrintPreview
> > gobjWord.Visible = True
> > End If
> >
> > If I remove the three lines which reference
> > gobjWord.ActiveDocument.MailMerge then the document opens in print
preview
> > with the mail merge toolbar greyed out so 'merge to new document' is not

> > available. It's as though the mail merge capability of the

document isn't
> > recognized.
> >
> > thx for any insight anyone can offer.
> >
> > Linda
> >
> >
> >
>
>

[j.mandala]

Regarding extensions for the data file> I have had luck using the .tab
extension. Both TransferText and Word seem happy with it.

[Bob Darlington]

Thanks for that J.
I'll give it a try.

--
Bob Darlington
Brisbane
"j.mandala" <ma*****@rci.rutgers.edu> wrote in message
news:6c**************************@posting.google.c om...

Regarding extensions for the data file> I have had luck using the .tab
extension. Both TransferText and Word seem happy with it.