Microsoft Word Mail Merge from SQL Server 2000 DB Data

[GUnit]

Greetings,

I'm trying to Automate some Mailing Labels from a Windows Form in VB.Net using SQL Server 2000 DB data to a Microsoft Word 2002 Document, and here's what I've got so far:
---------------------------------------------------------------------------------------
Sub DoMailMerge()

Dim oApp As Word.Application = New Word.Application

Dim oDoc As Word.Document = New Word.Document

Dim oMerge As Word.MailMerge

Dim oMergeDataSource As Word.MailMergeDataSource

Dim sFileName As String = "\\Fileserver\Computer Services\Application Development\DotNet Projects\WatershedBrochureTracking\WordDocument\MailMerge.dot"

Dim sSQL As String = "Select FirstName,LastName,Address1,City,State,ZipCode from Orders where OrderID = '" & mOrderID & "'"

Try

oApp.Visible = True

oApp.Documents.Open(CType(sFileName, String))

oDoc = oApp.ActiveDocument

oMerge = oDoc.MailMerge

oMerge.OpenDataSource(Name:="", Connection:="DSN=Watershed;Database=watershed;Trusted_Connection=Yes;persist security info=False", SQLStatement:=sSQL)

oMerge.Execute()

oMergeDataSource.Close()

oDoc.Close() ' close the original mail merge document

oMerge = Nothing

oDoc = Nothing

oApp = Nothing

Catch ex As Exception

HandleError(ex, "forms.formWordLabels.DoMailMerge()")

End Try

End Sub

--------------------------------------------------------------------------------------------------------

When the Word document opens I get prompted by Word to "Select a DataSource" for which I would like automated, as well as automating the query along with the labels. Please help...

Thanks

GUnit

 

[Jim Vierra]

Try using a file based connection

oMerge.OpenDataSource(Name:="c:\northwind.odc")
It's easier tomaintain as it ps configuration outside of the program where it belongs. A properly constructed connection spec will not give you any prompts and can built from inside of Word. Yu can also use a dsn file or a udl file.

Normally the connections are saved in the DOT or the master document for ease of maintenance.

--
Jim Vierra
Greetings,

I'm trying to Automate some Mailing Labels from a Windows Form in VB.Net using SQL Server 2000 DB data to a Microsoft Word 2002 Document, and here's what I've got so far:
---------------------------------------------------------------------------------------
Sub DoMailMerge()

Dim oApp As Word.Application = New Word.Application

Dim oDoc As Word.Document = New Word.Document

Dim oMerge As Word.MailMerge

Dim oMergeDataSource As Word.MailMergeDataSource

Dim sFileName As String = "\\Fileserver\Computer Services\Application Development\DotNet Projects\WatershedBrochureTracking\WordDocument\MailMerge.dot"

Dim sSQL As String = "Select FirstName,LastName,Address1,City,State,ZipCode from Orders where OrderID = '" & mOrderID & "'"

Try

oApp.Visible = True

oApp.Documents.Open(CType(sFileName, String))

oDoc = oApp.ActiveDocument

oMerge = oDoc.MailMerge

oMerge.OpenDataSource(Name:="", Connection:="DSN=Watershed;Database=watershed;Trusted_Connection=Yes;persist security info=False", SQLStatement:=sSQL)

oMerge.Execute()

oMergeDataSource.Close()

oDoc.Close() ' close the original mail merge document

oMerge = Nothing

oDoc = Nothing

oApp = Nothing

Catch ex As Exception

HandleError(ex, "forms.formWordLabels.DoMailMerge()")

End Try

End Sub

--------------------------------------------------------------------------------------------------------

When the Word document opens I get prompted by Word to "Select a DataSource" for which I would like automated, as well as automating the query along with the labels. Please help...

Thanks

GUnit