Mail merge through an Intranet

[ghornet]

Currently my users have to have a shared file that they can click on the
word docuement and then manually filter their results in the data
source. I am creating an Intranet page that links to the DOT files. So
the user can then browse to the Intranet page click on the template
they need to use and then run a macro I have created for each document
that runs a query based on what template they are opening.

It works fine on the server, but the problem is that when I try to open
the word document from the Intranet I run into some problems It opens
Word and it opens MS query automatically looking for the query file at
this location (\\Leads\CMS\MailList\Letters\CMS.dqy), then it just sits
their, has anyone else tried this? here is my macro.

-'
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\Leads\CMS\MailList\Letters\CMS.dqy",
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False,
_
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False,
Format:=wdOpenFormatAuto, _
Connection:= _
"DRIVER=SQL Server;SERVER=LEADS;UID=UID;PWD=PWD;APP=Microsoft®
Query;WSID=LEADS;Network=DBMSSOCN", _
SQLStatement:="SELECT * FROM CMS WHERE Template = 'SoldNote'"

ActiveDocument.MailMerge.EditDataSource
-

I inheirented these mail merge documents, so I don't know much about
how mailmerge works, just what I know from my last weeks worth of
research.

From what I understand is that when I click on my link in the Intranet
it spawns a new instance of the DOT file as a DOC file.
Thanks

D--

 

[Peter Jamieson]

then it just sits

their, has anyone else tried this?

Not in exactly that form. But here are some thoughts:
a. is the data source already set up in the .dot (i.e even if the macro
does not run, is the .dot or the .doc created from it connected to the data
source? If so, then there is a possibility that the macro is not even
working on the server.
b. do the suers have full permissions to the .dqy files?
c. does the macro still work on the server if you set "Connection" to be
""? Typically, all the necessary connection settings are already in the
..dqy, which you can inspect using Notepad. If setting Connection to ""
works, does it also work on the workstations?
d. in my experience, using the "DRIVER=" keyword without either a FILEDSN=
or DSN= keyword in the connection string rarely works anyway, although it
seems that Word gets away with doing things this way internally. In this
case you could consider avoiding the .dqy altogether and using something
like

ActiveDocument.MailMerge.OpenDataSource Name:= _
\\Leads\CMS\MailList\Letters\CMS.dsn,
Connection:= _
"DSN=\\Leads\CMS\MailList\Letters\CMS.dsn;UID=UID;PWD=PWD;", _
SQLStatement:="SELECT * FROM CMS WHERE Template = 'SoldNote'"

where the .dsn file is created using the ODBC Administrator. In this case,
Word should skip the whole business of opening MS Query and go directly to
the database for its data. That said, the person who created these merges
may have had good reason to do it the .dqy way.