send .csv file from server as attachment to email

Question

showman13 49 Junior Poster

10 Years Ago

Does anybody have any magic formula (known to work) for attaching a .csv file to an email?

I have been working on this most of the morning... Have tried to use several different versions of what is supposed to work from doing google searches and have yet to be successful.

I have a csv file that I have created (new file every day), that I need to attach to an email and send to a vendor to fulfill an order.

Any suggestions? I need to do this in a script so I can set it up as a CRON job to run automatically.

thanks in advance
Douglas

php

2 Contributors
6 Replies
7K Views
23 Hours Discussion Span
Latest Post 10 Years Ago Latest Post by showman13

veedeoo 474 Junior Poster

10 Years Ago

Are you using phpmailer class or the native php mail function? For what I have heard, phpmailer class is more reliable in terms of sending email with file attachement.

example implementation of phpmailer class with attachements. You can download phpmailer class here.

veedeoo 474 Junior Poster

10 Years Ago

I think you need to save the csv file before attaching it to the email.

Here is a test script I made to create and send csv file.

Step 1 : Download the phpmailer class here.

Step 2 : Unzipped the phpmailer zip file.

Step 3: In your server, create a directory named phpmailer. Move these files from the unzipped files to the phpmailer directory.

phpmailer/
class.phpmailer.php
class.pop3.php
class.smtp.php
PHPMailerAutoload.php

Step 4: Take a look at this example fputcsv script post 14.

Step 5: We modify the script to use MySQLI. I urged you to modify the MySQLI. I wrote it just to be able to connect an retrieve data for my testing.

 <?php
require_once('phpmailer/class.phpmailer.php');

## set database credentials
$db_host = '';
$db_user = '';
$db_pass = '';
$db_name = '';


$db_conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

if (mysqli_connect_errno($db_conn))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


 ## the function from http://us3.php.net/fputcsv

function query_to_csv($db_conn, $query, $filename, $attachment = false, $headers = true) {

    if($attachment) {
        // send response headers to the browser
        header( 'Content-Type: text/csv' );
        header( 'Content-Disposition: attachment;filename='.$filename);
        $fp = fopen('php://output', 'w');
    } else {
        $fp = fopen($filename, 'w');
    }

    $result = mysqli_query( $db_conn, $query);

    if($headers) {
        // output header row (if at least one row exists)
        $row = mysqli_fetch_assoc($result);
        if($row) {
            fputcsv($fp, array_keys($row));
            // reset pointer back to beginning
            mysqli_data_seek($result, 0);
        }
    }

    while($row = mysqli_fetch_assoc($result)) {
        fputcsv($fp, $row);
    }

    fclose($fp);
}

Step 6: We modify this script. Save this link for your reference later.

    function send_csv_file($csv_file,$contents){

        $mail = new PHPMailer(true); // the true param means it will throw exceptions on errors, which we need to catch

$mail->IsSMTP(); // telling the class to use SMTP

try {
  $mail->Host       = "mail.yourdomain.com"; // SMTP server
  $mail->SMTPDebug  = 2;                     // enables SMTP debug information (for testing)
  $mail->SMTPAuth   = true;                  // enable SMTP authentication
  $mail->SMTPSecure = "tls";                 // sets the prefix to the servier
  $mail->Host       = "smtp.gmail.com";      // sets GMAIL as the SMTP server
  $mail->Port       = 587;                   // set the SMTP port for the GMAIL server
  $mail->Username   = "yourusername@gmail.com";  // GMAIL username
  $mail->Password   = "yourpassword";            // GMAIL password
  $mail->AddReplyTo('name@yourdomain.com', 'First Last');
  $mail->AddAddress('whoto@otherdomain.com', 'John Doe');
  $mail->SetFrom('name@yourdomain.com', 'First Last');
  $mail->AddReplyTo('name@yourdomain.com', 'First Last');
  $mail->Subject = 'PHPMailer Test Subject via mail(), advanced';
  $mail->AltBody = 'To view the message, please use an HTML compatible email viewer!'; // optional - MsgHTML will create an alternate automatically
  $mail->MsgHTML(file_get_contents($contents));
  $mail->AddAttachment($csv_file);      // attachment
  //$mail->AddAttachment('images/phpmailer_mini.gif'); // attachment
  $mail->Send();
  echo "Message Sent OK<p></p>\n";
} catch (phpmailerException $e) {
  echo $e->errorMessage(); //Pretty error messages from PHPMailer
} catch (Exception $e) {
  echo $e->getMessage(); //Boring error messages from anything else!
}

    }

