The simplest approach is probably to use Word VBA to set up the data source
in an AutoOpen macro.
To do this, you really have to do at least two things:
a. make sure that the Mail Merge Main Document is never saved with any data
source information - otherwise, when you re-open it, the first thing that
Word does is to try to locate the existing data source. You can't write a
macro that intercepts Word before this point and you can't really guarantee
to avoid the dialog boxes that will appear if the data source is not found.
b. use an AutoOpen macro to connect to the new data source, as you mention.
To disconnect a datasource from its datasource before saving it, you can use
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
You will lose the source name, connection information and any sort/filter
selections, but not any of the fields in the document.
In Word 2003 you can also do ActiveDocument.MailMerge.DataSource.Close
Here's a sample AutoOpen for you:
Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String
' this is a simple example for a data source which is Sheet1 in an Excel
workbook
' set this to be the file name of your data source
strDataSource = "kt.xls"
' set this to be the connection string for your data source
'strConnection = ""
' set this to be the query for your data source (I think this will do)
strQuery = "SELECT * FROM [Sheet1$]"
With ActiveDocument
strDataSource = .Path & "\" & strDataSource
' for an Excel document as data source, we need the path name and probably
the Query string
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=strQuery
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument
' NB the above code does not execute the merge.
End With
End With
If you have never worked with VBA, grham Mayor has a useful article at
http://www.gmayor.com/installing_macro.htm