Mail merge problem

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

re: Mail merge problem

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" <wiseowl@covad.net> wrote in message
news:82efb$413b93a3$44a4c35c$30853@msgid.meganewss ervers.com...[color=blue]
> Hi everyone,
>
> I've created a mail merge Word doc. (using Office XP) , the data source[/color]
is[color=blue]
> 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][/color]
is[color=blue]
> true.
>
> When I open the doc via Word everything is fine, the data is loaded and[/color]
the[color=blue]
> 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[/color]
the[color=blue]
> 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 =[/color]
wdSendToNewDocument[color=blue]
> 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
>
>
>[/color]

re: Mail merge problem

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
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

re: Mail merge problem

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" <bob@dpcmanAX.com.au> wrote in message
news:413b9e78$0$5456$afc38c87@news.optusnet.com.au ...[color=blue]
> Linda,
> I use the following code, where strDotpath is the Word template I am[/color]
using,[color=blue]
> 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[/color]
here.[color=blue]
>
> --
> Bob Darlington
> Brisbane
>
> "Squirrel" <wiseowl@covad.net> wrote in message
> news:82efb$413b93a3$44a4c35c$30853@msgid.meganewss ervers.com...[color=green]
> > Hi everyone,
> >
> > I've created a mail merge Word doc. (using Office XP) , the data source[/color]
> is[color=green]
> > 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[/color][/color]
mail[color=blue][color=green]
> > merge letter is desired.
> > The query reads address info from the table for each record where[/color][/color]
[Merge][color=blue]
> is[color=green]
> > true.
> >
> > When I open the doc via Word everything is fine, the data is loaded and[/color]
> the[color=green]
> > 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[/color]
> the[color=green]
> > 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 =[/color]
> wdSendToNewDocument[color=green]
> > 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[/color][/color]
preview[color=blue][color=green]
> > 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[/color][/color]
isn't[color=blue][color=green]
> > recognized.
> >
> > thx for any insight anyone can offer.
> >
> > Linda
> >
> >
> >[/color]
>
>[/color]

