Do the letters have to be in Word format or could they be plain text or HTML as well?
Regards, -- Howie Hamlin - inFusion Project Manager On-Line Data Solutions, Inc. - www.CoolFusion.com - 631-737-4668 x101 inFusion Mail Server (iMS) - The Award-winning, Intelligent Mail Server >>> Find out how iMS Stacks up to the competition: >http://www.coolfusion.com/imssecomparison.cfm ----- Original Message ----- From: "Ian Skinner" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, April 29, 2002 5:13 PM Subject: Mail Merge Options > We are building a product that needs a feature where form letters are mail > merge with database information to generate printed output. > > We have been tyring to use the Word COM object with cold fusion to merge a > template doc file with a CSV file created by cold fusion to create the form > letter doc. This works, sometimes. But we are finding this very fragile, and > difficult to debug. > > I was hoping others out there who have done tasks like this would give me > some hints and pointers on what other options may be available. > > We need highly formatted, printed output of form letters merge with database > information, used for billing statements and other corespondance with > members. > > Thanks in advance. > > Ian Skinner > [EMAIL PROTECTED] > > > <CFSET gWordDirectory="e:\dataaccesssolutions\das_alpha\WordDocs\"> > <CFSET gCouncilNumber="632"> > <CFSET gModules="Adult, Camps, Donor, OlderGirl, ProgEvents"> > <CFSET > TemplateDirectory="e:\dataaccesssolutions\das_alpha\WordDocs\Templates\"> > <CFSET ModuleName="ProgEvents"> > <cfquery name="MailingTypes" datasource="#secure.dsn#"> > SELECT > DISTINCT TemplateName > FROM Mailing_Types > </cfquery> > <!--- First thing's first. Let's make sure the directory structure is > in place.. ---> > <CFOUTPUT> > <!--- Make a list of all the different Mailing Types ---> > <CFSET MailingTypesList=""> > <CFLOOP QUERY="MailingTypes"> > <CFSET MailingTypesList=MailingTypesList & > "#MailingTypes.TemplateName#,"> > </CFLOOP> > <CFSET > MailingTypesList=LEFT(MailingTypesList,LEN(MailingTypesList)-1)><!--- Check > to see if council directory exists ---> > <CFTRY> > <CFDIRECTORY ACTION="CREATE" > DIRECTORY="#trim(gWordDirectory)##trim(gCouncilNumber)#"> > Creating: #trim(gWordDirectory)##trim(gCouncilNumber)#<br> > <cfcatch type="any"> > <!--- if we get here, the directory already exists.. > good... ---></cfcatch></CFTRY><!--- Check to see if under the council > directory, there is a directory for each module type ---> > <!--- (Adult, Camps, Donor, OlderGirl, ProgEvents) ---> > <CFLOOP INDEX="i" LIST="#gModules#"> > <CFTRY> > <CFDIRECTORY ACTION="CREATE" > DIRECTORY="#trim(gWordDirectory)##trim(gCouncilNumber)#\#trim(i)#"> > Creating: #trim(gWordDirectory)##trim(gCouncilNumber)#\#trim(i)#<br> > <cfcatch type="any"> > <!--- if we get here, the directory already exists.. > good... ---></cfcatch></CFTRY> > </CFLOOP> > <!--- Check to see if each of the 12 different mailing types (one > directory each (12)) exists ---> > <CFLOOP INDEX="GM" LIST="#gModules#"> > <CFLOOP INDEX="MLT" LIST="#MailingTypesList#"> > <CFTRY> > <CFDIRECTORY ACTION="CREATE" > DIRECTORY="#trim(gWordDirectory)##trim(gCouncilNumber)#\#trim(GM)#\#trim(MLT > )#"> > <cfcatch type="any"> > <!--- if we get here, the directory already exists.. > good... ---></cfcatch></CFTRY> > </CFLOOP> > </CFLOOP> > <!--- Ok, all the correct directories exist, now let's go through > the audit trail table & see who needs a letter ---> > <!--- Event_Audit_Trail fields: indexkey, DateTime, RegID, > EventID, UserID, RegStatus, LetterFileName, LabelFileName ---> > > <!--- > <CFSET ValidRegStatusIndexs="4,5,8,11,15,22,23,24">---> > <!--- works: 4,8,11,15 ---> > <CFSET ValidRegStatusIndexs="4,8,11,15"> > <CFLOOP INDEX="RegStatusIndex" LIST="#ValidRegStatusIndexs#"> > <cfquery name="EventIDs" datasource="#secure.dsn#"> > SELECT > DISTINCT EventID > FROM Event_Audit_Trail > WHERE (RegStatus = #RegStatusIndex#) > AND (LetterFileName IS NULL) > </cfquery> > <CFLOOP QUERY="EventIDs"> > <cfquery name="MergeDocFileName" > datasource="#secure.dsn#"> > SELECT MergeDoc > FROM RegStatus > WHERE RegStatusCode = > #RegStatusIndex# > AND upper(EventTypeID) > = 'P' > </cfquery> > <cfquery name="Eligible" datasource="#secure.dsn#"> > SELECT Distinct RegID, indexkey > FROM Event_Audit_Trail > WHERE (RegStatus = > #RegStatusIndex#) > AND (LetterFileName IS > NULL) > AND > EventID='#EventIDs.EventID#' > </cfquery> > <CFSET MDFileName = > MergeDocFileName.MergeDoc> > <CFIF trim(Eligible.RegID) NEQ '' AND trim(MDFileName) > NEQ ''> > <CFSWITCH expression="#RegStatusIndex#"> > <CFCASE value="4,8,11,15,22,23,24"> > <!--- (15)Kicked Out, (11)Closed, (8)Need Adult, (4)Waiting List ---> > <cfquery name="Mailing" datasource="#secure.dsn#"> > SELECT > DISTINCT > events_registration.regID, events.EventName, events.StartDate, > events.StartTime, > > Member.LastName, Member.FirstName, Member.Address1, Member.City, > Member.State, Member.Zip5, > > Troop_Member.Troopid > FROM > events_registration, Member, events, Troop_Member > WHERE > events_registration.MemberIndex = Member.MemberIndex > AND > events_registration.EventID = events.EventID > AND > Member.Memberindex = Troop_Member.Memberindex > AND > (events_registration.regID IN (#Valuelist(Eligible.Regid)#)) > ORDER BY > events_registration.regiD > </cfquery> > </CFCASE> > <CFCASE value="5"> > <!--- ()FA Approved, (5)Unpaid ---> > <cfquery name="Mailing" datasource="#secure.dsn#"> > SELECT > DISTINCT > events_registration.regID, events_registration.troopID, > > Member.LastName, Member.FirstName, Member.Address1, Member.City, > Member.State, Member.Zip5, > > events.EventName, events.StartDate, FA_Requests.AmountApproved, > > events.GirlFee * events_registration.NumGirlsReg + events.AdultFee * > events_registration.NumAdultsReg AS PaymentAmt > FROM > Member, events, {oj FA_Requests RIGHT OUTER JOIN events_registration ON > FA_Requests.MemberIndex = events_registration.MemberIndex } > WHERE > events_registration.MemberIndex = Member.MemberIndex > AND > events_registration.EventID = events.EventID > </cfquery> > </CFCASE></CFSWITCH> > <CFSWITCH expression="#RegStatusIndex#"> > <CFCASE value="4,8,11,15,22,23,24"> > <!--- (15)Kicked Out, (11)Closed, (8)Need Adult, (4)Waiting List, > (22,23,24)Brought Over ---> > <cfset Output = "CurrentDate, Firstname, Lastname, > TroopNo, Address1, City, State, PostalCode, Salutation, Eventname, > EventDate, EventTime" & chr(10)> > <table> > <tr class="table_field"> > <td> RegId > </td> > <td> Name > </td> > <td> Event name > </td> > </tr> > <CFLOOP query="Mailing"> > <tr class="table_field"> > <td> #mailing.regid# > </td> > <td> #mailing.lastname#, > #mailing.Firstname# > </td> > <td> #Mailing.Eventname# > </td> > <p class="default"> > <cfset Output = Output & > "#dateformat(Now(),'MM/DD/YY')#, #Mailing.Firstname#, #Mailing.Lastname#, > #Mailing.TroopID#, #Mailing.Address1#, #Mailing.City#, #Mailing.State#, > #Mailing.Zip5#, Ms., #Mailing.Eventname#, > #DateFormat(Mailing.Startdate,'MM/DD/YY')#, > #TimeFormat(Mailing.StartTime,'hh:mm:ss tt')#"> > <CFIF Mailing.CurrentRow LT > Mailing.RecordCount> > <cfset Output = Output & > chr(10)> > </CFIF> > </tr> > </CFLOOP> > </table></CFCASE> > <CFCASE value="5"> > <!--- ()FA Approved, (5)Unpaid ---> > <cfset Output = "CurrentDate, Firstname, Lastname, > TroopNo, Address1, City, State, PostalCode, Salutation, Eventname, > EventDate" & chr(10)><!--- > <table> > <tr class="table_field"> > <td> RegId > </td> > <td> Name > </td> > <td> Event name > </td> > </tr> ---> > <CFLOOP query="Mailing"> > <!--- > <tr class="table_field"> > <td> #mailing.regid# > </td> > <td> #mailing.lastname#, > #mailing.Firstname# > </td> > <td> #Mailing.Eventname# > </td> > <p class="default">---> > <cfset Output = Output & > "#dateformat(Now(),'MM/DD/YY')#, #Mailing.Firstname#, #Mailing.Lastname#, > #Mailing.TroopID#, #Mailing.Address1#, #Mailing.City#, #Mailing.State#, > #Mailing.Zip5#, Ms., #Mailing.Eventname#, > #Dateformat(Mailing.Startdate,'MM/DD/YY')#"> > <CFIF Mailing.CurrentRow LT > Mailing.RecordCount> > <cfset Output = Output & > chr(10)> > </CFIF> > </tr> > </CFLOOP> > </table></CFCASE></CFSWITCH> > <cffile action="Write" > File="#TemplateDirectory##ModuleName#\#MDFileName#.csv" Output="#Output#" > Attributes="Normal"> > <p class="Default">File : <strong> > #TemplateDirectory##ModuleName#\#MDFileName#.csv</strong> Generated</p> > <CFSET file1= > "#TemplateDirectory##ModuleName#\#MDFileName#.doc"><!--- Try to connect to > the Word application object ---> > <cfif Fileexists(File1) and > FileExists('#TemplateDirectory##ModuleName#\#MDFileName#.csv')> > <CFTRY> > <CFOBJECT ACTION="CONNECT" > CLASS="Word.Application" NAME="objWord" TYPE="COM"> > <CFCATCH> > <CFOBJECT ACTION="CREATE" > CLASS="Word.Application" NAME="objWord" TYPE="COM"> > </CFCATCH></CFTRY> > <CFSCRIPT> > /* This will open Word if running locally */ > objWord.Visible = true; > > /* This returns the 'Documents' collection of the Word > object */ > objDoc = objWord.Documents; > > /* Specify a document to open */ > newDoc = objDoc.open(file1); > > /* Use the merge template to create a new "merged" > document */ > objMerge = newDoc.MailMerge; > > objMerge.Destination = 0; > > objMerge.Execute();</cfscript>Opening: #file1#<br><!--- > Save the newly merged document as a new file ---> > <cfloop collection="#objDoc#" item="thisDoc"> > <!--- > <cfif > FileExists("#gCouncilNumber#\#ModuleName#\#MergeDocFileName.MergeDoc#\ltr_#E > ventIDs.EventID#_#DateFormat(Now(),"MMDDYYYY")#.doc")> > <cffile action="delete" > file="#gCouncilNumber#\#ModuleName#\#MergeDocFileName.MergeDoc#\ltr_#EventID > s.EventID#_#DateFormat(Now()," MMDDYYYY ")#.doc"> > </cfif> > ---> > <cfscript> > thisMM = thisDoc.MailMerge; > thisMMState = thisMM.state; > > if (thisMMState eq 0) { > SavedToFile = > "#gCouncilNumber#\#ModuleName#\#MergeDocFileName.MergeDoc#\ltr_#EventIDs.Eve > ntID#_#DateFormat(Now(),"MMDDYYYY")#.doc"; > > > thisDoc.SaveAs("#gWordDirectory##SavedToFile#"); > } else { > thisDoc.Save(); > } > thisDoc.Close();</cfscript> > </cfloop> > File Saved to: #SavedToFile#<br> > <cfscript> > objWord.Quit();</cfscript><!---- Store File name created within Audit > Trail ---> > <cfquery name="AuditUpdate" > datasource="#secure.dsn#"> > Update Event_Audit_Trail > Set LetterFileName = > '#SavedToFile#' > WHERE Indexkey IN > (#Valuelist(Eligible.indexkey)#) > </cfquery> > <cfelse> > Template #File1# does not exists<br> > </cfif> > </CFIF> > </CFLOOP> > </CFLOOP> > </CFOUTPUT> > > > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Archives: http://www.mail-archive.com/[email protected]/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
