Email a Queue overdue persons report via Excel/CSV file

The weeks fly by so fast. It seems like we just completed last week’s tutorial about emailing a queue overdue persons report.

This week’s tutorial will build and extend upon last week’s tutorial. We focus on saving the data to a Excel/CSV file, and emailing the file as an email attachment.

It goes without saying, but I won’t cover the detailed nuts and bolts of the previous tutorial.

You have to read the previous tutorials for logical explanation of the code in its entirety.

Without further ado, let’s start coding today’s tutorial.

Start from where we last left off…

Copy and paste last week’s tutorial codebase into the text editor of your choice, naming and saving the queue-op-email-attach.php.

As in previous tutorials, the necessary comments, and file inclusions and requirements should already be included.

One of the first modifications to be made to the code is adding a timeNow variable that uses the built-in PHP date method to provide a timestamp for when the report was last run.

We didn’t do this in the last tutorial but will need it for this tutorial. You’ll see why here shortly.

Preparing and saving the report table in HTML and Excel/CSV format

The good news is that you have the HTML table that is used to display the report via email and the web browser.

However, we’re going to make some slight changes. Notice that I’ve added the previously mentioned timeNow variable in two places.

I also created a new variable to capture the data for saving to the Excel/CSV file: fileOverDue.

Writing the data to Excel/CSV file

It’s quite simple to write our report data to the Excel/CSV file. It’s only a fee lines of code.

For greater clarity, simply review the following tutorial for in-depth review: how to parse and save xml as CSV/Excel.

First, specify the file name and define it as the the reportFile variable. I chose to use the date and queue overdue persons text as the cvs file name (e.g. 2016-06-13-Queue-Overdue-Persons.csv).

Once named, we write the fileOverDue variable data to the file using open and write methods.

Preparing the email variables and file attachment content logic

To prepare the email to be sent, the following variables must be defined with their respective values:

  • to: the email address or email addresses separated by commas (e.g., “test@gmail.com,test@email.com”)
  • from: a from email address
  • from_name: name of person or organization the email should appear from
  • subject: subject of email
  • message: this value should be the txtOverDue variable

Unlike the last tutorial, there is a bit more logic to apply when sending a file as an email attachment.

One of the first things we’ll do is to apply some logic to check if the reportFile variable value, the filename you’ve assigned the Excel/CSV file, exists using a if statement and the built-in PHP file_exists method.

If the file exists, then define the following file attribute variables with their respective values:

  • file_tmp_name: this value should be reportFile variable
  • file_name: this value should be the file_tmp_name variable
  • file_size: pass the file_tmp_name variable as an argument to built-in PHP file_size method
  • file_type: pass the file_tmp_name variable as an argument to built-in PHP file_type method
  • file_error: leave blank

Once the file attribute variables have been successfully defined and associated to their respective values, then define and assign the following variables:

Finally pass the handle variable as an argument to the built-in PHP fclose method to close the file.

Next define an encoded_content variable and use built-in PHP chunk_split and base64_encode methods to encode the content variable (see below).

After this, now close the if statement.

Preparing the email header, body, and attachment

The next step in the process is providing a hashing value for the boundary, defining the boundary variable and using built-in PHP md5 method to create a hash.

Just as in the previous tutorial, define and associate the appropriate headers variable and respective values.

Notice that where the boundary variable is located at via the body and headers variable values.

Within the body variable value definition, use built-in PHP chunk_split and base64_encode methods to encode the message variable HTML content.

Attaching Excel/CSV file to email and sending

To attach your file to the message, make one final check to ensure the file does exist before making an attempt to attach it to the email.

To do this, we’ll use the a if statement and the built-in PHP file_exists method once more.

Simply define and associate the appropriate body variable and respective values as shown in the code below.

Use the built-in PHP rand method to provide a randomly generated attachment id between 2,020 and 505,050.

Next, define a sentMail variable and use the built-on PHP mail method to send the email, passing to it the following variables in their respective order: to, subject, body, and headers.

Last but not least, use an if else statement to validate whether or not the sentMail variable actually succeeded in sending email and returned true or boolean value.

Oh yes, and if you wanted to see the table via the web browser when you execute the file, then you can simply echo the message variable. That’s that.

Time to put it together and test your skills

Put all the code together, save and name your file accordingly, and open and execute the file in a web browser.

You should receive an email with a file attachment should you have email setup on your web server and executed this tutorial without fail.

Please don’t hesitate to let me know if you have comments or questions. I’d be glad to assist you where I can.

That’s all I have this week. Next week, we’ll have a brand new tutorial to set our eyes upon, Lord willing. See you then.

 


Please comment if this tutorial has helped you.