Emailing a report as attachment 1

[peterswan]

Hello,

I need to schedule a batch of reports to run in ColdFusion(one per salesperson), export them to Excel, and then automatically email them as attachments to each salesperson once a week.

I guess I should loop through each salesperson, create the report for that person, export to Excel and upload the file somewhere. Then I need to send it out to that recipient with CFMAIL and CFMAILPARAM.

Any help on the code for this would be appreciated.

Thanks,

Peter

 

[NorthStarDA]

well i would export the files to CSV format using CFFILE, you should not have to 'upload' them anywhere, just tell the template where to send the .csv files when they are written, like c:\temp_mail_files, then cfmail the files to their recipients, then CFFILE delete the temp .csv files you created.

<cfquery datasource="DSN_NAME" name="get_sales_people">
QUERY FOR SALESMEN
</cfquery>
<cfloop query="get_sales_people">
<cfquery datasource="DSN_NAME" name="get_report_data">
QUERY FOR REPORT DATA
</cfquery>
<cffile file="C:\mailfiles\name.csv" action="write" output="YOUR QUERY DATA IN CSV FORMAT">
<cfmail to="TO ADDRESS" from="FROM ADDRESS" subject="REPORT">
<cfmailparam file="C:\mailfiles\name.csv">
</cfmail>
<cffile action="delete" file="C:\mailfiles\name.csv">
</cfloop>

i would suggest creating a dynamic file name, maybe with the salespersons name in it and the date, here is an example of what i do to export to csv.

<CFSET TXTHEADER = '"Date","Name","Project","Action","Time In","Time Out","Total Time"'>
<CFSET TXTOUTPUT = "">
<CFSET OUTNAME = "#RandRange(1000, 100000)#.csv">
<CFOUTPUT>
<CFSET OUTFILE = "C:\path_to_file\#Variables.OUTNAME#">
</CFOUTPUT>
<CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTHEADER#">
<CFLOOP QUERY="getrecords">
<CFSET TXTOUTPUT = '"#DateFormat(work_date, "MM/DD/YYYY")#","#first_name# #last_name#","#projectname#","#actionname#","#TimeFormat(time_in, "hh:MM:SS tt")#","#TimeFormat(time_out, "hh:MM:SS tt")#","#TimeFormat(TOTAL_TIME, "HH:MM:SS")#"'>
<CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">
</CFLOOP>

you should be able to get the general idea from these examples

 

[peterswan]

Thanks, NorthStarDA,

I ended up going with CFHTTP. That way I could run a file that was already created and tell it to drop the finished version of the Excel report into a certain folder and call it a certain name (I used the first name plus the last initial, plus the word "weekly", plus today's date as the file name). The CFHTTP has attributes called "path" and "file" that do this, I learned.

The rest was pretty easy, and I went with your CFMAIL example, and also the CFFILE action="delete" example.

Thanks for your help. I just saved some guy about an hour of work every Monday, creating and saving reports, and sending and attaching emails.



Peter