Task Summary Email - Order Tasks

steph_aw ✭✭
edited 03/23/23 in Smartsheet Basics

I'm trying to create a summary email that goes out to our entire team that shows three sections:

Overdue Tasks

Due This Month

Assigned This Week

I'd be fine with just a simple table under each header, showing the matching tasks.

When I try to create an alert, it looks like I can use brackets to pull Task info into the body of the email, but I can't organize the tasks that are pulled in.

Is there a way to create this kind of email?



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @steph_aw

    Do you have a column in your sheet to identify these three different buckets?

    What I would do in this instance is set up a formula in a helper column to automatically identify if a row is either overdue or due this week:

    =IF(Status@row = "Complete", "", IF([Due Date]@row <= TODAY(), "Overdue", IF(AND(MONTH([Due Date]@row) = MONTH(TODAY())), "Due this Month")))

    You can hide this column if you don't want to see it in your sheet, but I've set some Conditional Formatting on mine to make it clear what each row has associated.

    Next, I would create a Row Report from the sheet and filter it so that only rows with content in this helper column show up, OR rows that have been Assigned in the last 7 days.

    You can Sort the Report so the tasks are ordered by Overdue or Due this Month, then set up a recurring email that contains the Report content.

    It would, however, send these tasks in a PDF in the email. Would that work for your purposes?

    See: Share a Report as an Attachment in Email

    Let me know if this makes sense and will work for you! If not, it would be helpful to see a screen capture of your current set-up, but please block out sensitive data.