By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,980 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Mail merge problem

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
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


Nov 13 '05 #4

P: n/a
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



Nov 13 '05 #5

P: n/a
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
>
>
>



Nov 13 '05 #6

P: n/a
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
> >
> >
> >
>
>



Nov 13 '05 #7

P: n/a
Regarding extensions for the data file> I have had luck using the .tab
extension. Both TransferText and Word seem happy with it.
Nov 13 '05 #8

P: n/a
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.

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.