473,888 Members | 1,462 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.Visibl e = True
gobjWord.Docume nts.Open CurrentProject. Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.Active Document.MailMe rge.Destination = wdSendToNewDocu ment
gobjWord.Active Document.MailMe rge.SuppressBla nkLines = True
gobjWord.Active Document.MailMe rge.Execute
gobjWord.Active Document.PrintP review
gobjWord.Visibl e = True
End If

If I remove the three lines which reference
gobjWord.Active Document.MailMe rge 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
8 9536
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.Documen ts.Open(strDotP ath)
With WordObj.MailMer ge
.OpenDataSource Name:=strDataFi leName
.Destination = wdSendtoNewDocu ment
.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.mega newsservers.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.Visibl e = True
gobjWord.Docume nts.Open CurrentProject. Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.Active Document.MailMe rge.Destination = wdSendToNewDocu ment gobjWord.Active Document.MailMe rge.SuppressBla nkLines = True
gobjWord.Active Document.MailMe rge.Execute
gobjWord.Active Document.PrintP review
gobjWord.Visibl e = True
End If

If I remove the three lines which reference
gobjWord.Active Document.MailMe rge 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
I have a working example that "you" can use. it is automatic..

The code you need is:

With WordDoc.MailMer ge
.Destination = 0 ' 0 = new doc
.MailAsAttachme nt = False
.MailAddressFie ldName = ""
.MailSubject = ""
.SuppressBlankL ines = 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
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.c om.au> wrote in message
news:41******** **************@ news.optusnet.c om.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.Documen ts.Open(strDotP ath)
With WordObj.MailMer ge
.OpenDataSource Name:=strDataFi leName
.Destination = wdSendtoNewDocu ment
.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.mega newsservers.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.Visibl e = True
gobjWord.Docume nts.Open CurrentProject. Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.Active Document.MailMe rge.Destination =

wdSendToNewDocu ment
gobjWord.Active Document.MailMe rge.SuppressBla nkLines = True
gobjWord.Active Document.MailMe rge.Execute
gobjWord.Active Document.PrintP review
gobjWord.Visibl e = True
End If

If I remove the three lines which reference
gobjWord.Active Document.MailMe rge 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
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.TransferT ext acExportMerge, , queryname, "datafile.t xt"
then rename the file with a .doc extension.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam. spam> wrote in message
news:39******** *********@newsr ead2.news.atl.e arthlink.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.c om.au> wrote in message
news:41******** **************@ news.optusnet.c om.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.Documen ts.Open(strDotP ath)
With WordObj.MailMer ge
.OpenDataSource Name:=strDataFi leName
.Destination = wdSendtoNewDocu ment
.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.mega newsservers.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.Visibl e = True
gobjWord.Docume nts.Open CurrentProject. Path & "\" & DocName
DoEvents

' NEXT LINE FAILS with err.number = 5852 and err.description =
"requested object is not available"
gobjWord.Active Document.MailMe rge.Destination =

wdSendToNewDocu ment
gobjWord.Active Document.MailMe rge.SuppressBla nkLines = True
gobjWord.Active Document.MailMe rge.Execute
gobjWord.Active Document.PrintP review
gobjWord.Visibl e = True
End If

If I remove the three lines which reference
gobjWord.Active Document.MailMe rge 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
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.c om.au> wrote in message
news:41******** **************@ news.optusnet.c om.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.TransferT ext acExportMerge, , queryname, "datafile.t xt"
then rename the file with a .doc extension.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam. spam> wrote in message
news:39******** *********@newsr ead2.news.atl.e arthlink.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.c om.au> wrote in message
news:41******** **************@ news.optusnet.c om.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.Documen ts.Open(strDotP ath)
With WordObj.MailMer ge
.OpenDataSource Name:=strDataFi leName
.Destination = wdSendtoNewDocu ment
.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.mega newsservers.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.Visibl e = True
> gobjWord.Docume nts.Open CurrentProject. Path & "\" & DocName
> DoEvents
>
> ' NEXT LINE FAILS with err.number = 5852 and err.description = > "requested object is not available"
> gobjWord.Active Document.MailMe rge.Destination =
wdSendToNewDocu ment
> gobjWord.Active Document.MailMe rge.SuppressBla nkLines = True
> gobjWord.Active Document.MailMe rge.Execute
> gobjWord.Active Document.PrintP review
> gobjWord.Visibl e = True
> End If
>
> If I remove the three lines which reference
> gobjWord.Active Document.MailMe rge 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
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******** *********@newsr ead2.news.atl.e arthlink.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.c om.au> wrote in message
news:41******** **************@ news.optusnet.c om.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.TransferT ext acExportMerge, , queryname, "datafile.t xt"
then rename the file with a .doc extension.
--
Bob Darlington
Brisbane
"Steve" <no****@nospam. spam> wrote in message
news:39******** *********@newsr ead2.news.atl.e arthlink.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.c om.au> wrote in message
news:41******** **************@ news.optusnet.c om.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.Documen ts.Open(strDotP ath)
> With WordObj.MailMer ge
> .OpenDataSource Name:=strDataFi leName
> .Destination = wdSendtoNewDocu ment
> .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.mega newsservers.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.Visibl e = True
> > gobjWord.Docume nts.Open CurrentProject. Path & "\" &
DocName > > DoEvents
> >
> > ' NEXT LINE FAILS with err.number = 5852 and err.description = > > "requested object is not available"
> > gobjWord.Active Document.MailMe rge.Destination =
> wdSendToNewDocu ment
> > gobjWord.Active Document.MailMe rge.SuppressBla nkLines =

