Send custom e-mail after X days WooCommerce order

Question

I am trying to create a function that should send a custom E-mail if a "processing order" is older than five days.

I am a bit stuck on my code. It doesn't seem to work - nothing is happening. I am also wondering how I could add order ID into the body of the custom email?

My code:

// Lookup DB for orderdate older than 5 days AND send E-mail
function expire_after_x_days(){
        global $wpdb;
    // Get current time
        $today = date("mdy");

    // set time to expire
        $time_to_expire = "-5 days";
        $expiration_date = date("mdy", strtotime( $today . $time_to_expire));

    // Get orders with processing status
        $result = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE post_type = 'shop_order' AND post_status = 'wc-processing'");

        if( !empty($result)) foreach ($result as $order){
            // Get order's time
            $order_time = get_the_time('mdy', $order->ID );

    // Compare order's time with current time
        if ( $order_time < $expiration_date ){

    // send custom email   
        $to = 'test@gmail.com';
        $subject = 'Test subject of my email';
        $body = 'The email body content. Perhaps also write order ID';
        $headers = array('Content-Type: text/html; charset=UTF-8');

wp_mail( $to, $subject, $body, $headers );
            }
        }
} 
    add_action( 'admin_footer', 'expire_after_x_days' );

Answer 1

Inspired from Send a custom reminder email for WooCommerce On-Hold orders after two days answer code, the following revisited code will be triggered once daily and will send a follow-up email with a custom message on processing orders after 5 days (for "processing" order status):

/*Add follow up email*/
add_action( 'restrict_manage_posts', 'follow_up_email_processing_order' );
function follow_up_email_processing_order() {
    global $pagenow, $post_type;

    if( 'shop_order' === $post_type && 'edit.php' === $pagenow
        && get_option( 'processing_orders_followup_daily_process' ) < time() ) :

    $days_delay = 5; // 5 Days
    $one_day    = 24 * 60 * 60;
    $today      = strtotime( date('Y-m-d') );

    $processing_orders = (array) wc_get_orders( array(
        'limit'        => -1,
        'status'       => 'processing',
        'date_modified' => '<' . ( $today - ($days_delay * $one_day) ), //Get modified date to know how many time has passed since it changed its status to processed
    ) );

    if ( sizeof($processing_orders) > 0 ) {
        $reminder_text = __("Followup email sent $today.", "woocommerce");

        foreach ( $processing_orders as $order ) {
            $order->update_meta_data( '_send_processing', true );
            $order->update_status( 'follow-up', $reminder_text );

            $wc_emails = WC()->mailer()->get_emails(); // Get all WC_emails objects instances
            $wc_emails['WC_Email_Customer_Processing_Order']->trigger( $order->get_id() ); // Send email
        }
    }
    update_option( 'processing_orders_followup_daily_process', $today + $one_day );

    endif;
}


add_action ( 'woocommerce_email_order_details', 'processing_followup_notification', 5, 4 );
function processing_followup_notification( $order, $sent_to_admin, $plain_text, $email ){
    if ( 'customer_processing_order' == $email->id && $order->get_meta('_send_processing') ){
        $order_id     = $order->get_id();
        //$order_link   = wc_get_page_permalink('myaccount').'view-order/'.$order_id.'/';
        $order_link = $order->get_checkout_order_received_url();
        $order_number = $order->get_order_number();

        echo '<h2>'.__("We haven't forgoten about you!.").'</h2>
        <p>'.sprintf( __("CUSTOM MESSAGE HERE… %s"), 
            '<a href="'.$order_link.'">'.__("Your My account order #").$order_number.'<a>'
        ) .'</p>';

        $order->delete_meta_data('_send_processing');
        $order->save();
    }
}

/* Modify the subject */
add_filter('woocommerce_email_subject_customer_processing_order', 'change_followup_email_subject', 1, 2);
function change_followup_email_subject( $subject, $order ) {
    global $woocommerce;

    $first_name = strtok( $order->billing_first_name,  ' ' );

    $subject = sprintf( '¡%s Your order is still being processed!', $first_name );

    return $subject;
}