Fill in your email credentials and the reciever's email.

Step 7: Create the html file for your email's body. We save this file as content.html

    <h3> Title of your message </h3>
    <p> Hello this is a test</p>
    <p>please see attached csv file</p>

Step 8: We execute the functions above.

## define your query
$query = "SELECT * FROM YOUR_TABLE ";

#define the file name for the csv file and the html file contents.
$csv_file = 'test.csv';
$contents = 'content.html';

## execute the csv function
query_to_csv($db_conn, $query, $csv_file, false);

## if you want the csv file to be attached to the browser set false to true.

We check if the file exists, before executing our next function

if(file_exists($csv_file) && (file_exists($contents))){

## execute
send_csv_file($csv_file,$contents);

}

I tested the above script twice and it is sending csv file. It should work on your end.

Please double check on the syntax errors e.g. missing brackets etc.. It is common during copying and pasting from the notepad++.

Good luck..

Edited 10 Years Ago by veedeoo because: info added.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

showman13 49 Junior Poster · Answer 1 · 2014-03-07T21:53:23+00:00

Have never used phpmailer class... I'm still in the old style programming... One of these days I'll come into the new world. Probably just about the same time that they come up with something totally new.

This is my normal funtion that I use for sending emails, but it isn't designed for multi-part emails...

//**********************************************
// Function to send emails
//  call with email address / from address / subject / body of message
function mail_sender($to,$from,$subject,$message_body)
{
$message = "<html><head><title>".$sitename."</title></head><body>";
$message=$message.$message_body;
$message .= "</body></html>";
// Always set content-type when sending HTML email
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";// More headers
$headers .= "From: <".$from.">" . "\r\n";   //  modified function to include $from
$message = wordwrap($message,70);
// Send email
mail($to,$subject,$message,$headers);
}
//***********************************************

Would be easiest if I could just adapt that, but don't know how.

This is just one of the multiple options that I have tried this morning, that I got from my research, but couldn't ever make it work.

I got it to attach a .csv file to the email, but it was empty...

I'm obviously doing something wrong.

//***********************************************
// create_csv_string  and send_csv_mail  work together to send email with csv attached
function create_csv_string($data) {
  // Open temp file pointer
  if (!$fp = fopen('php://temp', 'w+')) return FALSE;
  // Loop data and write to file pointer
  foreach ($data as $line) fputcsv($fp, $line);
  // Place stream pointer at beginning
  rewind($fp);
  // Return the data
  return stream_get_contents($fp);
}

function send_csv_mail ($csvData, $body, $to, $subject, $from) {
  // This will provide plenty adequate entropy
  $multipartSep = '-----'.md5(time()).'-----';
  // Arrays are much more readable
  $headers = array(
    "From: $from",
    "Reply-To: $from",
    "Content-Type: multipart/mixed; boundary=\"$multipartSep\""
  );
  // Make the attachment
  $attachment = chunk_split(base64_encode(create_csv_string($csvData)));
  // Structure the body of the message
  $body = "--$multipartSep\r\n"
        . "Content-Type: text/plain; charset=ISO-8859-1; format=flowed\r\n"
        . "Content-Transfer-Encoding: 7bit\r\n"
        . "\r\n"
        . "$body\r\n"
        . "--$multipartSep\r\n"
        . "Content-Type: text/csv\r\n"
        . "Content-Transfer-Encoding: base64\r\n"
        . "Content-Disposition: attachment; filename=\"file.csv\"\r\n"
        . "\r\n"
        . "$attachment\r\n"
        . "--$multipartSep--";
   // Send the email
   mail($to, $subject, $body, implode("\r\n", $headers));
}

