Word Mailmerge

[John]

Hi

I am trying to do a word mailmerge form within my vb.net app. My problem is
how to do a query on one of my tables and use the result as the mail merge
datasource. Any help would be appreciated.

Thanks

Regards

 

[One Handed Man]

Can word make use of an XML file, if so you could output that. Failing that
ourput to an Acess or Excel speadsheet.

OHM

 

[Cindy Meister -WordMVP-]

Hi John,

I am trying to do a word mailmerge form within my vb.net app. My problem is
how to do a query on one of my tables and use the result as the mail merge
datasource. Any help would be appreciated.

What's the data source? And does it support saving the query as a query/view?

If not, does it have an ODBC interface (driver)?

And how many characters would you estimate the query could contain, max?

Finally, which version(s) of word would you be dealing with?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Kirk]

here is a sample I wrote for a friend that needed the same thing.
Notice the need of a DSN. This is using odbc. I couldn't find any other
way.
also, notice the hardcoded path to the template file. You will need to
adjust for yours instead.
My template file had just 1 mail merge field named 'au_fname'.

Private Sub DoMailMerge()
Dim w As New Word.Application
Dim d As Word.Document
d = w.Documents.Open("c:\some.dot")

Dim strConnection As String

strConnection = "DSN=bob;uid=sa;pwd=;"
d.MailMerge.OpenDataSource("c:\t.txt", , , , , , , , , , ,
strConnection, "SELECT * FROM authors")

d.MailMerge.Execute()
d.Close(False)
w.Visible = True
End Sub

Kirk Graves

 

[John]

Hi

The database backend is access. Query length would be less then 200
characters. Word version 2000.

Thanks

Regards

 

[John]

My database is access. I just can't figure out how to run a query on an
access table and use the result as the datasource.

Thanks

Regards

 

[Cindy Meister -WordMVP-]

Hi John,

The database backend is access. Query length would be less then 200
characters. Word version 2000.

<<I am trying to do a word mailmerge form within my vb.net app. My
problem is how to do a query on one of my tables and use the result
as the mail merge datasource.>>

Well, looked at from a purely Access POV, you'd use DAO to generate a
query in Access, then link the mail merge to that. Since the general
opinion in these groups is that you shouldn't use DAO, there are
alternatives.

As long as the SQL-statement you use to build your query is 510
characters or less (we're dealing with a limit in Word, here), you
can set the query while linking in the data source, using the
SQLStatement and SQLStatement1 arguments of the OpenDataSource method
for the MailMerge.DataSource object.

For 255 characters or less, you also have the option of setting the
.QueryString property at any time.

The third possibility would be to create a *.dqy for the query using
MS Query and call that as the data source.

From the sound of it, you should be able to do everything you need
using the .OpenDataSource method's argument(s). Since Word is very
picky about the punctuation used in SQL, your best bet is to open up
Word and record a macro while linking to the data source, going over
MS Query. If you activate the "Select method" checkbox in the Open
Data source dialog box, you should get the option to choose ODBC, and
then Word should offer MS Query (if it's installed).

Why MS Query (and ODBC)? Word mail merge provides two possible
connection methods for Access databases: DDE and ODBC. DDE requires
actually opening the Access interface, which can lead to all sorts of
problems. ODBC will give you backwards and forwards compatibility
with all current versions of Office (97 through 2003). As long as
you're dealing with "typical" Office installations, you don't need to
worry about DSNs, since the basic mail merge DSNs are installed by
default.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[John]

Hi

What is t.txt file in this example?

Thanks

Regards

 

[John]

Hi

I have tried;

wrdMailMerge.OpenDataSource(Name:="F:\dbpath\mydb.mdb",
Connection:=m_objConnection, SQLStatement:="SELECT * FROM Contacts")

but I get a 'Type Mismatch' error. What am I doing wrong?

Thanks

Regards

 

[Kirk]

Believe it or not, it is just a dummy file. It must exist, and the file
path must be passed to the argument, but the file can be empty.

Kirk Graves

 

[Kirk]

John,

The demo was originally used against an SQL Server database (or really, any
ODBC database). If you connecting to an Access db, it changes slightly.
Use this line instead.

wrdMailMerge.OpenDataSource(Name:="F:\dbpath\mydb.mdb",
SQLStatement:="SELECT * FROM Contacts")

The connection argument should not be necessary.

Hope that helps

Kirk Graves