My Portal Project

A recent request from a past client brought out an old piece of code that I always liked. This client had an ASP3 site that they used to manage another public site, and they had a requirement to do mail merges from this admin site. The previous site data management tool was an Access database where MS Office integration is pretty easy, but MS’s tight product integration (that classic love/hate relationship) makes the process of browser based mail merges with MS Word just as easy.

It requires, of course, that the user be using Internet Explorer 4+, and have at least Word 97 installed. The use of client-side VBScript is required, and you will need an ASP page on the server to provide the XML for the merge. Lastly, in some cases the user will need to place the originating site in their “Trusted Sites” list.

The client-side VBScript has two functions, one to pull the data from the server using the parameters the user enters, and another function to handle a button click that formats the new merged document. Below is the commented script required for this:

<script LANGUAGE=VBScript>
Sub CreateDataDoc(oApp)
  	'Declare variables.
	Dim sServer,oDoc,oRS,sTemp,sHead,oRange,oField
	' next two lines grab user input.
	startL = document.mergeParams.startLetter.value
	endL = document.mergeParams.endLetter.value
	' Create a new document.
	Set oDoc = oApp.Documents.Add
	' Create a new recordset.
	Set oRS = CreateObject("ADODB.Recordset")
	oRS.Open "https://my.domain.com/mergeProvider.asp?startletter=" & startL_
         & "&endletter=" & endL & "z"
	' 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 "C:data.doc"
	' 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("Word.Application")
	' 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, "name"
		oApp.Selection.TypeParagraph
		.Fields.Add oApp.Selection.Range, "address1"
		oApp.Selection.TypeParagraph
		.Fields.Add oApp.Selection.Range, "address2"
		oApp.Selection.TypeParagraph
		.Fields.Add oApp.Selection.Range, "city"
		oApp.Selection.TypeText ", "
		.Fields.Add oApp.Selection.Range, "state"
		oApp.Selection.TypeText "     "
		.Fields.Add oApp.Selection.Range, "zip"
		oApp.Selection.TypeParagraph
		' Create an autotext entry.
		Dim oAutoText
		Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add_
               ("MyLabelLayout", oDoc.Content)
		oDoc.Content.Delete
		.MainDocumentType = 1  ' 1 = wdMailingLabels
		' Open the saved data source.
		.OpenDataSource "C:data.doc"
		' Create a new document. "5160" is the Avery mailing label template
		oApp.MailingLabel.CreateNewDocument "5160", "", "MyLabelLayout"_
                , , 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 "C:test.doc"
	'oMergedDoc.Close False
	'oApp.Quit False
End Sub
</script>

The code behind the “mailMergeProvider.asp” page is fairly simple in that it pulls start and end parameters from the page, calls a database with those parameters and returns XML back to the client. With VBScript, this is a simple process where you simply “save” the resultset to the response stream with a “1″ parameter that converts it to XML.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/myConnectionStrings.asp" -->
<%
Session.Timeout=120
if (Request.QueryString("startletter")  <> “”) then
	startLetter = Request.QueryString(”startletter”)
else
	startLetter = “a”
end if
if (Request.QueryString(”endletter”)  <> “”) then
	endLetter = Request.QueryString(”endletter”)
else
	endLetter = “z”
end if
‘ RECORDET OF ALL WITH FIRST LETTERS BETWEEN START AND END
set recList = Server.CreateObject(”ADODB.Recordset”)
recList.ActiveConnection = MM_connect_STRING
recList.Source = “SELECT * FROM tableName WHERE myKeyColumn >’” + startletter +_
     “‘ AND myKeyColumn< ='" + endLetter + "z' ORDER BY myKeyColumn ASC"
recList.CursorType = 0
recList.CursorLocation = 2
recList.LockType = 3
recList.Open()
Response.ContentType = "text/xml"
recList.Save Response,1
%>

This entry was posted on Friday, January 13th, 2006 at 8:17 am and is filed under Microsoft, Code Bin, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.