Mail Merge office 2003 from SQL Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trev - 10 Oct 2007 08:59 GMT

Cross-posted to Word Programming

I am working for a client on SQL Server 2000. They have an urgent
requirement regarding Mail Merge and I simply do not have the knowledge to
help. Here is the problem as sent to me, can anyone assist (apparently it
worked fine from ASP with Office 2000) :

I am looking for someone to solve the problem described under 'Requirement'
below:

Database: SQL 2000
Contains 2 tables with data:
TableA: contains all the data
TableB: contains 2 fields: Field1: Unique Identifier to line in TableA
Field2: Staff Number
Staff select records to be mail merged using a search and select process
through our Intranet. Their selection list is recorded in TableB. This Table
is then joined to TableA for the merge (ie in a View)

Requirement:

To mail merge the selection list against their staff number into Word 2003
using a selected template (we could end up with several different template
layouts).

Several staff could be using the system at the same time.

I need this to be as automated as possible. Staff using the system should
not have to go through the mail merge system currently offered in Word 2003.
Ideally they should be able to select a template, click on it, type in their
staff number and then the mail merge should be automatic from that point to
delivering the result in Word.

The mail-merge also needs to deal with a couple of conditional if
statements, based on a confidential indicator (values 0 – 4), two fields can
either be shown or not dependant of the Indicator value.

Pointers or assistance greatly appreciated.

TIA
Trev

Reply to this Message

Peter Jamieson - 10 Oct 2007 09:19 GMT

Broadly speaking it should be possible to do this in much the same way that
it worked with Office 2000. The document and the query should be the same or
similar, but
a. Word nowpops up an additional question when you open a mail merge
document that is already attached to its data source. If you are opening the
document programmatically, the equivalent is that Word may fail to open the
data source if you have not made a modification to the user's Windows
Registry (in this case, the "user" could be ASP.NET, depending on what
you're doing) - see

http://support.microsoft.com/kb/825765/en-us

b. If you are attaching the data source programmatically using Word's
OpenDataSource method (this is likely, but it is also possible that the
document has been saved with the data source attached) then you will
probably need to modify the code a bit. With WOrd 2000 you would have been
using ODBC to connect to SQL Server and you would have had something like

objDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=name of the SQL Server DSN;other connection info;", _
SQLStatement:="SELECT * FROM whatever"

or possibly

objDocument.MailMerge.OpenDataSource _
Name:="pathname of a dsn file.dsn", _
Connection:="FILEDSN=pathname of a dsn file.dsn;other connection info;", _
SQLStatement:="SELECT * FROM whatever"

in Word 2003 you will almost certainly need to add an extra parameter,

Subtype:=wdMergeSubtypeWord2000

FWIW in Word 2003 the default connection method has changed from ODBC ato
OLE DB and you might want to change the OpenDataSource to reflect that. In
that case, you need a .udl file or .odc instead of that DSN, a different
connection string, and to alias the table names in the SELECT statement. (If
you search this group in Google groups for Peter Jamieson SQL Server SELECT
you may find some examples. The main advantage of using OLE DB is that it
deals with Unicode field types correctly.

There are other changes in the Word user interface that may affect what
users see and usabilty in general, but I suggest you start by getting the
connections to work.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Cross-posted to Word Programming
>
[quoted text clipped - 46 lines]
> TIA
> Trev

Reply to this Message