veedeoo 474 Junior Poster Featured Poster · Answer 2 · 2014-03-08T07:02:08+00:00

I forgot to add

mysqli_close($db_conn);

after the

fclose($fp);

showman13 49 Junior Poster · Answer 3 · 2014-03-08T14:26:58+00:00

Well, Good Morning and thank you veedeoo for your response.

But I have got to tell you that there must be some magic programming fairy that worked through the night... or maybe (probably) it was all the prayers for guidance and clarity in what I'm doing, but I woke up this morning and tested it again, only to find that it works perfectly. Must have been a change that I made yesterday and forgot to upload or something.

hopefully, it will continue to work, as I think I'm going to test it a bit more just to be sure...

BTW, my script was creating the .csv before emailing it. What I posted in here was just the function that was called to send the email with the attachment.

I'll let you know how it goes before marking this solved.

thanks again for your very detailed response... I'll study it to see what I can learn from it... that is how I've learned everything I know up to this point.

Douglas

showman13 49 Junior Poster · Answer 4 · 2014-03-08T20:04:20+00:00

OK, it is working fine, I'm not going to mess with it at this point.

But, I did want to share what I think I know from this...
This is the final version of the functions that I have that are working.

I added a variable csvName to the function call, because it was creating a csv file called 'file.csv' and I needed the actual daily name sent.

But, what I think is happening, PLEASE correct me if I'm wrong... is this
The data that is being sent to the function in the variable csvData, is actually the content of the string that is built before the csv file is generated. So the script builds the string, in the variable 'output' and then uses that to generate the csv file and then it is also used in the function call, so I'm thinking that it is effectively rebuilding the csv file in the function that it is including as the email attachment.

Does that make sense to you?

//***********************************************
// create_csv_string  and send_csv_mail  work together to send email with csv attached
function create_csv_string($data) {
  // Open temp file pointer
  if (!$fp = fopen('php://temp', 'w+')) return FALSE;
  // Loop data and write to file pointer
  foreach ($data as $line) fputcsv($fp, $line);
  // Place stream pointer at beginning
  rewind($fp);
  // Return the data
  return stream_get_contents($fp);
}

function send_csv_mail ($csvData, $body, $to, $subject, $from, $csvName) {
  // This will provide plenty adequate entropy
  $multipartSep = '-----'.md5(time()).'-----';
  // Arrays are much more readable
  $headers = array(
    "From: $from",
    "Reply-To: $from",
    "Content-Type: multipart/mixed; boundary=\"$multipartSep\""
  );
  // Make the attachment
  $attachment = chunk_split(base64_encode($csvData));
  // Structure the body of the message
  $body = "--$multipartSep\r\n"
        . "Content-Type: text/html; charset=ISO-8859-1; format=flowed\r\n"
        . "Content-Transfer-Encoding: 7bit\r\n"
        . "\r\n"
        . "$body\r\n"
        . "--$multipartSep\r\n"
        . "Content-Type: text/csv\r\n"
        . "Content-Transfer-Encoding: base64\r\n"
        . "Content-Disposition: attachment; filename=\"".$csvName."\"\r\n"
        . "\r\n"
        . "$attachment\r\n"
        . "--$multipartSep--";
   // Send the email
   mail($to, $subject, $body, implode("\r\n", $headers));
}

And here is the script that creates the csv file on my server and calls the function to send the email to the vendor.

