...

View Full Version : Emailing An Excel File



tomyknoker
07-16-2007, 12:38 PM
hi all... ok i have set up a pretty neat php page which basically emails me what members have logged in over the past month... using a cron job i run this script every month, and get emailed results for the previous month... NOW the tricky part in this email i also want to have an excel file attached with that data... i can make the excel page separately but aren't sure how to get it all happening together... anyone know if it can be done?

not sure how possible it...

mlseim
07-16-2007, 02:19 PM
Where is the Excel file located, on your local PC, or in a website directory?

If you can upload the Excel file with a defined name that is either always
the same name, or a defined name that is part of the month/year....

example: excelfile.xls
or: excel0707.xls , excel0807.xls (month year .xls)

The PHP script could find and attach the file automatically if it knows
where to look and the name of the file.

You would just have to make sure that the file is there when it
does its email thing.

You'll find many examples of sending file attachments with PHP using Google.

tomyknoker
07-16-2007, 02:21 PM
Ahhhhhh ok got it, so then I would have to create the Excel file and save it somehow to the server... Is that possible?

Or can I actually make it all happen using the same php page?

mlseim
07-16-2007, 02:27 PM
Yes, with a simple PHP upload script, you could upload your Excel
file into a particular directory. That could be done on a webpage that
only you know the URL (no big security issues to deal with). A simple
form for uploading a file.

Your CRON job script would look for that file at any time.

You can't have a script upload a file from your PC automatically,
it requires your intervention.

EDIT:
It is possible to have an .exe program running on your PC that uploads
an Excel file automatically, from your PC to your website.... but that program
would need to be written (custom using C++, Visual Basic, or an Excel Macro?)
It's something you would need to do on your PC and have it running.
That's a bit beyond the scope of this forum.


.

tomyknoker
07-16-2007, 03:01 PM
Ok sounds good well I've been using a PEAR class to write and format the Excel file... At the top of this pgae I have the following code...


$xls->send("test.xls");

This basically names the file to be sent, do you know what I would need to change to upload?

NancyJ
07-16-2007, 04:26 PM
If you're not using any fancy excel features, its probably easier to send a csv - its plain text and can be read by any spreadsheet or text package. Plus php has built in functions for reading and writing csvs http://www.php.net/manual/en/function.fputcsv.php

ofcourse you dont need to save the file anywhere to send it as an attachment - you just need to know what would be in the file.

So you would want a script that runs your queries. Writes what would be in the file to a variable then sends the email.

Sitepoint has a good article about advanced email features - including how to send attachments http://www.sitepoint.com/print/advanced-email-php

tomyknoker
07-16-2007, 04:35 PM
Yea that's my drama, I'm using the PEAR Spreadsheet Class, so pretty code heavy but the php page with generates the Excel works flawlessy and my php code which emails me a report just in the email works flawlessy it's combining the two that I am really struggling with...

NancyJ
07-16-2007, 06:35 PM
at what point are you struggling? Did you read the sitepoint article?

mlseim
07-16-2007, 08:19 PM
So, if I got this right ... the only thing you need to do is
upload the file from your PC to your website using PHP?

The part about emailing it is working OK?

NancyJ
07-16-2007, 08:52 PM
As I interpret it he has 2 scripts, one that sends an email and another that generates an excel file and he needs to merge the 2 together - so that the excel file goes with the email.
Could be wrong though but I dont see any mention (from him at least) of the excel file being on his PC - or any other machine that isnt the server that is sending the email.

tomyknoker
07-16-2007, 11:30 PM
As I interpret it he has 2 scripts, one that sends an email and another that generates an excel file and he needs to merge the 2 together - so that the excel file goes with the email.
Could be wrong though but I dont see any mention (from him at least) of the excel file being on his PC - or any other machine that isnt the server that is sending the email.Hi Nancy, yes that's correct so what I still don't know is can it be done? Or do I need to run the upload script first and then run the email script second which would attach the file that I guess would have to sit in a directory on my server?

