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
