Automated HTML Email Generation Script Summary

Overview

I wrote a Python script to automate the generation of a formatted HTML email summarizing the weekly accomplishments of a team working on specific Jira Epics. It pulls raw data from Excel files synchronized from SharePoint, processes the data, and creates an HTML summary that can be pasted directly into a weekly email. As the work was done at a previous employer I'm not permitted to share the actual code.

Key Tasks Performed

• Reading Data: Loads two Excel files with Epic information and focus areas.
• Merging Data: Merges the two datasets based on Epic ID to ensure focus areas are aligned.
• Data Cleaning: Removes unnecessary rows and fills missing values (e.g., assigns 'No PM Assigned' for missing PMs).
• Sorting by Priority: Sorts the Epics by Outcome Priority for proper display order.
• Generating HTML: Creates an HTML email summary grouped by focus areas with bullet-pointed accomplishments.
• Saving Output: Saves the HTML content to a file for easy use in email communications.
• Logging: Appends execution logs to log.txt with timestamps for tracking.

Purpose

The purpose of this script is to save time and ensure consistency by automating the creation of weekly email summaries. What previously took several hours can now be done in seconds, ensuring smooth and efficient reporting.

Output Example

The email generated will include:

• Focus Area Headers
• Epic Summary: Epic Name - Epic ID, Outcome #, Assigned PM
• Accomplishment 1
• Accomplishment 2

Conclusion

This script leverages pandas for data handling and ensures quick, consistent output. It enables teams to generate professional reports without manual formatting, saving valuable time.