<?php  // script name salt_order_gen.php - used to generate .csv file of
// yesterday's SALT purchase records on my server and email csv to vendor
session_start();
include "all_inc/config_site.php";
include "all_inc/functions.php";
$created = date("Y-m-d H:i:s", time()) ;

// query to get list of mem_ids from rank_record belonging to
// members that purchased SALT yesterday
$sql = "
  SELECT mem_id
  FROM rank_record
  WHERE DATE(presidential) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)
";
$P_sql = $sql; //  just to send in email for test
$result=mysql_query($sql);
$num_rows = mysql_num_rows($result);
if ($num_rows>0){
  $output='';
  while($row=mysql_fetch_array($result)){ // step through each purchase record
    $mem_id=$row[0];
    //  this query is for the information on each New buyer of SALT
    $sql = "
      SELECT fname, lname, phone, email
      FROM members
      WHERE mem_id = '$mem_id'
      LIMIT 1
    ";
    $results=mysql_query($sql);
    $rows=mysql_fetch_array($results);
    //  $SALTmem - contains fname / lname / phone / email
    $SALTmem ='"'.$rows[0].'",'.'"'.$rows[1].'",'.'"'.$rows[2].'",'.'"'.$rows[3].'"';
        $output .= $SALTmem."\n";
  }
  //print"<br>output is <br>".$output; // used for testing - display to screen

  // Create Domain / Directory / file / and check_file variables
  $domain = 'http://www.'.$siteurl;
  $directory = '/salt_orders/';
  $filename =  'TEST_SALT_'.date('Ymd',(strtotime('-1 day',strtotime($created)))).'.csv';
  $check_file = $domain.$directory.$filename; // used in curl read only
  // check to see if this filename has already been submitted
  // if yes - DO NOT resubmit it
  $ch = curl_init("$check_file");
  curl_setopt($ch, CURLOPT_NOBODY, true);
  curl_exec($ch);
  $retcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  // $retcode >= 400 -> not found, $retcode = 200, found.
  curl_close($ch);
  if($retcode=='200'){ // this file already exists
    // change admin email subject indicating duplicate file being created in error
    $subject="PROCESSING ERROR while ";
    $error_process="<p>THIS FILE HAS ALREADY BEEN PROCESSED AND SUBMITTED<br><br>INVESTIGATE IT</p>";
  }else{// New file name, so create it
    // This creates the .csv file on my server
    // and used for future verification that a daily file has not already been sent.
    $fd = fopen ( '.'.$directory.$filename, "w");// open file to write to
    $csv_create_result = fputs($fd, $output);// write the data to the opened file
    fclose($fd); // Close the file
    if( $csv_create_result){ //Send file via email to
      $vend = 'vendoremail@address.com'; //
      $vsub .= "New SALT order";
      $vmessage = $vsub." ATTACHED
        <p>File name is : ".$filename."</p>
        <p>Contains ".$num_rows." New SALT Orders from Yesterday : </p>
      ";
      // Send email with csv attachment
      send_csv_mail($output,$vmessage,$vend,$vsub,$support_mail,$filename) ;

    }else{
      $subject="Local CSV file creation failed while ";
      //tried to create local csv file but failed
    }
  }
}else{
  $subject="No New Records Available for ";
}
    //print "testing only<br>".$output; // just to display what was in the output variable to test
// Send email to admin no matter what the results
  $to = $support_mail;
  $subject .= "Generating SALT order";
  $message = $subject."
    <p>File name is : ".$filename."</p>
    <p>Checkfile value is : ".$check_file."</p>
    <p>P_sql is : ".$P_sql."</p>
    <p>Number of New SALT Orders Yesterday : ".$num_rows."</p>
    <p>Following is the content of the .csv file</p>
    <p>".$output."</p>";
  if (isset($error_process)){$message=$error_process.$message;}
  // Send email to admin for testing and recording, no matter what the result
    mail_sender($to,$support_mail,$subject,$message) ;
?>