Link to home
Start Free TrialLog in
Avatar of jdunneuk
jdunneuk

asked on

Coldfusion Excel and CfMail

Hi Guys,

What I am trying to do is attach an Excel file that is dynamically generated to an email.

However I am having problems with old files been sent and data been incorrect.

The only solution is to generate to a var or so kind and attach this to an email and send it, I think my current method is not working due the fact the vars are not been released or perhaps it is due to the load balancer we use which splits the traffic via two servers.

I hope someone can help

thanks

John
<cfparam name="searchValue">
<cfparam name="searchType">
<cfparam name="requestType">
<cfparam name="requestStatus">
<cfparam name="start" default="1">
<cfparam name="finish" default="1">
<cfparam name = "ACF2" default="">
<cfparam name="emailTo">
 
<!--- File Paths --->
<cfset Session.thisPath = ExpandPath("*.*")>
<cfset Session.thisDirectory = GetDirectoryFromPath(Session.thisPath)>
<cfset Session.timeDate = #Now()#>
<cfset Session.timeDate = #TimeFormat(Session.timeDate, "hhmmss")#>
<cfset Session.randomadd = RandRange(1,10000,"SHA1PRNG")>
<cfset Session.filename = Session.thisDirectory & "Report_"& ACF2 & "_" & Session.timeDate & "_"& Session.randomadd & start &"to"&finish&searchValue&".xls">
 
 
 
 
 
 
<cffile file="#Session.filename#" action="write" nameconflict="overwrite" output='
	<cfcontent type="application/vnd.ms-excel">
	<table border="0">
	  <tr>
		<td><div align="center"><strong>Fund </strong></div></td>
		<td><div align="center"><strong>Participant No</strong></div></td>
		<td><div align="center"><strong>Request Control No.</strong></div></td>
		<td><div align="center"><strong> Acc No.</strong></div></td>
		<td><div align="center"><strong>CUSIP</strong></div></td>
		<td><div align="center"><strong>SSN/TIN</strong></div></td>
		<td><div align="center"><strong>Output</strong></div></td>
		<td><div align="center"><strong>Status</strong></div></td>
		<td><div align="center"><strong>Date</strong></div></td>
	  </tr>' 
	  
	 addnewline="Yes">
	  
	 
<!--- append the dynamic data to the file ---> 
<cfloop index="i" from="1" to="#ArrayLen(tradeRequestViewVOList)#">
 
<cfset tradeRequestViewVO = tradeRequestViewVOList[i]>
							
		<cfif i Mod 2>
			<cfset BackColor="White">
		<cfelse>
			<cfset BackColor="Silver">
		</cfif>
	<cffile action="APPEND"
	file="#Session.filename#" output='
	<tr bgcolor= "#BackColor#">
												
		<td>#tradeRequestViewVO.getFndmName()#</td>
		<td>#tradeRequestViewVO.getNsccParticipantNum()#</td>
		<td>#tradeRequestViewVO.getNsccReqNum()#</td>
		<td>&nbsp;#tradeRequestViewVO.getRequestAcctNum()#</td>
		<td>#tradeRequestViewVO.getCusip()# - #tradeRequestViewVO.getInvvName()#</td>
		<td>&nbsp;#tradeRequestViewVO.getOwnerTinNum()#</td>
		<td>#tradeRequestViewVO.getOutputTtypeId()#</td>
		<td>#tradeRequestViewVO.getRequestStatId()#</td>
		<td>#DateFormat(tradeRequestViewVO.getLastUpdateTimestamp(), "mm/dd/yyyy")#</td>
	
	</tr>'
	addnewline="yes">
	</cfloop>
 
<cffile action="Append"file="#Session.filename#" output="
</table>" addnewline="Yes"> 
 
<CFMAIL
				TO="#emailTo#" 
				FROM="report@mail,com" 
				SUBJECT="FRMS Report"
				TYPE="HTML"
				SERVER="mymailserver"
				port="25" mimeattach="#Session.filename#">
		
	</CFMAIL>
	
 
	
	
	 
<!--- This will not work as the file is deleted before attached
<cffile action="delete" file="#filename#">
	
<script language="javascript">
	window.close();
</script>
 
--->

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PhiveWeeze
PhiveWeeze
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jdunneuk
jdunneuk

ASKER

that worked a charm :). although it wasnt the entire problem its fixed it thanks for the help :)