NancyJ
07-16-2007, 11:38 PM
ofcourse it can be done. Have you tried anything yet? Did you read the article on sending attachments?
If the excel file is on the server why do you need an upload script?
If you can create the file and you can send emails with attachments, where is the problem?

mlseim
07-17-2007, 12:42 AM
Searching Google, there are tons of tutorials and code snippets on
emailing a file attachment from your server. I would start there, and
it would be faster than communicating back and forth with these posts.

tomyknoker
07-17-2007, 02:37 AM
ofcourse it can be done. Have you tried anything yet? Did you read the article on sending attachments?
If the excel file is on the server why do you need an upload script?
If you can create the file and you can send emails with attachments, where is the problem?hi nancy, no at the moment i have a page which produces an excel file, it simply 'downloads' it to my desktop... what i need to change on the page though is instead of downloading the page, i want the page to upload to the server... i have done a search and am having no luck at all...

NancyJ
07-17-2007, 09:17 AM
no you dont. The file is being created on your server. You need to modify your code to either save the file to a location where a separate script can read it in to the email or save the content to a variable that you can send in the email.

tomyknoker
07-17-2007, 09:23 AM
Well this is my file... What do I need to change? I'm really confused...


<?php

include 'library/config.php';
include 'library/opendb.php';

$date = date('d/m/Y');

require_once "/home/19999/domains/mydomain/html/admin/Spreadsheet_Excel_Writer-0.9.1/Writer.php";
// Create an instance
$xls =& new Spreadsheet_Excel_Writer();

// Send HTTP headers to tell the browser what's coming
$xls->send("test.xls");

// Add a worksheet to the file, returning an object to add data to
$sheet =& $xls->addWorksheet(.$date);

$date = date('d/m/Y');
$month = date('m');

if ($month == "12") {
$theMonth = "01";
}

else {
$theMonth = $month+1;
}

$monthText = date("F", mktime(0,0,0,$theMonth,1,0));

$query = "SELECT * tblmembers ORDER BY State ASC, LastName ASC";

$result = mysql_query($query);
$num = mysql_num_rows($result);
$i=1;

while ($row = mysql_fetch_array($result)) {

// The the row height
$sheet->setRow(0,17);
$sheet->setRow($i,17);

// Set the column width for the first 4 columns
$sheet->setColumn(0,$i,15);
$sheet->setColumn(7,8,30);
$sheet->setColumn(18,18,30);
$sheet->setColumn(20,20,30);
$sheet->setColumn(31,32,30);

# Add the column headings
// Set up some formatting
$colHeadingFormat =& $xls->addFormat();
$colHeadingFormat->setBold();
$colHeadingFormat->setFontFamily('Helvetica');
$colHeadingFormat->setSize('10');
$colHeadingFormat->setAlign('left');

$colData =& $xls->addFormat();
$colData->setAlign('left');

// An array with the data for the column headings
$colNames = array('MembersID', 'First Name', 'Last Name', 'Date Of Birth', 'Gender', 'Phone Number', 'Mobile Number', 'Email Address', 'Address', 'Suburb', 'City', 'State', 'Country', 'Postcode', 'How Did You Hear About Us');

// Add all the column headings with a single call
// leaving a blank row to look nicer
$sheet->writeRow(0,0,$colNames,$colHeadingFormat);

// The cell group to freeze
// 1st Argument - vertical split position
// 2st Argument - horizontal split position (0 = no horizontal split)
// 3st Argument - topmost visible row below the vertical split
// 4th Argument - leftmost visible column after the horizontal split
$freeze = array(1,0,1,0);

// Freeze those cells!
$sheet->freezePanes($freeze);

$sheet->write($i,0,"{$row['ID']}",$colData);
$sheet->write($i,1,"{$row['FirstName']}",$colData);
$sheet->write($i,2,"{$row['LastName']}",$colData);
$sheet->write($i,4,"{$row['Gender']}",$colData);
$sheet->write($i,5,"{$row['PhoneNumber']}",$colData);

$sheet->writeString($i,6,"{$row['MobileNumber']}",$colData);
$sheet->write($i,7,"{$row['Email']}",$colData);
$sheet->write($i,8,"{$row['Address']}",$colData);
$sheet->write($i,9,"{$row['Suburb']}",$colData);
$sheet->write($i,10,"{$row['City']}",$colData);
$sheet->write($i,11,"{$row['State']}",$colData);
$sheet->write($i,12,"{$row['Country']}",$colData);
$sheet->write($i,13,"{$row['PostCode']}",$colData);
$sheet->write($i,14,"{$row['HearAboutUs']}",$colData);
$i++;

}


