Skip to content

Email Monthly Spreadsheet Reports from WordPress

I’ve been working on a project where we have a very custom Customer Service form and we wanted to start sending out monthly excel reports with all the issues from the past month.

The form is a bit too custom to switch over to a plugin, so I opted to do the set-up by hand.  At the start the form was only sending out emails. So first I needed to create a custom post type to hold all the responses.  you can create one by adding the following code to your functions.php file.  WordPress has a breakdown of all the arguments in their documentation.

1
2
3
4
5
6
7
8
9
10
11
//Support Content Type
$args = array(
    'label'              => 'Support Request',
    'show_ui'             => true,
    'show_in_admin_bar'   => false,
    'public'              => false,
    'menu_position'       => 25,
    'menu_icon'           => 'dashicons-editor-help',
    'supports'            => array( 'title','editor','custom-fields')
);
register_post_type( 'support_request', $args );

Next, I needed to add in the following after sending the email off on the custom form page.  I’m setting the post title and post message to be identical to the email title and body that’s already been generated by the form.  I’m also adding in two custom fields “name” and “email.”  Those are just for example, you can add as you like and your form will likely dictate those.

1
2
3
4
5
6
7
8
9
//add to support request content type
$postarr = array(
    'post_title' => $title,
    'post_content'=> $message,
    'post_type' => 'support_request'
);
$post_id = wp_insert_post( $postarr );
update_post_meta($post_id,'Name',$_REQUEST['customer-name']);
update_post_meta($post_id,'Email',$_REQUEST['customer-email']);

After that, I created a page to generate the excel file.  I created a custom “generate_customer_report_excel.php” in my theme’s directory so it wouldn’t fatten up my functions.php file.  I’m going to create the filename ($output_filename) in the functions.php file to keep things DRY. If you want a frequency different then 30 days, you’ll need to change the “‘after’ => ‘- 30 days‘” line. You will also need to create a “support_requests” folder in your uploads folder. Here’s an example of what that page looks like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
//build your query 
$args = array(
    'post_type' => 'support_request',
    'post_status' => array('publish', 'pending', 'draft', 'auto-draft', 'future', 'private', 'inherit'),
    'posts_per_page' => -1,
    'date_query'    => array(
        'column'  => 'post_date',
        'after'   => '- 30 days'
    )
);
 
query_posts($args);
 
// Prepare our csv download
$output_handle = fopen(WP_CONTENT_DIR . '/uploads/support_requests/'.$output_filename ,"w");
 
// Insert header row
fputcsv( $output_handle, ['Name','Email','Message']);
 
// The Loop
while ( have_posts() ) : the_post();
    $id = get_the_ID();
    $row = array(
        get_post_meta( $id, 'Name', false )[0],
        get_post_meta( $id, 'Email', false )[0],
        get_the_content()
    );
    fputcsv( $output_handle, $row);
endwhile;
if(!have_posts()){fputcsv( $output_handle, ['No Support Requests']);}
 
// Close output file stream
fclose( $output_handle );

Finally, I want to use WP Cron to set a monthly cron job to send out the email with the CSV attached.  To change the interval you’ll need to change the “‘interval’ => 2592000,”  and “$DownloadReportFrom = date(‘m_d_Y’,strtotime(“-1 month”));” to be the time span you want. Also, you’ll definaly need to adjust the to and from emails within the support_monthly_report function. This is the code to go in the functions.php file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
//Support Question reports
function my_cron_schedules($schedules){
    if(!isset($schedules["monthly"])){
        $schedules["monthly"] = array(
            'interval' => 2592000,
            'display' => __('Once a month'));
    }
    return $schedules;
}
add_filter('cron_schedules','my_cron_schedules');
 
add_action('init','support_monthly_report_schedule');
add_action('support_monthly_report_job','support_monthly_report');
 
function support_monthly_report_schedule(){
    if (!wp_next_scheduled ( 'support_monthly_report_job' )) {
        wp_schedule_event(time(), 'monthly', 'support_monthly_report_job');
    }
}
 
function support_monthly_report(){
    $email = 'TOEMAIL';
    $subject = 'Monthly Report';
    $message = 'EMAILBODY';
 
    $DownloadReportFrom = date('m_d_Y',strtotime("-1 month"));
    $DownloadReportTo = date('m_d_Y',strtotime("now"));
    $output_filename = 'Support_report-' . $DownloadReportFrom .'-'. $DownloadReportTo  . '.csv';
 
    include('generate_customer_report_excel.php');
    usleep(300000);
    $attachments = array( WP_CONTENT_DIR . '/uploads/support_requests/'.$output_filename );
    $headers = 'From: FROMNAME ' . "\r\n";
 
    wp_mail($email,$subject, $message, $headers, $attachments );
}

And that should do it! You’ll be sending snazzy spreadsheets out in no time 😀

Published inPHPScripting

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *