1 Attachment(s)
Winword Mail Merge Using VB.net
This code helps to AutoMate WinWord Using Mail Merge
Main Method
vb Code:
Imports System.Data
Imports System.IO
Imports System.Reflection
Imports Microsoft.Office.Interop
'Mark a Reference to Microsoft word Object from Com Tab
Module Module1
Sub Main()
Dim sqlQry As String = "SELECT TOP 2 ContactName,ContactTitle,Address from customers"
'Get the values from table
Dim dttable As DataTable = DatabaseOperation.DatabaseOperation(sqlQry, DataBaseQueryType.Datatable)
Dim sTemplateFileName As String = "DocumentTemplate.doc"
'Word merging
Dim moApp As Object
moApp = CreateObject("Word.Application")
If IsNothing(moApp) = False Then
moApp.visible = False
Dim sDocFileName As String
For Each row As DataRow In dttable.Rows
'Open the Document
moApp.Documents.Open(Path.Combine(ApplicationPath, sTemplateFileName))
sDocFileName = Convert.ToString(row(0)) & ".doc"
Dim intCount As Integer = 0
For Each MergeField As Word.MailMergeField In moApp.ActiveDocument.MailMerge.Fields
'Select the text
MergeField.Select()
'Type the text
moApp.Selection.TypeText(Convert.ToString(row(intCount)))
intCount += 1
Next
'Save the document
moApp.ActiveDocument.SaveAs(Path.Combine(ApplicationPath, sDocFileName))
'Close the document
moApp.Documents.Close()
Next
'Dont save the template changes
moApp.Quit(Word.WdSaveOptions.wdDoNotSaveChanges)
'Dispose
End If
moApp = Nothing
dttable.Dispose()
dttable = Nothing
End Sub
Private Function ApplicationPath() As String
Return _
Path.GetDirectoryName([Assembly].GetEntryAssembly().Location)
End Function
End Module
Database Operations
vb Code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.StringBuilder
Imports System.Configuration
Module DatabaseOperation
Public Enum DataBaseQueryType
Datatable = 1
End Enum
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim sConstring As String
Private Sub OpenConnection()
Dim connectionString As String = _
"Integrated Security=SSPI;Persist Security Info=False;" + _
"Initial Catalog=Northwind;Data Source=localhost"
con = New SqlConnection(connectionString)
If con.State = ConnectionState.Closed Then
con.Open()
End If
End Sub
Private Sub CloseConnection()
If con.State = ConnectionState.Open Then
con.Close()
End If
End Sub
Public Function DatabaseOperation(ByVal qry As String, ByVal type As DataBaseQueryType) As Object
Dim objDB As New Object
Dim dt As DataTable
Dim da As SqlDataAdapter
If type = DataBaseQueryType.Datatable Then
OpenConnection()
Dim cmd As New SqlCommand(qry)
cmd.Connection = con
dt = New DataTable
da = New SqlDataAdapter(cmd)
da.Fill(dt)
objDB = dt
dt.Dispose()
CloseConnection()
Return objDB
End If
Return objDB
End Function
Re: Winword Mail Merge Using VB.net
This is exactly what I need, thank you! I spent the day on google looking literally at hundreds of searches for "mail merge vb.net sql server" I'll be doing my searching more here, Thanks again!
Re: Winword Mail Merge Using VB.net
Sorry to bump such an old thread, but stumbled across this the other day, and it works almost how I want it. One thing I cannot work out though, is if the the sql statement needs to bring back the fields in the same order as the mail merge fields within the word document?
My merge is working to an extent, but puts the fields from the dataset in to random merge fields, not the correct ones.
Any ideas?
Also, the above example creates a new word document for each record, ideally I would like to insert a new page for each row, instead of a new document. Any ideas on that one? I spent the entirety of yesterday trying ways to insert a page break, but no luck.
Thanks
Re: Winword Mail Merge Using VB.net
Quote:
Originally Posted by
samo8076
Sorry to bump such an old thread, but stumbled across this the other day, and it works almost how I want it. One thing I cannot work out though, is if the the sql statement needs to bring back the fields in the same order as the mail merge fields within the word document?
My merge is working to an extent, but puts the fields from the dataset in to random merge fields, not the correct ones.
Any ideas?
Also, the above example creates a new word document for each record, ideally I would like to insert a new page for each row, instead of a new document. Any ideas on that one? I spent the entirety of yesterday trying ways to insert a page break, but no luck.
Thanks
Looking at the code:
Code:
For Each MergeField As Word.MailMergeField In moApp.ActiveDocument.MailMerge.Fields
'Select the text
MergeField.Select()
'Type the text
moApp.Selection.TypeText(Convert.ToString(row(intCount)))
intCount += 1
Next
It is assuming that the mergefields will be in the same order as the columns of the datatable. In your case, this is most likely not so. What you should probably do is keep the data table column names the same as the merge field names, then inside the mergefield loop above, get the string name of the mergefield after Mergefield.Select() occurs, match it to the string name column in the datatable, then assign that value using .TypeText()
Re: Winword Mail Merge Using VB.net
Thanks, works great now :)
Re: Winword Mail Merge Using VB.net
The example on this forum thread is using Interop, which can cause trouble in server-side application (see Microsoft's KB 257757).
My advice would be to use fully managed .NET solution with OpenXML. Since OpenXML has steep learning curve, it would probably make sense to look for a toolkit which uses that kind of approach.
I have good experience with Docentric Toolkit. I have been using it for three years now. So here are its pros and cons:
Pros:
- plain simple Word Add-in for template generation
- OpenXML based, server side friendly
- support for images, data-bound graphs, tables
- conditional elements in templates
- support for placeholders in headers and footers
- very fast report generation
Cons:
- not free
- support for PDF output is still somewhat limited.