// Finish the spreadsheet, dumping it to the browser
$xls->close();

include 'library/closedb.php';
?>

NancyJ
07-17-2007, 09:28 AM
read the documentation for your spreadsheet class.

tomyknoker
07-17-2007, 09:28 AM
Read it all the help there said it couldn't be done...

NancyJ
07-17-2007, 09:40 AM
well thats just not true. If that data can be written to the browser then it must be possible to write to a file or to a variable.
Ofcourse... it would be much simpler with a csv

tomyknoker
07-17-2007, 09:50 AM
Well I posted on the PEAR forum and have been told it can't be... So I just don't know... Maybe someone can see something? http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php

NancyJ
07-17-2007, 09:57 AM
guess they were wrong because I just did it... in 2 different ways
When you create the object send a filename and remove the 'send' line. This will save the file to your server.
Or you can leave the initial code as it is and use the output buffer to capture the data to a variable - see ob_start, ob_get_contents and ob_end_clean.
Which you can then write to a file, output to screen or send in an email.

tomyknoker
07-17-2007, 09:58 AM
What really? Ahhh jeez... So then with the first option how does that work? Can you please tell me I'm desperate...

NancyJ
07-17-2007, 10:02 AM
what do you mean 'how does that work'? What dont you understand? I've told you 2 different ways to do what you want but I'm not psychic

tomyknoker
07-17-2007, 10:12 AM
Can you explain possibly in the form of code how I do it?

tomyknoker
07-17-2007, 10:14 AM
Is it as simple as doing this?

From

// Create an instance
$xls =& new Spreadsheet_Excel_Writer();

// Send HTTP headers to tell the browser what's coming
$xls->send("test.xls");

To

// Create an instance
$xls =& new Spreadsheet_Excel_Writer(mydomain/html/user/reports/test.xls);

// Send HTTP headers to tell the browser what's coming
//$xls->send("test.xls");

NancyJ
07-17-2007, 10:15 AM
nearly. You need to put quotes around your filepath
..ofcourse that assuming that 'mydomain/html/user/reports/test.xls' is the correct path on your server.

tomyknoker
07-17-2007, 10:19 AM
Hmmm so I'm getting closer :) It didn't work... It didn't produce an error, I think it's to do with the path... So does the above mean it's looking for that path? Should I not be including test.xls?

NancyJ
07-17-2007, 10:22 AM
I cant tell you what the path will be because I dont know the structure of your server, *but* try putting a / at the beggining of the path. At the moment its looking for that path relative to the directory the script is running from. So either put the exact server path in starting with a / or make the path relative from where you're running it from.

tomyknoker
07-17-2007, 10:25 AM
Miracle it worked didn't have the right permissions on the folder!

tomyknoker
07-17-2007, 11:49 AM
Ok almost there... I've added the php attachment code to my php page, and the email comes through but with no attachment, do I have the code in the wrong place? I moved it to the top of the page and then it looked like it came through but as a whole heap of text in the email...


$date = date('d/m/Y');
$month = date('m');

if ($month == "12") {
$theMonth = "01";
}

else {
$theMonth = $month+1;
}

$monthText = date("F", mktime(0,0,0,$theMonth,1,0));

//$query = mysql_query("SELECT * FROM `tblmembers` WHERE (MONTH(DateOfBirth) = '{$theMonth}') AND `MemberApproved`='A'");

$query = "SELECT * tblmembers WHERE ID='1000'";