True > > gobjWord.Active Document.MailMe rge.Execute
> > gobjWord.Active Document.PrintP review
> > gobjWord.Visibl e = True
> > End If
> >
> > If I remove the three lines which reference
> > gobjWord.Active Document.MailMe rge 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
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
Thanks for that J.
I'll give it a try.

--
Bob Darlington
Brisbane
"j.mandala" <ma*****@rci.ru tgers.edu> wrote in message
news:6c******** *************** ***@posting.goo gle.com...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3144
by: Lisa | last post by:
I have a query named QryDept where one of the fields is DeptID. The query is used for the data source of a mail merge letter. I would like to control which department is to get the mail merge letters. I have a department selection pop-up form where the departments are listed in a listbox. The value of the listbox is DepartmentID. On the form is a Create Mail Merge button which is coded to open the mail merge and make the pop-up form not...
1
2210
by: Shannon Rotz | last post by:
I'm trying to automate Word XP from Access XP with the following function. The sqlstmt argument is "Select * from where ". I understand that Microsoft Word mailmerge changed its default data source to OLEDB, so I took a look in the KB on how to do this. According to Q285332, my function should work - but it doesn't! I keep getting "Cannot open data source" or the "Confirm data source" dialog box. I have tried this with and without the...
3
5593
by: Andy Davis | last post by:
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...
4
7478
by: pmhaupt2 | last post by:
I developed an Access 2003 program that will allow the user to produce a group of Word letters that merge with data records from an Access database. I created a mail merge Word document and associated it with a specific query in my Access database. My problem is that when I run my "create letters" button from an Access form, the program opens up Word correctly with the proper letter. However, the MailMerge toolbar in Word appears...
2
1810
by: David | last post by:
I am having problems closing winword.exe.My mail merge works ok but the word application is not closing down. Can anyone help in telling me where I am going wrong and what I am missing out of my code. Ihave had suggestions but nothing as worked up to now. Many thanks Dave Public Sub Fire() 'On Error GoTo Err_Fire
0
2775
by: Phil C. | last post by:
Hi, I'm using Access 2000. I have a Select Query that uses the MID function to separate the actual text of articles from the title of the articles. The articles are enterd into the underlying table (in a memo field) in html format, as one big block of text. The memo field is called (I named it before I realized that I needed to separate title from text).
0
2211
by: mbbostwick | last post by:
I have a problem with a mail merge procedure I used to use with Access '97. We recently converted to Office XP (2002) and I now have an issue I am unfamilliar with and have been unable to resolve. I am trying to merge individual records into seperate mail merge word docs. In the past I linked my word files directly to the tables and then simply changed the QueryString of the merge file for the particular record I need to create a...
6
4657
by: crealesmith | last post by:
Firstly, I have no problem with mail merging to Word, VB code for that works perfectly. On one mail merge I need to merge 15 fields of data that are from 3 seperate records. The 3 records are all in the same table. If I use a control source that is selecting the 3 records, all of the data is shown but spread over 3 pages in the mail merge, but needs to be listed together in one paragraph. Is there anyway of looping through the 3...
9
2379
by: atc | last post by:
Sorry but another question from a newbie. I am trying to create a mail merge based on a query. I have a button on a form (form is based on query). I found this code in one of the discussions and tried to modify it for my needs. Private Sub Command36_Click() Set objWord = GetObject("C:\Documents and Settings\xxxx\My Documents\merge.doc", "Word.Document") 'turn off alerts objWord.Application.DisplayAlerts = wdAlertsNone ' Make Word...
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11181
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10886
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5819
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4245
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.