Winword Mail Merge Using VB.net

[danasegarane]

This code helps to AutoMate WinWord Using Mail Merge

Main Method

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

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

[OCD Dan]

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!

[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

[jayinthe813]

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:

 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()

[samo8076]

Thanks, works great now

[PhGrant]

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.