if ($results = mysql_query($query)) {
if (mysql_num_rows($results) < 1) {
die('No members are having birthdays next month');

} else {
while ($qry = mysql_fetch_array($results)) {

if (empty ($qry["Firstname_mng"]) AND empty ($qry["Lastname_mng"])) {
$repFirstname = "uknown";
} else {
$Firstname_mng = $qry["Firstname_mng"];
$Lastname_mng = $qry["Lastname_mng"];
}
$firstName = $qry["LastName"];
$lastName = $qry["FirstName"];
$Email = $qry["Email"];
$DateOfBirth = date("d/m/Y", strtotime($qry["DateOfBirth"]));
$State = $qry["State"];

$login .= sprintf("<p><strong>Name:</strong> %s %s, <a href='mailto:%s'>%s</a><br /><strong>DOB:</strong> %s<br /> <strong>State:</strong> %s<br /><strong>Mng:</strong> %s %s<br /></p>",
$firstName,
$lastName,
$Email,
$Email,
$DateOfBirth,
$State,
$Firstname_mng,
$Lastname_mng
);
}
}
}
$message .= sprintf("<html>
<body>
<strong>Celebrating birthdays in $monthText</strong><br />
-----------------------------------------<br />
%s
</body>
</html>",$login);

$semi_rand = md5( time() );

$mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";

$headers .= "\nMIME-Version: 1.0\n" .
"Content-Type: multipart/mixed;\n" .
" boundary=\"{$mime_boundary}\"";
$message = "This is a multi-part message in MIME format.\n\n" .
"--{$mime_boundary}\n" .
"Content-Type: text/plain; charset=\"iso-8859-1\"\n" .
"Content-Transfer-Encoding: 7bit\n\n" .
$message . "\n\n";

$data = chunk_split( base64_encode( $data ) );

$message .= "--{$mime_boundary}\n" .
"Content-Type: {$fileatttype};\n" .
" name=\"{$fileattname}\"\n" .
"Content-Disposition: attachment;\n" .
" filename=\"{$fileattname}\"\n" .
"Content-Transfer-Encoding: base64\n\n" .
$data . "\n\n" .
"--{$mime_boundary}--\n";

$fileatt = "/home/mydomain.com/html/reports/test.xls";
$fileatttype = "application/xls";
$fileattname = "newname.xls";

$file = fopen( $fileatt, 'rb' );
$data = fread( $file, filesize( $fileatt ) );
fclose( $file );

$addresses = explode(', ', 'em@myemail.com');
$subject = $subject .'Members Having Birthdays In '. $monthText;
$headers = "FROM: Me<em@myemail.com>\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

foreach ($addresses as $to) {
mail($to,$subject,$message,"$headers");
}

?>

NancyJ
07-17-2007, 12:14 PM
yes, its in the wrong place. You're adding it to the message before you've actually set it.

tomyknoker
07-17-2007, 12:20 PM
I tried this but it only send a whole heap of text in the message...


$date = date('d/m/Y');
$month = date('m');

if ($month == "12") {
$theMonth = "01";
}

else {
$theMonth = $month+1;
}

$monthText = date("F", mktime(0,0,0,$theMonth,1,0));

//$query = mysql_query("SELECT * FROM `tblmembers` WHERE (MONTH(DateOfBirth) = '{$theMonth}') AND `MemberApproved`='A'");

$query = "SELECT * tblmembers WHERE ID='1000'";

if ($results = mysql_query($query)) {
if (mysql_num_rows($results) < 1) {
die('No members are having birthdays next month');

} else {
while ($qry = mysql_fetch_array($results)) {

if (empty ($qry["Firstname_mng"]) AND empty ($qry["Lastname_mng"])) {
$repFirstname = "uknown";
} else {
$Firstname_mng = $qry["Firstname_mng"];
$Lastname_mng = $qry["Lastname_mng"];
}
$firstName = $qry["LastName"];
$lastName = $qry["FirstName"];
$Email = $qry["Email"];
$DateOfBirth = date("d/m/Y", strtotime($qry["DateOfBirth"]));
$State = $qry["State"];

$fileatt = "/home/mydomain.com/html/reports/test.xls";
$fileatttype = "application/xls";
$fileattname = "newname.xls";

$file = fopen( $fileatt, 'rb' );
$data = fread( $file, filesize( $fileatt ) );
fclose( $file );

$login .= sprintf("<p><strong>Name:</strong> %s %s, <a href='mailto:%s'>%s</a><br /><strong>DOB:</strong> %s<br /> <strong>State:</strong> %s<br /><strong>Mng:</strong> %s %s<br /></p>",
$firstName,
$lastName,
$Email,
$Email,
$DateOfBirth,
$State,
$Firstname_mng,
$Lastname_mng
);
}
}
}
$message .= sprintf("<html>
<body>
<strong>Celebrating birthdays in $monthText</strong><br />
-----------------------------------------<br />
%s
</body>
</html>",$login);

$semi_rand = md5( time() );

$mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";

$headers .= "\nMIME-Version: 1.0\n" .
"Content-Type: multipart/mixed;\n" .
" boundary=\"{$mime_boundary}\"";
$message = "This is a multi-part message in MIME format.\n\n" .
"--{$mime_boundary}\n" .
"Content-Type: text/plain; charset=\"iso-8859-1\"\n" .
"Content-Transfer-Encoding: 7bit\n\n" .
$message . "\n\n";

$data = chunk_split( base64_encode( $data ) );

$message .= "--{$mime_boundary}\n" .
"Content-Type: {$fileatttype};\n" .
" name=\"{$fileattname}\"\n" .
"Content-Disposition: attachment;\n" .
" filename=\"{$fileattname}\"\n" .
"Content-Transfer-Encoding: base64\n\n" .
$data . "\n\n" .
"--{$mime_boundary}--\n";

$addresses = explode(', ', 'em@myemail.com');
$subject = $subject .'Members Having Birthdays In '. $monthText;
$headers = "FROM: Me<em@myemail.com>\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

foreach ($addresses as $to) {
mail($to,$subject,$message,"$headers");
}

?>

NancyJ
07-17-2007, 12:35 PM
for try taking out the encoding. Are you sending it to hotmail? I've seen problems before where hotmail just displays the encoded text.

so take out the $data = chunk_split(base_64($data)) part and change "Content-Transfer-Encoding: base64\n\n" . to "Content-Transfer-Encoding: 7bit\n\n" .

then see what you get

tomyknoker
07-17-2007, 12:44 PM
Interesting ok well now it displays the excel file actually in the message... wierd...

NancyJ
07-17-2007, 01:06 PM
ok, well the only thing that jumps out at me is to try replacing the "\n" s with "\r\n"

NancyJ
07-17-2007, 01:09 PM
You could also try using application/octet-stream at the content-type

tomyknoker
07-17-2007, 01:10 PM
ug still no luck!

tomyknoker
07-17-2007, 01:38 PM
Ok I thought I'd be smart and just go with PHPMailer as it seems worlds easier... But I have an error there too... Is anything blaring out at you that doesn't look right? Maybe it's the sprintf section... Not sure...


<?php

require("/home/html/phpmailer/class.phpmailer.php");

$mail = new PHPMailer();

$mail->From = "email@mydomain.com";
$mail->FromName = "My Domain";
$mail->Host = "smtp.mydomain.com";
$mail->Mailer = "smtp";

include 'library/config.php';
include 'library/opendb.php';

$date = date('d/m/Y');
$month = date('m');

if ($month == "12") {
$theMonth = "01";
}

else {
$theMonth = $month+1;
}

$monthText = date("F", mktime(0,0,0,$theMonth,1,0));

$query = "SELECT * FROM tblmembers ";

$results = mysql_query($query);

while ($qry = mysql_fetch_array ($results))
{

$login .= sprintf("<p><strong>Name:</strong> %s %s, <a href='mailto:%s'>%s</a><br /><strong>DOB:</strong> %s<br /> <strong>State:</strong> %s<br /><strong>Representative:</strong> %s %s<br /></p>",
$firstName,
$lastName,
$Email,
$Email,
$DateOfBirth,
$State,
$repFirstname,
$repLastname
);

// HTML body
$body = "Hello <font size=\"4\">" . $qry["FirstName"] . "</font>, <p>";
$body .= "<i>Your</i> personal photograph to this message.<p>";
$body .= "Sincerely, <br>";
$body .= "PHPMailer List manager ".$login."";

// Send the Email
$mail->AddAddress("email@mydomain.com", "Me");
$mail->Subject = "Having Birthdays In ". $monthText;
$mail->Body = $body;
$mail->AddAttachment("/home/html/reports/test.xls", "new_name.zip"); // optional name

if(!$mail->Send())

{
echo "There was an error sending the message";
exit;
}

}

?>

NancyJ
07-17-2007, 01:47 PM
what error are you getting?

tomyknoker
07-17-2007, 01:49 PM
It's just echoing out the error message at the bottom... "There was an error sending the message";

NancyJ
07-17-2007, 01:59 PM
does php mailer have any error reporting? Can you go into the file and get an echo out of what its trying to send - that might help with debugging

NancyJ
07-17-2007, 02:15 PM
try this


$fileatt = "/home/mydomain.com/html/reports/test.xls";
$type = "application/x-msdownload";
$name = "newname.xls";

$file = fopen( $fileatt, 'rb' );
$data = fread( $file, filesize( $fileatt ) );
fclose( $file );
$data = chunk_split( base64_encode( $data ) );

$uid = strtoupper(md5(uniqid(time())));

$header = "From: Me<me@myemail.com>\n";
$header .= "MIME-Version: 1.0\n";
$header .= "Content-Type: multipart/mixed; boundary=$uid\n";

$header .= "--$uid\n";
$header .= "Content-Type: text/html\n";
$header .= "Content-Transfer-Encoding: 8bit\n\n";
$header .= "$message_text\n";

$header .= "--$uid\n";
$header .= "Content-Type: $type; name=\"$name\"\n";

$header .= "Content-Transfer-Encoding: base64\n";
$header .= "Content-Disposition: attachment; filename=\"$name\"\n\n";
$header .= "$data\n";

$header .= "--$uid--";

$subject = $subject .'Members Having Birthdays In '. $monthText;

foreach ($addresses as $to) {
mail($to,$subject,$message,"$headers");
}

If it doesnt work, try creating a spreadsheet in excel, upload it to your server and try to send it as an attachement using the same code - if that works then the problem is in the file we created not in the emailing code.

tomyknoker
07-17-2007, 03:13 PM
Hey Nancy... Thanks again, that was a good idea ok gave that a try but still the same wierd error's... Hmmm very confused...

NancyJ
07-17-2007, 03:21 PM
so you're still getting the email arriving in the body of the text? (I'm assuming its a load of gobble-de-gook or the base64 encoded text) At least we know its not a problem with the file then. What email address (hotmail/yahoo/gmail etc) are you sending it to/ which email client are you using? Do you have a different address you could try. The problem might be at the recieving end not the sending.

tomyknoker
07-17-2007, 03:46 PM
hey was wondering how to type 'gobble-de-gook :) well it's showing the file, actually on the test file it didn't... it was gobble-de-gook but on the actual previous tries with the file on the server, it show's the excel file but it's scattered through the email body (if that makes sense)... i tried a couple of different acounts, it a proper email account, i also checked directly through the webmail... no luck... it just doesn't want to actually add an attachment...

NancyJ
07-17-2007, 03:51 PM
can you get it to send to hazel@hazelryan.co.uk so I can see what is coming through and view the headers. (fwding wont work, it has to come direct from the script)

mlseim
07-17-2007, 05:42 PM
I found this:

http://www.thescripts.com/forum/thread652749.html

He had trouble too ... and it was a "newline" problem.
See the answer to his question on the post.

Apparently, things are funny with .xls files?

tomyknoker
07-19-2007, 02:42 PM
hi everyone i didn't get these replies to my email i thought no one cared! :)

ok an update nancy and mlseim your help was brilliant! i ended up using phpmailer... and it seems to work, meant i had to redo the file but it all worked well so i don't know what the issue was, but i must say phpmailer is great! if anyone hasn't used it i recommend!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum