Mail Merge from a Windows App using Sql Server

Posted on 2005-05-06
Last Modified: 2010-04-23
Hello experts,

I am creating a VB.Net Application that pulls an ID from SQL Server. Using this ID I want to send it to Word and have Word open a specific document, go out to SQL Server and grab the fields it needs for that document and perform a mail merge. From there it will send it to the printer. Do you know if this is possible, and if so do you have an example of how it would be performed, it would be appreciated.

Waupaca Developer
Question by:itcenters
    LVL 13

    Accepted Solution

    Hi itcenters,
    I did something similar, only using DAO/Access + VB6, but the idea is the same. I'm assuming database access to sql-server is no problem for you (otherwise you'll need to read up on this :)). Make your document in Word as you would normally. Put a placeholder where you want to replace fields, using a marker like for example "<FIRST_NAME>". Make sure you spell the field names right :))

    For each record:
    - Get the document name from the sql-server (or make it based on the ID)
    - Open a copy of Word: Set objWordApp = CreateObject("Word.Application")
    - Open the document: objWordApp.Documents.Add strDocumentTemplate
    - Replace the placeholders with field contents, something like this:
            For Each oField In rs.Fields
                With objWordApp.Selection.Find
                    .Text = "<" & oField.Name & ">"
                    .Replacement.Text = oField.Value
                    .Forward = True
                    .Wrap = wdFindContinue
                    .Format = False
                    .MatchCase = False
                    .MatchWholeWord = False
                    .MatchWildcards = False
                    .MatchSoundsLike = False
                    .MatchAllWordForms = False
                End With
                objWordApp.Selection.Find.Execute Replace:=wdReplaceAll
            Next ' for each field
    - then print the document created: objWordApp.ActiveDocument.PrintOut Background:=False
    - and close it, don't save changes :)): objWordApp.ActiveDocument.Close SaveChanges:=False
    NEXT please

    Is that about what you were looking for? It works quite well, just make sure the field names match and try it on a sample before you do a run of 1000. It's probably not the most efficient way, but if you want to use Word and need to change templates, it'll take some time regardless of the way you do it. :)


    Author Comment

    I'm not sure if it works I am getting the data too it but it is not replacing the particular strings. I think I know where the problem is. You have a line of code

    objWordApp.Selection.Find.Execute Replace:=wdReplaceAll

    What are you referencing with the: wdReplaceAll

    I have a feeling I'm missing it right there. Because I'm trying to execute


    Solution looks great though. Closer than I have been so far.
    LVL 13

    Expert Comment

    Ah, that's VB6, Use the constant wdReplaceAll (=2) for parameter Replace
    My "Execute" looks like this:

    Execute([FindText], [MatchCase], [MatchWholeWord], [MatchWildcards], [MatchSoundsLike], [MatchAllWordForms], [Forward], [Wrap], [Format], [ReplaceWith], [Replace]) As Boolean

    so you'd use
    Call wrdApp.Selection.Find.Execute(,,,,,,,,,, 2)
    (just specify the Replace = 2 directly)
    Actually, looking at that function, you could probably specify all parameters directly :)

    Author Comment

    Worked perfectly. Thank you

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now