Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Mail Merge from a Windows App using Sql Server

Posted on 2005-05-06
4
Medium Priority
?
265 Views
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
0
Comment
Question by:itcenters
  • 2
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
softplus earned 2000 total points
ID: 13947342
Hi itcenters,
I did something similar, only using DAO/Access + VB6, but the idea is the same. I'm assuming database access vb.net 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
            objWordApp.Selection.Find.ClearFormatting
            objWordApp.Selection.Find.Replacement.ClearFormatting
            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. :)

John
0
 

Author Comment

by:itcenters
ID: 13948365
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

wrdApp.Selection.Find.Execute

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

Expert Comment

by:softplus
ID: 13948382
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 :)
John
0
 

Author Comment

by:itcenters
ID: 13948407
Worked perfectly. Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month12 days, 11 hours left to enroll

578 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