re: Mail merge problem

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" <nospam@nospam.spam> wrote in message
news:39P_c.7168$Vl5.4242@newsread2.news.atl.earthl ink.net...[color=blue]
> 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[/color]
fields.[color=blue]
>
> Steve
>
>
>
> "Bob Darlington" <bob@dpcmanAX.com.au> wrote in message
> news:413b9e78$0$5456$afc38c87@news.optusnet.com.au ...[color=green]
> > Linda,
> > I use the following code, where strDotpath is the Word template I am[/color]
> using,[color=green]
> > and strDataFileName is the data source (text file). I prefer to export[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
can't[color=blue][color=green]
> > 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[/color]
> here.[color=green]
> >
> > --
> > Bob Darlington
> > Brisbane
> >
> > "Squirrel" <wiseowl@covad.net> wrote in message
> > news:82efb$413b93a3$44a4c35c$30853@msgid.meganewss ervers.com...[color=darkred]
> > > Hi everyone,
> > >
> > > I've created a mail merge Word doc. (using Office XP) , the data[/color][/color][/color]
source[color=blue][color=green]
> > is[color=darkred]
> > > 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[/color][/color]
> mail[color=green][color=darkred]
> > > merge letter is desired.
> > > The query reads address info from the table for each record where[/color][/color]
> [Merge][color=green]
> > is[color=darkred]
> > > true.
> > >
> > > When I open the doc via Word everything is fine, the data is loaded[/color][/color][/color]
and[color=blue][color=green]
> > the[color=darkred]
> > > 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[/color][/color][/color]
New[color=blue][color=green][color=darkred]
> > > Document" rather than have the user work separately in Word. I can[/color][/color][/color]
open[color=blue][color=green]
> > the[color=darkred]
> > > 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 =[/color]
> > wdSendToNewDocument[color=darkred]
> > > 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[/color][/color]
> preview[color=green][color=darkred]
> > > with the mail merge toolbar greyed out so 'merge to new document' is[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > available. It's as though the mail merge capability of the document[/color][/color]
> isn't[color=green][color=darkred]
> > > recognized.
> > >
> > > thx for any insight anyone can offer.
> > >
> > > Linda
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Mail merge problem

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" <bob@dpcmanAX.com.au> wrote in message
news:413bc4d5$0$8142$afc38c87@news.optusnet.com.au ...[color=blue]
> 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" <nospam@nospam.spam> wrote in message
> news:39P_c.7168$Vl5.4242@newsread2.news.atl.earthl ink.net...[color=green]
> > Bob,
> >
> > I have the same probvlem with a mail merge. It seems that when you try[/color][/color]
to[color=blue][color=green]
> > 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[/color][/color]
merge"[color=blue][color=green]
> > yet to see if it works with the query with a criteria in one of the[/color]
> fields.[color=green]
> >
> > Steve
> >
> >
> >
> > "Bob Darlington" <bob@dpcmanAX.com.au> wrote in message
> > news:413b9e78$0$5456$afc38c87@news.optusnet.com.au ...[color=darkred]
> > > Linda,
> > > I use the following code, where strDotpath is the Word template I am[/color]
> > using,[color=darkred]
> > > and strDataFileName is the data source (text file). I prefer to export[/color][/color]
> the[color=green][color=darkred]
> > > 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[/color][/color]
> can't[color=green][color=darkred]
> > > 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[/color]
> > here.[color=darkred]
> > >
> > > --
> > > Bob Darlington
> > > Brisbane
> > >
> > > "Squirrel" <wiseowl@covad.net> wrote in message
> > > news:82efb$413b93a3$44a4c35c$30853@msgid.meganewss ervers.com...
> > > > Hi everyone,
> > > >
> > > > I've created a mail merge Word doc. (using Office XP) , the data[/color][/color]
> source[color=green][color=darkred]
> > > 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[/color]
> > mail[color=darkred]
> > > > merge letter is desired.
> > > > The query reads address info from the table for each record where[/color]
> > [Merge][color=darkred]
> > > is
> > > > true.
> > > >
> > > > When I open the doc via Word everything is fine, the data is loaded[/color][/color]
> and[color=green][color=darkred]
> > > the
> > > > Mail Merge toolbar is active and I can select "Merge to New[/color][/color][/color]
Document".[color=blue][color=green][color=darkred]
> > > >
> > > > I want to open this Word doc. from Access and perform the "Merge to[/color][/color]
> New[color=green][color=darkred]
> > > > Document" rather than have the user work separately in Word. I can[/color][/color]
> open[color=green][color=darkred]
> > > 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[/color][/color][/color]
=[color=blue][color=green][color=darkred]
> > > > "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[/color]
> > preview[color=darkred]
> > > > with the mail merge toolbar greyed out so 'merge to new document' is[/color][/color]
> not[color=green][color=darkred]
> > > > available. It's as though the mail merge capability of the document[/color]
> > isn't[color=darkred]
> > > > recognized.
> > > >
> > > > thx for any insight anyone can offer.
> > > >
> > > > Linda
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Mail merge problem

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" <nospam@nospam.spam> wrote in message
news:gZR_c.7317$Vl5.2250@newsread2.news.atl.earthl ink.net...[color=blue]
> 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" <bob@dpcmanAX.com.au> wrote in message
> news:413bc4d5$0$8142$afc38c87@news.optusnet.com.au ...[color=green]
> > Steve,
> > That is one of the reasons I export the query to a text file, and use[/color][/color]
that[color=blue][color=green]
> > as my data source.
> > I use
> > DoCmd.TransferText acExportMerge, , queryname, "datafile.txt"
> > then rename the file with a .doc extension.
> > --
> > Bob Darlington
> > Brisbane
> > "Steve" <nospam@nospam.spam> wrote in message
> > news:39P_c.7168$Vl5.4242@newsread2.news.atl.earthl ink.net...[color=darkred]
> > > Bob,
> > >
> > > I have the same probvlem with a mail merge. It seems that when you try[/color][/color]
> to[color=green][color=darkred]
> > > use a query with criteria in one of the fields for the data source,[/color][/color][/color]
mail[color=blue][color=green][color=darkred]
> > > merge won't accept the query. I haven't tried Albert's "super mail[/color][/color]
> merge"[color=green][color=darkred]
> > > yet to see if it works with the query with a criteria in one of the[/color]
> > fields.[color=darkred]
> > >
> > > Steve
> > >
> > >
> > >
> > > "Bob Darlington" <bob@dpcmanAX.com.au> wrote in message
> > > news:413b9e78$0$5456$afc38c87@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[/color][/color][/color]
export[color=blue][color=green]
> > the[color=darkred]
> > > > 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[/color]
> > can't[color=darkred]
> > > > 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[/color][/color][/color]
post[color=blue][color=green][color=darkred]
> > > here.
> > > >
> > > > --
> > > > Bob Darlington
> > > > Brisbane
> > > >
> > > > "Squirrel" <wiseowl@covad.net> wrote in message
> > > > news:82efb$413b93a3$44a4c35c$30853@msgid.meganewss ervers.com...
> > > > > Hi everyone,
> > > > >
> > > > > I've created a mail merge Word doc. (using Office XP) , the data[/color]
> > source[color=darkred]
> > > > 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[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
loaded[color=blue][color=green]
> > and[color=darkred]
> > > > the
> > > > > Mail Merge toolbar is active and I can select "Merge to New[/color][/color]
> Document".[color=green][color=darkred]
> > > > >
> > > > > I want to open this Word doc. from Access and perform the "Merge[/color][/color][/color]
to[color=blue][color=green]
> > New[color=darkred]
> > > > > Document" rather than have the user work separately in Word. I[/color][/color][/color]
can[color=blue][color=green]
> > open[color=darkred]
> > > > 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 & "\" &[/color][/color][/color]
DocName[color=blue][color=green][color=darkred]
> > > > > DoEvents
> > > > >
> > > > > ' NEXT LINE FAILS with err.number = 5852 and[/color][/color][/color]
err.description[color=blue]
> =[color=green][color=darkred]
> > > > > "requested object is not available"
> > > > > gobjWord.ActiveDocument.MailMerge.Destination =
> > > > wdSendToNewDocument
> > > > > gobjWord.ActiveDocument.MailMerge.SuppressBlankLin es =[/color][/color][/color]
True[color=blue][color=green][color=darkred]
> > > > > 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'[/color][/color][/color]
is[color=blue][color=green]
> > not[color=darkred]
> > > > > available. It's as though the mail merge capability of the[/color][/color][/color]
document[color=blue][color=green][color=darkred]
> > > isn't
> > > > > recognized.
> > > > >
> > > > > thx for any insight anyone can offer.
> > > > >
> > > > > Linda
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Mail merge problem

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

re: Mail merge problem

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

--
Bob Darlington
Brisbane
"j.mandala" <mandala@rci.rutgers.edu> wrote in message
news:6c9a60ae.0409061036.270b5f18@posting.google.c om...[color=blue]
> Regarding extensions for the data file> I have had luck using the .tab
> extension. Both TransferText and Word seem happy with it.[/color]