Microsoft KB Archive/285176

= How to automate Word to perform a client-side Mail Merge using XML from SQL Server =

Article ID: 285176

Article Last Modified on 10/23/2007

-

APPLIES TO


 * Microsoft Office Word 2007
 * Microsoft Office Word 2003
 * Microsoft Word 2000 Standard Edition
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Active Server Pages 4.0

-



This article was previously published under Q285176



SUMMARY
This article demonstrates how to automate Microsoft Word from client-side script to perform a mail merge for labels. Word does not have a direct method for using XML data as the data source for a mail merge. The sample illustrates Active Server Pages (ASP) code that streams an XML recordset from a Web server to a client. Client-side script converts this XML data to a delimited text file that is local to the client and then automates Word to perform a mail merge by using the local text file as the mail merge data source.



MORE INFORMATION
ActiveX Data Objects (ADO) 2.5 and later allows recordset data to be persisted in XML format. Using ASP on a Web server, you can build an ADO recordset from a database and return the data as XML to your clients. In turn, clients can use ADO to read the XML recordset and manipulate the data as needed. See the &quot;References&quot; section of this article for links to additional resources on using ADO with XML.

For illustration purposes, the following sample uses the sample Pubs database in Microsoft SQL Server. However, similar code may be used to perform a mail merge with any database to which you can make an ADO connection.

Sample
 On your Web server, create a virtual directory that is called WordMailMerge.  Use Notepad to create a file called Default.asp that contains the code given below. Save the file to the WordMailMerge virtual directory. <%@ Language=VBScript %>    Sub CreateDataDoc(oApp) ' Declare variables. Dim sServer,oDoc,oRS,sTemp,sHead,oRange,oField ' Place your server's name here. sServer = &quot; &quot; ' Create a new document. Set oDoc = oApp.Documents.Add ' Create a new recordset. Set oRS = CreateObject(&quot;ADODB.Recordset&quot;) ' Open the XML recordset from the server oRS.Open &quot;http://&quot; & sServer & &quot;/WordMailMerge/Getdata.asp&quot; ' Convert the recordset to a string. sTemp = oRS.GetString(2, -1, vbTab) ' 2 = adClipString ' Append the field names to the front of the string. For Each oField In oRS.Fields sHead = sHead & oField.Name & vbTab Next ' Strip off the last tab. sTemp = Mid(sHead, 1, Len(sHead) - 1) & vbCrLf & sTemp ' Get a range object and insert the text into the document. Set oRange = oDoc.Range oRange.Text = sTemp ' Convert the text to a table. oRange.ConvertToTable vbTab ' Save the document to a temp file. oDoc.SaveAs &quot;C:\data.doc&quot; ' Close the document (no save). oDoc.Close False End Sub

Sub ButtonClick Dim oApp Dim oDoc Dim oMergedDoc ' Create an instance of Word. Set oApp = CreateObject(&quot;Word.Application&quot;) ' Create our data file. CreateDataDoc oApp ' Add a new document. Set oDoc = oApp.Documents.Add With oDoc.MailMerge ' Add our fields. .Fields.Add oApp.Selection.Range, &quot;au_fname&quot; oApp.Selection.TypeText &quot; &quot; .Fields.Add oApp.Selection.Range, &quot;au_lname&quot; oApp.Selection.TypeParagraph .Fields.Add oApp.Selection.Range, &quot;city&quot; oApp.Selection.TypeText &quot;, &quot; .Fields.Add oApp.Selection.Range, &quot;state&quot; oApp.Selection.TypeParagraph .Fields.Add oApp.Selection.Range, &quot;zip&quot; oApp.Selection.TypeParagraph ' Create an autotext entry. Dim oAutoText Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add _ (&quot;MyLabelLayout&quot;, oDoc.Content) oDoc.Content.Delete .MainDocumentType = 1 ' 1 = wdMailingLabels ' Open the saved data source. .OpenDataSource &quot;C:\data.doc&quot;

' Create a new document. oApp.MailingLabel.CreateNewDocument &quot;5160&quot;, &quot;&quot;, _ &quot;MyLabelLayout&quot;,, 4 ' 4 = wdPrinterManualFeed

.Destination = 0 ' 0 = wdSendToNewDocument ' Execute the mail merge. .Execute

oAutoText.Delete End With ' Close the mail merge edit document. oDoc.Close False ' Get the current document. Set oMergedDoc = oApp.ActiveDocument ' Show Word to the user. oApp.Visible = True ' Uncomment these lines to save the merged document locally. 'oMergedDoc.SaveAs &quot;C:\test.doc&quot; 'oMergedDoc.Close False 'oApp.Quit False End Sub       Use Notepad to create a file called Getdata.asp that contains the code given below. Save the file to the WordMailMerge virtual directory. <%@ Language=VBScript %> <% Dim oConn,oRS,strConn,sSQLServer ' Build the connection string. Replace and with ' the username and password of an account that has permissions on the database. sSQLServer = &quot; &quot; strConn = &quot;Provider=SQLOLEDB.1;Persist Security Info=False;&quot; & _ &quot;User ID= ;Password= ;Initial Catalog=pubs;Data Source=&quot; & sSQLServer ' Set our return content type. Response.ContentType = &quot;text/xml&quot; ' Create a connection. set oConn = Server.CreateObject(&quot;ADODB.Connection&quot;) ' Open the connection. oConn.Open strConn ' Execute the SQL statement. set oRS = oConn.Execute(“SELECT * FROM AUTHORS”) ' Save the recordset in the Response object. oRS.Save Response,1 %>                   </li> Change the sServer variable in Default.asp to point to your Web server, and change the sSQLServer variable in Getdata.asp to point to your SQL Server.</li> Start Internet Explorer and browse to http://<servername>/WordMailMerge/Default.asp (where <servername> is the name of your Web server).</li> Click the button on the Web page to automate Word and perform the mail merge. When Automation is complete, Word appears with a new document that contains the mailing labels that resulted from the mail merge.</li></ol>

<div class="references_section">