Automate Microsoft Word Mail Merge with vb.net


Join me on Facebook

Good morning! Well things have slowed down at work a bit and on the home front. For that I am grateful. Last night I watched Samuel Jackson in “Lakeview Terrace”. Quite the awesome movie! He plays such a good bad guy!

Today’s topic is to automate a Microsoft Word Mail Merge. This is a question I get asked pretty frequently but really it isn’t that hard. Make it a great day!

Dim objSqlConnection As New SqlConnection("user id=user_id_goes_here;password=password_goes_here;initial catalog=database_name_goes_here;data source=server_name_goes_here;Connect Timeout=30")
        Dim objSQLDataAdapter As New SqlDataAdapter("select [name], [phone] from database_name_goes_here.dbo.testtable", objSqlConnection)
        Dim objSqlDataSet As New DataSet
        objSQLDataAdapter.Fill(objSqlDataSet, "mydata")
Dim myWord As New Microsoft.Office.Interop.Word.Application
        Dim myDataDoc As Microsoft.Office.Interop.Word.DocumentClass = myWord.Documents.Add
        Dim myTable As Microsoft.Office.Interop.Word.Table = myDataDoc.Tables.Add(myWord.ActiveDocument.Range(0, 0), 1, 2)
        myTable.Rows(1).Cells(1).Range.Text = "name"
        myTable.Rows(1).Cells(2).Range.Text = "phone"
        For Each myRow As DataRow In objSqlDataSet.Tables("mydata").Rows
            Dim wRow As Microsoft.Office.Interop.Word.Row = myTable.Rows.Add()
            wRow.Cells(1).Range.Text = myRow.Item("name")
            wRow.Cells(2).Range.Text = myRow.Item("phone")
        Next
        myDataDoc.SaveAs("C:\Test\WordData.doc")
        myDataDoc.Close()
        Dim myMailMergeDoc As Document = myWord.Documents.Open("C:\Test\MMTest.doc")
        myMailMergeDoc.MailMerge.OpenDataSource(Name:="C:\Test\WordData.doc")
        myMailMergeDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument
        myMailMergeDoc.MailMerge.Execute()
        myWord.ActiveDocument.SaveAs("c:\Test\MMResult.doc")
        myWord.Visible = True
        myMailMergeDoc.Close()
        myWord.Quit()

Technorati Tags: ,,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,

Advertisements
  1. #1 by mark on June 1, 2009 - 6:18 am

    This line has a problem:myMailMergeDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocumentWdMailMergeDestination is not declared

  2. #2 by mark on June 1, 2009 - 6:34 am

    my mistake, sorted it

  3. #3 by sandra on July 29, 2013 - 9:39 am

    I would just like to point out that Word Automation (Office COM) is not a recommended way to perform mail merge or other word processing operations, especially on a server (ASP.NET, Windows Service). We have been there and I know the problems when using Office COM pretty well. Later we used for mail merge Open XML which is gives you absolute control over the output document but in my opinion is tedious and requires a lot of knowledge about Open XML. This is the main reason we found ourselves this 3rd party solution that enables us to visually design templates in MS Word:
    http://www.docentric.com
    There are also several other 3rd party libraries out there but this was the only one that comes with the Add In for MS Word which really improves template design, especially when you have to create/maintain a lot of templates.

    • #4 by Kelly's Chronicles on July 29, 2013 - 10:33 am

      Totally agree its not recommended. The point was to find a way to do it which is the fun part of coding…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: