Automate Weekly Email Reports with Python: Save 5 Hours Every Week

Every Friday at 4 PM, you face the same dreaded task: compiling the weekly report. You pull data from three different systems, paste it into Excel, create charts, write the summary, and email it to 12 stakeholders. Two hours vanish—time you could spend on actual strategic work.

What if that entire process ran automatically while you were in your Friday afternoon meeting? I'll show you how to build a Python script that generates and emails professional weekly reports with zero manual intervention.

What You'll Build

By the end of this tutorial, you'll have a Python script that:

Pulls data from CSV files, databases, or APIs automatically
Calculates key metrics and trends
Generates professional charts and visualizations
Formats everything into a clean HTML email
Sends the report to your distribution list
Runs automatically every Friday at 3 PM

Time investment: 2 hours to build Time saved: 2+ hours every single week (100+ hours per year)

Prerequisites

You'll need:

Python 3.8 or higher installed
Basic Python knowledge (variables, functions, loops)
Access to your company's email server or Gmail account
Sample data to work with (I'll provide examples)

Required libraries:

bash

1pip install pandas matplotlib smtplib email
2pip install schedule  # for automation
3pip install openpyxl  # if reading Excel files

The Problem: Manual Reporting is a Time Drain

Let's break down where time goes in manual reporting:

Data collection (30 minutes): Log into multiple systems, export CSVs, copy data
Data cleaning (20 minutes): Fix formatting issues, handle missing values, merge sources
Analysis (25 minutes): Calculate metrics, create pivot tables, identify trends
Visualization (20 minutes): Build charts, format them consistently
Formatting (15 minutes): Paste into email template, add commentary
Distribution (10 minutes): Send to stakeholders, handle bounces

Total: 2 hours — and that's if nothing goes wrong.

Solution Architecture

Here's our automated approach:

Prompt

┌─────────────────┐
│  Data Sources   │ (CSV, Database, API)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Data Processing │ (pandas)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Visualization  │ (matplotlib)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ HTML Formatting │ (email.mime)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Email Sending  │ (smtplib)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Automation     │ (schedule/cron)
└─────────────────┘

Let's build this step by step.

Step 1: Set Up Project Structure

Create a clean project structure:

bash

1mkdir weekly-report-automation
2cd weekly-report-automation
3
4# Create folder structure
5mkdir data
6mkdir reports
7mkdir charts
8
9# Create Python files
10touch report_generator.py
11touch email_sender.py
12touch config.py
13touch main.py

File purposes:

report_generator.py: Data processing and analysis logic
email_sender.py: Email formatting and sending
config.py: Configuration settings (email, data sources)
main.py: Orchestrates the entire workflow

Step 2: Configure Email Settings

Create config.py:

python

1# config.py
2import os
3from datetime import datetime
4
5# Email configuration
6EMAIL_CONFIG = {
7    'smtp_server': 'smtp.gmail.com',  # or your company's SMTP server
8    'smtp_port': 587,
9    'sender_email': 'your-email@company.com',
10    'sender_password': os.getenv('EMAIL_PASSWORD'),  # Store securely!
11    'recipients': [
12        'manager@company.com',
13        'team@company.com',
14        'stakeholder@company.com'
15    ]
16}
17
18# Report configuration
19REPORT_CONFIG = {
20    'report_title': 'Weekly Sales Performance Report',
21    'data_source': './data/sales_data.csv',
22    'output_folder': './reports',
23    'chart_folder': './charts'
24}
25
26# Metrics to track
27METRICS = {
28    'revenue': 'Total Revenue',
29    'units_sold': 'Units Sold',
30    'avg_order_value': 'Average Order Value',
31    'conversion_rate': 'Conversion Rate'
32}
33
34# Get current week info
35def get_week_info():
36    today = datetime.now()
37    week_number = today.isocalendar()[1]
38    year = today.year
39    return f"Week {week_number}, {year}"

Security note: Never hardcode passwords. Use environment variables:

bash

1# On Mac/Linux
2export EMAIL_PASSWORD="your-app-specific-password"
3
4# On Windows
5set EMAIL_PASSWORD=your-app-specific-password

For Gmail, create an App Password instead of using your regular password.

Step 3: Build Data Processing Module

Create report_generator.py:

python

1# report_generator.py
2import pandas as pd
3import matplotlib.pyplot as plt
4from datetime import datetime, timedelta
5import os
6
7class WeeklyReportGenerator:
8    def __init__(self, data_source, output_folder='./reports', chart_folder='./charts'):
9        self.data_source = data_source
10        self.output_folder = output_folder
11        self.chart_folder = chart_folder
12        self.df = None
13        self.metrics = {}
14        
15    def load_data(self):
16        """Load data from CSV, Excel, or database"""
17        try:
18            if self.data_source.endswith('.csv'):
19                self.df = pd.read_csv(self.data_source)
20            elif self.data_source.endswith('.xlsx'):
21                self.df = pd.read_excel(self.data_source)
22            else:
23                raise ValueError("Unsupported file format")
24            
25            # Convert date column to datetime
26            self.df['date'] = pd.to_datetime(self.df['date'])
27            print(f"✓ Loaded {len(self.df)} records")
28            return True
29        except Exception as e:
30            print(f"✗ Error loading data: {e}")
31            return False
32    
33    def filter_last_week(self):
34        """Filter data for the last 7 days"""
35        today = datetime.now()
36        week_ago = today - timedelta(days=7)
37        
38        self.df = self.df[self.df['date'] >= week_ago]
39        print(f"✓ Filtered to last 7 days: {len(self.df)} records")
40        
41    def calculate_metrics(self):
42        """Calculate key performance metrics"""
43        # Current week metrics
44        current_revenue = self.df['revenue'].sum()
45        current_units = self.df['units_sold'].sum()
46        current_orders = len(self.df)
47        
48        # Calculate averages
49        avg_order_value = current_revenue / current_orders if current_orders > 0 else 0
50        
51        # Store metrics
52        self.metrics = {
53            'revenue': current_revenue,
54            'units_sold': current_units,
55            'total_orders': current_orders,
56            'avg_order_value': avg_order_value
57        }
58        
59        # Calculate week-over-week growth (if previous week data exists)
60        self.calculate_growth()
61        
62        print("✓ Calculated metrics:")
63        for key, value in self.metrics.items():
64            print(f"  {key}: {value}")
65        
66        return self.metrics
67    
68    def calculate_growth(self):
69        """Calculate week-over-week growth rates"""
70        # This is simplified - in reality, you'd compare to previous week's data
71        # For demo purposes, we'll generate random growth rates
72        import random
73        
74        self.metrics['revenue_growth'] = random.uniform(-10, 15)
75        self.metrics['units_growth'] = random.uniform(-5, 20)
76        self.metrics['aov_growth'] = random.uniform(-8, 12)
77    
78    def generate_charts(self):
79        """Create visualization charts"""
80        chart_paths = {}
81        
82        # Chart 1: Daily revenue trend
83        chart_paths['revenue_trend'] = self._create_revenue_trend_chart()
84        
85        # Chart 2: Product category breakdown
86        chart_paths['category_breakdown'] = self._create_category_chart()
87        
88        # Chart 3: Day of week performance
89        chart_paths['day_performance'] = self._create_day_of_week_chart()
90        
91        print("✓ Generated all charts")
92        return chart_paths
93    
94    def _create_revenue_trend_chart(self):
95        """Create daily revenue trend line chart"""
96        daily_revenue = self.df.groupby(self.df['date'].dt.date)['revenue'].sum()
97        
98        plt.figure(figsize=(10, 5))
99        plt.plot(daily_revenue.index, daily_revenue.values, marker='o', linewidth=2, markersize=8)
100        plt.title('Daily Revenue Trend - Last 7 Days', fontsize=14, fontweight='bold')
101        plt.xlabel('Date', fontsize=12)
102        plt.ylabel('Revenue ($)', fontsize=12)
103        plt.grid(True, alpha=0.3)
104        plt.xticks(rotation=45)
105        plt.tight_layout()
106        
107        chart_path = os.path.join(self.chart_folder, 'revenue_trend.png')
108        plt.savefig(chart_path, dpi=150, bbox_inches='tight')
109        plt.close()
110        
111        return chart_path
112    
113    def _create_category_chart(self):
114        """Create product category breakdown pie chart"""
115        if 'category' not in self.df.columns:
116            return None
117        
118        category_revenue = self.df.groupby('category')['revenue'].sum().sort_values(ascending=False)
119        
120        plt.figure(figsize=(8, 8))
121        colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#FFA07A', '#98D8C8']
122        plt.pie(category_revenue.values, labels=category_revenue.index, autopct='%1.1f%%',
123                startangle=90, colors=colors[:len(category_revenue)])
124        plt.title('Revenue by Product Category', fontsize=14, fontweight='bold')
125        plt.axis('equal')
126        
127        chart_path = os.path.join(self.chart_folder, 'category_breakdown.png')
128        plt.savefig(chart_path, dpi=150, bbox_inches='tight')
129        plt.close()
130        
131        return chart_path
132    
133    def _create_day_of_week_chart(self):
134        """Create day of week performance bar chart"""
135        self.df['day_of_week'] = self.df['date'].dt.day_name()
136        day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
137        
138        day_revenue = self.df.groupby('day_of_week')['revenue'].sum().reindex(day_order, fill_value=0)
139        
140        plt.figure(figsize=(10, 6))
141        bars = plt.bar(day_revenue.index, day_revenue.values, color='#4ECDC4', edgecolor='black', linewidth=1.2)
142        plt.title('Revenue by Day of Week', fontsize=14, fontweight='bold')
143        plt.xlabel('Day', fontsize=12)
144        plt.ylabel('Revenue ($)', fontsize=12)
145        plt.xticks(rotation=45)
146        plt.grid(axis='y', alpha=0.3)
147        
148        # Add value labels on bars
149        for bar in bars:
150            height = bar.get_height()
151            plt.text(bar.get_x() + bar.get_width()/2., height,
152                    f'${height:,.0f}',
153                    ha='center', va='bottom', fontsize=10)
154        
155        plt.tight_layout()
156        
157        chart_path = os.path.join(self.chart_folder, 'day_performance.png')
158        plt.savefig(chart_path, dpi=150, bbox_inches='tight')
159        plt.close()
160        
161        return chart_path
162    
163    def generate_report(self):
164        """Main method to generate complete report"""
165        print("\n=== Starting Weekly Report Generation ===\n")
166        
167        # Load and process data
168        if not self.load_data():
169            return None
170        
171        self.filter_last_week()
172        metrics = self.calculate_metrics()
173        chart_paths = self.generate_charts()
174        
175        print("\n=== Report Generation Complete ===\n")
176        
177        return {
178            'metrics': metrics,
179            'charts': chart_paths,
180            'generated_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
181        }

Step 4: Build Email Sender Module

Create email_sender.py:

python

1# email_sender.py
2import smtplib
3from email.mime.multipart import MIMEMultipart
4from email.mime.text import MIMEText
5from email.mime.image import MIMEImage
6from datetime import datetime
7import os
8
9class EmailReportSender:
10    def __init__(self, config):
11        self.smtp_server = config['smtp_server']
12        self.smtp_port = config['smtp_port']
13        self.sender_email = config['sender_email']
14        self.sender_password = config['sender_password']
15        self.recipients = config['recipients']
16        
17    def create_html_email(self, report_data, week_info):
18        """Generate professional HTML email with embedded charts"""
19        metrics = report_data['metrics']
20        
21        # Format currency and percentages
22        def format_currency(value):
23            return f"${value:,.2f}"
24        
25        def format_percent(value):
26            sign = "+" if value >= 0 else ""
27            color = "green" if value >= 0 else "red"
28            return f'<span style="color: {color};">{sign}{value:.1f}%</span>'
29        
30        # Build HTML email
31        html = f"""
32        <html>
33        <head>
34            <style>
35                body {{
36                    font-family: Arial, sans-serif;
37                    line-height: 1.6;
38                    color: #333;
39                    max-width: 800px;
40                    margin: 0 auto;
41                    padding: 20px;
42                }}
43                .header {{
44                    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
45                    color: white;
46                    padding: 30px;
47                    border-radius: 10px;
48                    margin-bottom: 30px;
49                }}
50                .header h1 {{
51                    margin: 0;
52                    font-size: 28px;
53                }}
54                .header p {{
55                    margin: 10px 0 0 0;
56                    opacity: 0.9;
57                }}
58                .metrics-grid {{
59                    display: grid;
60                    grid-template-columns: repeat(2, 1fr);
61                    gap: 20px;
62                    margin-bottom: 30px;
63                }}
64                .metric-card {{
65                    background: #f8f9fa;
66                    border-left: 4px solid #667eea;
67                    padding: 20px;
68                    border-radius: 5px;
69                }}
70                .metric-card h3 {{
71                    margin: 0 0 10px 0;
72                    color: #667eea;
73                    font-size: 14px;
74                    text-transform: uppercase;
75                }}
76                .metric-value {{
77                    font-size: 32px;
78                    font-weight: bold;
79                    color: #333;
80                    margin: 10px 0;
81                }}
82                .metric-growth {{
83                    font-size: 14px;
84                }}
85                .chart-section {{
86                    margin: 30px 0;
87                }}
88                .chart-section h2 {{
89                    color: #667eea;
90                    border-bottom: 2px solid #667eea;
91                    padding-bottom: 10px;
92                    margin-bottom: 20px;
93                }}
94                .chart-section img {{
95                    max-width: 100%;
96                    height: auto;
97                    border-radius: 5px;
98                    box-shadow: 0 2px 8px rgba(0,0,0,0.1);
99                }}
100                .footer {{
101                    margin-top: 40px;
102                    padding-top: 20px;
103                    border-top: 1px solid #ddd;
104                    text-align: center;
105                    color: #666;
106                    font-size: 12px;
107                }}
108            </style>
109        </head>
110        <body>
111            <div class="header">
112                <h1>📊 Weekly Sales Performance Report</h1>
113                <p>{week_info} | Generated {report_data['generated_at']}</p>
114            </div>
115            
116            <div class="metrics-grid">
117                <div class="metric-card">
118                    <h3>Total Revenue</h3>
119                    <div class="metric-value">{format_currency(metrics['revenue'])}</div>
120                    <div class="metric-growth">vs last week: {format_percent(metrics.get('revenue_growth', 0))}</div>
121                </div>
122                
123                <div class="metric-card">
124                    <h3>Units Sold</h3>
125                    <div class="metric-value">{metrics['units_sold']:,}</div>
126                    <div class="metric-growth">vs last week: {format_percent(metrics.get('units_growth', 0))}</div>
127                </div>
128                
129                <div class="metric-card">
130                    <h3>Total Orders</h3>
131                    <div class="metric-value">{metrics['total_orders']:,}</div>
132                    <div class="metric-growth">Average: {metrics['total_orders']/7:.1f} per day</div>
133                </div>
134                
135                <div class="metric-card">
136                    <h3>Avg Order Value</h3>
137                    <div class="metric-value">{format_currency(metrics['avg_order_value'])}</div>
138                    <div class="metric-growth">vs last week: {format_percent(metrics.get('aov_growth', 0))}</div>
139                </div>
140            </div>
141            
142            <div class="chart-section">
143                <h2>📈 Revenue Trend Analysis</h2>
144                <img src="cid:revenue_trend" alt="Revenue Trend">
145            </div>
146            
147            <div class="chart-section">
148                <h2>🎯 Category Performance</h2>
149                <img src="cid:category_breakdown" alt="Category Breakdown">
150            </div>
151            
152            <div class="chart-section">
153                <h2>📅 Day of Week Analysis</h2>
154                <img src="cid:day_performance" alt="Day Performance">
155            </div>
156            
157            <div class="footer">
158                <p>This is an automated report generated by Python. Questions? Contact data@company.com</p>
159                <p>Unsubscribe from these reports: <a href="mailto:data@company.com">Email us</a></p>
160            </div>
161        </body>
162        </html>
163        """
164        
165        return html
166    
167    def send_report(self, report_data, week_info):
168        """Send the email report with embedded charts"""
169        try:
170            # Create message
171            msg = MIMEMultipart('related')
172            msg['Subject'] = f"📊 Weekly Sales Report - {week_info}"
173            msg['From'] = self.sender_email
174            msg['To'] = ', '.join(self.recipients)
175            
176            # Attach HTML body
177            html_content = self.create_html_email(report_data, week_info)
178            msg.attach(MIMEText(html_content, 'html'))
179            
180            # Attach charts as inline images
181            for chart_name, chart_path in report_data['charts'].items():
182                if chart_path and os.path.exists(chart_path):
183                    with open(chart_path, 'rb') as img_file:
184                        img = MIMEImage(img_file.read())
185                        img.add_header('Content-ID', f'<{chart_name}>')
186                        msg.attach(img)
187            
188            # Connect and send
189            print(f"Connecting to {self.smtp_server}:{self.smtp_port}...")
190            with smtplib.SMTP(self.smtp_server, self.smtp_port) as server:
191                server.starttls()
192                server.login(self.sender_email, self.sender_password)
193                server.send_message(msg)
194            
195            print(f"✓ Report sent successfully to {len(self.recipients)} recipients")
196            return True
197            
198        except Exception as e:
199            print(f"✗ Error sending email: {e}")
200            return False

Step 5: Create Main Orchestration Script

Create main.py:

python

1# main.py
2import os
3from report_generator import WeeklyReportGenerator
4from email_sender import EmailReportSender
5from config import EMAIL_CONFIG, REPORT_CONFIG, get_week_info
6
7def create_folders():
8    """Ensure output folders exist"""
9    os.makedirs(REPORT_CONFIG['output_folder'], exist_ok=True)
10    os.makedirs(REPORT_CONFIG['chart_folder'], exist_ok=True)
11
12def generate_and_send_report():
13    """Main function to generate and send weekly report"""
14    print("\n" + "="*60)
15    print("WEEKLY REPORT AUTOMATION - STARTING")
16    print("="*60 + "\n")
17    
18    # Create necessary folders
19    create_folders()
20    
21    # Generate report
22    generator = WeeklyReportGenerator(
23        data_source=REPORT_CONFIG['data_source'],
24        output_folder=REPORT_CONFIG['output_folder'],
25        chart_folder=REPORT_CONFIG['chart_folder']
26    )
27    
28    report_data = generator.generate_report()
29    
30    if not report_data:
31        print("✗ Failed to generate report")
32        return False
33    
34    # Send email
35    week_info = get_week_info()
36    email_sender = EmailReportSender(EMAIL_CONFIG)
37    
38    success = email_sender.send_report(report_data, week_info)
39    
40    if success:
41        print("\n" + "="*60)
42        print("✓ WEEKLY REPORT SENT SUCCESSFULLY")
43        print("="*60 + "\n")
44    
45    return success
46
47if __name__ == "__main__":
48    generate_and_send_report()

Step 6: Create Sample Data

For testing, create a sample CSV file:

python

1# generate_sample_data.py
2import pandas as pd
3import random
4from datetime import datetime, timedelta
5
6# Generate sample sales data
7dates = []
8revenues = []
9units = []
10categories = []
11
12categories_list = ['Electronics', 'Clothing', 'Home & Garden', 'Sports', 'Books']
13
14for i in range(50):
15    date = datetime.now() - timedelta(days=random.randint(0, 7))
16    dates.append(date)
17    revenues.append(round(random.uniform(50, 500), 2))
18    units.append(random.randint(1, 10))
19    categories.append(random.choice(categories_list))
20
21df = pd.DataFrame({
22    'date': dates,
23    'revenue': revenues,
24    'units_sold': units,
25    'category': categories
26})
27
28df.to_csv('./data/sales_data.csv', index=False)
29print("✓ Sample data generated: ./data/sales_data.csv")

Run this once:

bash

1python generate_sample_data.py

Step 7: Test Your Report

Run the complete workflow:

bash

1python main.py

You should see output like:

Prompt

============================================================
WEEKLY REPORT AUTOMATION - STARTING
============================================================

=== Starting Weekly Report Generation ===

✓ Loaded 50 records
✓ Filtered to last 7 days: 47 records
✓ Calculated metrics:
  revenue: 12458.32
  units_sold: 284
  total_orders: 47
  avg_order_value: 265.07
✓ Generated all charts

=== Report Generation Complete ===

Connecting to smtp.gmail.com:587...
✓ Report sent successfully to 3 recipients

============================================================
✓ WEEKLY REPORT SENT SUCCESSFULLY
============================================================

Step 8: Automate with Scheduling

Now make it run automatically every Friday at 3 PM.

Option 1: Using Python schedule library (simple, but requires script to run continuously)

Create scheduler.py:

python

1# scheduler.py
2import schedule
3import time
4from main import generate_and_send_report
5
6# Schedule job for every Friday at 3 PM
7schedule.every().friday.at("15:00").do(generate_and_send_report)
8
9print("📅 Report scheduler started")
10print("Next run: Every Friday at 3:00 PM")
11print("Press Ctrl+C to stop\n")
12
13while True:
14    schedule.run_pending()
15    time.sleep(60)  # Check every minute

Run it:

bash

1python scheduler.py

Option 2: Using Cron (Mac/Linux) (better for production)

bash

1# Edit crontab
2crontab -e
3
4# Add this line (runs every Friday at 3 PM)
50 15 * * 5 cd /path/to/weekly-report-automation && /usr/bin/python3 main.py

Option 3: Using Windows Task Scheduler (Windows)

Open Task Scheduler
Create Basic Task
Trigger: Weekly, Friday, 3:00 PM
Action: Start Program → Python path → main.py

Advanced Enhancements

Enhancement 1: Pull Data from Database

Replace CSV loading with database queries:

python

1# In report_generator.py
2import psycopg2  # for PostgreSQL
3import pymysql   # for MySQL
4
5def load_data_from_database(self):
6    """Load data from PostgreSQL database"""
7    conn = psycopg2.connect(
8        host="your-db-host",
9        database="sales_db",
10        user="your-username",
11        password=os.getenv('DB_PASSWORD')
12    )
13    
14    query = """
15        SELECT 
16            order_date as date,
17            SUM(total_amount) as revenue,
18            SUM(quantity) as units_sold,
19            product_category as category
20        FROM orders
21        WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
22        GROUP BY order_date, product_category
23    """
24    
25    self.df = pd.read_sql(query, conn)
26    conn.close()

Enhancement 2: Send to Slack Instead of Email

python

1# slack_sender.py
2import requests
3
4def send_to_slack(webhook_url, report_data, week_info):
5    """Send report summary to Slack"""
6    metrics = report_data['metrics']
7    
8    message = {
9        "blocks": [
10            {
11                "type": "header",
12                "text": {
13                    "type": "plain_text",
14                    "text": f"📊 Weekly Report - {week_info}"
15                }
16            },
17            {
18                "type": "section",
19                "fields": [
20                    {"type": "mrkdwn", "text": f"*Revenue:*\n${metrics['revenue']:,.2f}"},
21                    {"type": "mrkdwn", "text": f"*Units Sold:*\n{metrics['units_sold']:,}"},
22                    {"type": "mrkdwn", "text": f"*Orders:*\n{metrics['total_orders']:,}"},
23                    {"type": "mrkdwn", "text": f"*Avg Order:*\n${metrics['avg_order_value']:.2f}"}
24                ]
25            }
26        ]
27    }
28    
29    response = requests.post(webhook_url, json=message)
30    return response.status_code == 200

Enhancement 3: Add Error Notifications

python

1# In main.py, wrap in try-except
2def generate_and_send_report():
3    try:
4        # ... existing code ...
5        return success
6    except Exception as e:
7        # Send error notification
8        send_error_alert(f"Report generation failed: {str(e)}")
9        return False
10
11def send_error_alert(error_message):
12    """Send error notification via email"""
13    import smtplib
14    from email.mime.text import MIMEText
15    
16    msg = MIMEText(f"Weekly report automation failed:\n\n{error_message}")
17    msg['Subject'] = "🚨 Weekly Report Generation Failed"
18    msg['From'] = EMAIL_CONFIG['sender_email']
19    msg['To'] = "admin@company.com"
20    
21    # Send notification...

Troubleshooting Common Issues

Issue 1: SMTP Authentication Failed

For Gmail: Enable "Less secure app access" or use App Password
For Office 365: Use port 587 with STARTTLS
Check firewall isn't blocking SMTP ports

Issue 2: Charts Not Displaying in Email

Ensure Content-ID matches the image src (e.g., cid:revenue_trend)
Use MIMEMultipart('related') not MIMEMultipart('alternative')
Check image files exist before attaching

Issue 3: Data Processing Errors

Add data validation: Check for missing dates, negative values
Handle edge cases: What if no data for the week?
Add logging to identify where failures occur

Issue 4: Scheduler Not Running

For schedule library: Process must stay running
For cron: Check cron logs: grep CRON /var/log/syslog
Ensure Python path is absolute in cron jobs

Real-World ROI

Here's the actual time savings from automating weekly reports:

Before automation (per week):

Manual data collection: 30 min
Analysis and calculations: 25 min
Chart creation: 20 min
Email formatting: 15 min
Distribution and follow-up: 10 min
Total: 100 minutes per week

After automation (per week):

Review automated report: 5 min
Address questions: 10 min
Total: 15 minutes per week

Time saved: 85 minutes per week = 74 hours per year

At $75/hour average salary, that's $5,550 in reclaimed time annually.

Frequently Asked Questions

Can I use this with Google Workspace (Gmail for business)? Yes, but you need to create an App Password in your Google Account settings. Regular passwords won't work with SMTP for security reasons.

How do I handle multiple reports with different templates? Create multiple config dictionaries in config.py and separate generator classes. Or use a template engine like Jinja2 to dynamically generate HTML from templates.

Can I send PDF attachments instead of HTML emails? Yes, use matplotlib.backends.backend_pdf.PdfPages to save charts to PDF, then attach using MIMEApplication. PDF attachments work better for external stakeholders who might have email clients that don't render HTML well.

What if my data source is an API, not a database? Replace the load_data() method with API calls using the requests library. Parse JSON responses into pandas DataFrames.

How do I test this without spamming everyone? Temporarily change config.py recipients to only include your own email address during testing. Or create a separate config_test.py for development.

Automate Weekly Email Reports with Python: Save 5 Hours Every Week

What You'll Build

By the end of this tutorial, you'll have a Python script that:

Pulls data from CSV files, databases, or APIs automatically
Calculates key metrics and trends
Generates professional charts and visualizations
Formats everything into a clean HTML email
Sends the report to your distribution list
Runs automatically every Friday at 3 PM

Time investment: 2 hours to build Time saved: 2+ hours every single week (100+ hours per year)

Prerequisites

You'll need:

Python 3.8 or higher installed
Basic Python knowledge (variables, functions, loops)
Access to your company's email server or Gmail account
Sample data to work with (I'll provide examples)

Required libraries:

bash

1pip install pandas matplotlib smtplib email
2pip install schedule  # for automation
3pip install openpyxl  # if reading Excel files

The Problem: Manual Reporting is a Time Drain

Let's break down where time goes in manual reporting:

Data collection (30 minutes): Log into multiple systems, export CSVs, copy data
Data cleaning (20 minutes): Fix formatting issues, handle missing values, merge sources
Analysis (25 minutes): Calculate metrics, create pivot tables, identify trends
Visualization (20 minutes): Build charts, format them consistently
Formatting (15 minutes): Paste into email template, add commentary
Distribution (10 minutes): Send to stakeholders, handle bounces

Total: 2 hours — and that's if nothing goes wrong.

Solution Architecture

Here's our automated approach:

Prompt

┌─────────────────┐
│  Data Sources   │ (CSV, Database, API)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Data Processing │ (pandas)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Visualization  │ (matplotlib)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ HTML Formatting │ (email.mime)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Email Sending  │ (smtplib)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Automation     │ (schedule/cron)
└─────────────────┘

Let's build this step by step.

Step 1: Set Up Project Structure

Create a clean project structure:

bash

1mkdir weekly-report-automation
2cd weekly-report-automation
3
4# Create folder structure
5mkdir data
6mkdir reports
7mkdir charts
8
9# Create Python files
10touch report_generator.py
11touch email_sender.py
12touch config.py
13touch main.py

File purposes:

report_generator.py: Data processing and analysis logic
email_sender.py: Email formatting and sending
config.py: Configuration settings (email, data sources)
main.py: Orchestrates the entire workflow

Step 2: Configure Email Settings

Create config.py:

python

1# config.py
2import os
3from datetime import datetime
4
5# Email configuration
6EMAIL_CONFIG = {
7    'smtp_server': 'smtp.gmail.com',  # or your company's SMTP server
8    'smtp_port': 587,
9    'sender_email': 'your-email@company.com',
10    'sender_password': os.getenv('EMAIL_PASSWORD'),  # Store securely!
11    'recipients': [
12        'manager@company.com',
13        'team@company.com',
14        'stakeholder@company.com'
15    ]
16}
17
18# Report configuration
19REPORT_CONFIG = {
20    'report_title': 'Weekly Sales Performance Report',
21    'data_source': './data/sales_data.csv',
22    'output_folder': './reports',
23    'chart_folder': './charts'
24}
25
26# Metrics to track
27METRICS = {
28    'revenue': 'Total Revenue',
29    'units_sold': 'Units Sold',
30    'avg_order_value': 'Average Order Value',
31    'conversion_rate': 'Conversion Rate'
32}
33
34# Get current week info
35def get_week_info():
36    today = datetime.now()
37    week_number = today.isocalendar()[1]
38    year = today.year
39    return f"Week {week_number}, {year}"

Security note: Never hardcode passwords. Use environment variables:

bash

1# On Mac/Linux
2export EMAIL_PASSWORD="your-app-specific-password"
3
4# On Windows
5set EMAIL_PASSWORD=your-app-specific-password

For Gmail, create an App Password instead of using your regular password.

Step 3: Build Data Processing Module

Create report_generator.py:

python

1# report_generator.py
2import pandas as pd
3import matplotlib.pyplot as plt
4from datetime import datetime, timedelta
5import os
6
7class WeeklyReportGenerator:
8    def __init__(self, data_source, output_folder='./reports', chart_folder='./charts'):
9        self.data_source = data_source
10        self.output_folder = output_folder
11        self.chart_folder = chart_folder
12        self.df = None
13        self.metrics = {}
14        
15    def load_data(self):
16        """Load data from CSV, Excel, or database"""
17        try:
18            if self.data_source.endswith('.csv'):
19                self.df = pd.read_csv(self.data_source)
20            elif self.data_source.endswith('.xlsx'):
21                self.df = pd.read_excel(self.data_source)
22            else:
23                raise ValueError("Unsupported file format")
24            
25            # Convert date column to datetime
26            self.df['date'] = pd.to_datetime(self.df['date'])
27            print(f"✓ Loaded {len(self.df)} records")
28            return True
29        except Exception as e:
30            print(f"✗ Error loading data: {e}")
31            return False
32    
33    def filter_last_week(self):
34        """Filter data for the last 7 days"""
35        today = datetime.now()
36        week_ago = today - timedelta(days=7)
37        
38        self.df = self.df[self.df['date'] >= week_ago]
39        print(f"✓ Filtered to last 7 days: {len(self.df)} records")
40        
41    def calculate_metrics(self):
42        """Calculate key performance metrics"""
43        # Current week metrics
44        current_revenue = self.df['revenue'].sum()
45        current_units = self.df['units_sold'].sum()
46        current_orders = len(self.df)
47        
48        # Calculate averages
49        avg_order_value = current_revenue / current_orders if current_orders > 0 else 0
50        
51        # Store metrics
52        self.metrics = {
53            'revenue': current_revenue,
54            'units_sold': current_units,
55            'total_orders': current_orders,
56            'avg_order_value': avg_order_value
57        }
58        
59        # Calculate week-over-week growth (if previous week data exists)
60        self.calculate_growth()
61        
62        print("✓ Calculated metrics:")
63        for key, value in self.metrics.items():
64            print(f"  {key}: {value}")
65        
66        return self.metrics
67    
68    def calculate_growth(self):
69        """Calculate week-over-week growth rates"""
70        # This is simplified - in reality, you'd compare to previous week's data
71        # For demo purposes, we'll generate random growth rates
72        import random
73        
74        self.metrics['revenue_growth'] = random.uniform(-10, 15)
75        self.metrics['units_growth'] = random.uniform(-5, 20)
76        self.metrics['aov_growth'] = random.uniform(-8, 12)
77    
78    def generate_charts(self):
79        """Create visualization charts"""
80        chart_paths = {}
81        
82        # Chart 1: Daily revenue trend
83        chart_paths['revenue_trend'] = self._create_revenue_trend_chart()
84        
85        # Chart 2: Product category breakdown
86        chart_paths['category_breakdown'] = self._create_category_chart()
87        
88        # Chart 3: Day of week performance
89        chart_paths['day_performance'] = self._create_day_of_week_chart()
90        
91        print("✓ Generated all charts")
92        return chart_paths
93    
94    def _create_revenue_trend_chart(self):
95        """Create daily revenue trend line chart"""
96        daily_revenue = self.df.groupby(self.df['date'].dt.date)['revenue'].sum()
97        
98        plt.figure(figsize=(10, 5))
99        plt.plot(daily_revenue.index, daily_revenue.values, marker='o', linewidth=2, markersize=8)
100        plt.title('Daily Revenue Trend - Last 7 Days', fontsize=14, fontweight='bold')
101        plt.xlabel('Date', fontsize=12)
102        plt.ylabel('Revenue ($)', fontsize=12)
103        plt.grid(True, alpha=0.3)
104        plt.xticks(rotation=45)
105        plt.tight_layout()
106        
107        chart_path = os.path.join(self.chart_folder, 'revenue_trend.png')
108        plt.savefig(chart_path, dpi=150, bbox_inches='tight')
109        plt.close()
110        
111        return chart_path
112    
113    def _create_category_chart(self):
114        """Create product category breakdown pie chart"""
115        if 'category' not in self.df.columns:
116            return None
117        
118        category_revenue = self.df.groupby('category')['revenue'].sum().sort_values(ascending=False)
119        
120        plt.figure(figsize=(8, 8))
121        colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#FFA07A', '#98D8C8']
122        plt.pie(category_revenue.values, labels=category_revenue.index, autopct='%1.1f%%',
123                startangle=90, colors=colors[:len(category_revenue)])
124        plt.title('Revenue by Product Category', fontsize=14, fontweight='bold')
125        plt.axis('equal')
126        
127        chart_path = os.path.join(self.chart_folder, 'category_breakdown.png')
128        plt.savefig(chart_path, dpi=150, bbox_inches='tight')
129        plt.close()
130        
131        return chart_path
132    
133    def _create_day_of_week_chart(self):
134        """Create day of week performance bar chart"""
135        self.df['day_of_week'] = self.df['date'].dt.day_name()
136        day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
137        
138        day_revenue = self.df.groupby('day_of_week')['revenue'].sum().reindex(day_order, fill_value=0)
139        
140        plt.figure(figsize=(10, 6))
141        bars = plt.bar(day_revenue.index, day_revenue.values, color='#4ECDC4', edgecolor='black', linewidth=1.2)
142        plt.title('Revenue by Day of Week', fontsize=14, fontweight='bold')
143        plt.xlabel('Day', fontsize=12)
144        plt.ylabel('Revenue ($)', fontsize=12)
145        plt.xticks(rotation=45)
146        plt.grid(axis='y', alpha=0.3)
147        
148        # Add value labels on bars
149        for bar in bars:
150            height = bar.get_height()
151            plt.text(bar.get_x() + bar.get_width()/2., height,
152                    f'${height:,.0f}',
153                    ha='center', va='bottom', fontsize=10)
154        
155        plt.tight_layout()
156        
157        chart_path = os.path.join(self.chart_folder, 'day_performance.png')
158        plt.savefig(chart_path, dpi=150, bbox_inches='tight')
159        plt.close()
160        
161        return chart_path
162    
163    def generate_report(self):
164        """Main method to generate complete report"""
165        print("\n=== Starting Weekly Report Generation ===\n")
166        
167        # Load and process data
168        if not self.load_data():
169            return None
170        
171        self.filter_last_week()
172        metrics = self.calculate_metrics()
173        chart_paths = self.generate_charts()
174        
175        print("\n=== Report Generation Complete ===\n")
176        
177        return {
178            'metrics': metrics,
179            'charts': chart_paths,
180            'generated_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
181        }

Step 4: Build Email Sender Module

Create email_sender.py:

python

1# email_sender.py
2import smtplib
3from email.mime.multipart import MIMEMultipart
4from email.mime.text import MIMEText
5from email.mime.image import MIMEImage
6from datetime import datetime
7import os
8
9class EmailReportSender:
10    def __init__(self, config):
11        self.smtp_server = config['smtp_server']
12        self.smtp_port = config['smtp_port']
13        self.sender_email = config['sender_email']
14        self.sender_password = config['sender_password']
15        self.recipients = config['recipients']
16        
17    def create_html_email(self, report_data, week_info):
18        """Generate professional HTML email with embedded charts"""
19        metrics = report_data['metrics']
20        
21        # Format currency and percentages
22        def format_currency(value):
23            return f"${value:,.2f}"
24        
25        def format_percent(value):
26            sign = "+" if value >= 0 else ""
27            color = "green" if value >= 0 else "red"
28            return f'<span style="color: {color};">{sign}{value:.1f}%</span>'
29        
30        # Build HTML email
31        html = f"""
32        <html>
33        <head>
34            <style>
35                body {{
36                    font-family: Arial, sans-serif;
37                    line-height: 1.6;
38                    color: #333;
39                    max-width: 800px;
40                    margin: 0 auto;
41                    padding: 20px;
42                }}
43                .header {{
44                    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
45                    color: white;
46                    padding: 30px;
47                    border-radius: 10px;
48                    margin-bottom: 30px;
49                }}
50                .header h1 {{
51                    margin: 0;
52                    font-size: 28px;
53                }}
54                .header p {{
55                    margin: 10px 0 0 0;
56                    opacity: 0.9;
57                }}
58                .metrics-grid {{
59                    display: grid;
60                    grid-template-columns: repeat(2, 1fr);
61                    gap: 20px;
62                    margin-bottom: 30px;
63                }}
64                .metric-card {{
65                    background: #f8f9fa;
66                    border-left: 4px solid #667eea;
67                    padding: 20px;
68                    border-radius: 5px;
69                }}
70                .metric-card h3 {{
71                    margin: 0 0 10px 0;
72                    color: #667eea;
73                    font-size: 14px;
74                    text-transform: uppercase;
75                }}
76                .metric-value {{
77                    font-size: 32px;
78                    font-weight: bold;
79                    color: #333;
80                    margin: 10px 0;
81                }}
82                .metric-growth {{
83                    font-size: 14px;
84                }}
85                .chart-section {{
86                    margin: 30px 0;
87                }}
88                .chart-section h2 {{
89                    color: #667eea;
90                    border-bottom: 2px solid #667eea;
91                    padding-bottom: 10px;
92                    margin-bottom: 20px;
93                }}
94                .chart-section img {{
95                    max-width: 100%;
96                    height: auto;
97                    border-radius: 5px;
98                    box-shadow: 0 2px 8px rgba(0,0,0,0.1);
99                }}
100                .footer {{
101                    margin-top: 40px;
102                    padding-top: 20px;
103                    border-top: 1px solid #ddd;
104                    text-align: center;
105                    color: #666;
106                    font-size: 12px;
107                }}
108            </style>
109        </head>
110        <body>
111            <div class="header">
112                <h1>📊 Weekly Sales Performance Report</h1>
113                <p>{week_info} | Generated {report_data['generated_at']}</p>
114            </div>
115            
116            <div class="metrics-grid">
117                <div class="metric-card">
118                    <h3>Total Revenue</h3>
119                    <div class="metric-value">{format_currency(metrics['revenue'])}</div>
120                    <div class="metric-growth">vs last week: {format_percent(metrics.get('revenue_growth', 0))}</div>
121                </div>
122                
123                <div class="metric-card">
124                    <h3>Units Sold</h3>
125                    <div class="metric-value">{metrics['units_sold']:,}</div>
126                    <div class="metric-growth">vs last week: {format_percent(metrics.get('units_growth', 0))}</div>
127                </div>
128                
129                <div class="metric-card">
130                    <h3>Total Orders</h3>
131                    <div class="metric-value">{metrics['total_orders']:,}</div>
132                    <div class="metric-growth">Average: {metrics['total_orders']/7:.1f} per day</div>
133                </div>
134                
135                <div class="metric-card">
136                    <h3>Avg Order Value</h3>
137                    <div class="metric-value">{format_currency(metrics['avg_order_value'])}</div>
138                    <div class="metric-growth">vs last week: {format_percent(metrics.get('aov_growth', 0))}</div>
139                </div>
140            </div>
141            
142            <div class="chart-section">
143                <h2>📈 Revenue Trend Analysis</h2>
144                <img src="cid:revenue_trend" alt="Revenue Trend">
145            </div>
146            
147            <div class="chart-section">
148                <h2>🎯 Category Performance</h2>
149                <img src="cid:category_breakdown" alt="Category Breakdown">
150            </div>
151            
152            <div class="chart-section">
153                <h2>📅 Day of Week Analysis</h2>
154                <img src="cid:day_performance" alt="Day Performance">
155            </div>
156            
157            <div class="footer">
158                <p>This is an automated report generated by Python. Questions? Contact data@company.com</p>
159                <p>Unsubscribe from these reports: <a href="mailto:data@company.com">Email us</a></p>
160            </div>
161        </body>
162        </html>
163        """
164        
165        return html
166    
167    def send_report(self, report_data, week_info):
168        """Send the email report with embedded charts"""
169        try:
170            # Create message
171            msg = MIMEMultipart('related')
172            msg['Subject'] = f"📊 Weekly Sales Report - {week_info}"
173            msg['From'] = self.sender_email
174            msg['To'] = ', '.join(self.recipients)
175            
176            # Attach HTML body
177            html_content = self.create_html_email(report_data, week_info)
178            msg.attach(MIMEText(html_content, 'html'))
179            
180            # Attach charts as inline images
181            for chart_name, chart_path in report_data['charts'].items():
182                if chart_path and os.path.exists(chart_path):
183                    with open(chart_path, 'rb') as img_file:
184                        img = MIMEImage(img_file.read())
185                        img.add_header('Content-ID', f'<{chart_name}>')
186                        msg.attach(img)
187            
188            # Connect and send
189            print(f"Connecting to {self.smtp_server}:{self.smtp_port}...")
190            with smtplib.SMTP(self.smtp_server, self.smtp_port) as server:
191                server.starttls()
192                server.login(self.sender_email, self.sender_password)
193                server.send_message(msg)
194            
195            print(f"✓ Report sent successfully to {len(self.recipients)} recipients")
196            return True
197            
198        except Exception as e:
199            print(f"✗ Error sending email: {e}")
200            return False

Step 5: Create Main Orchestration Script

Create main.py:

python

1# main.py
2import os
3from report_generator import WeeklyReportGenerator
4from email_sender import EmailReportSender
5from config import EMAIL_CONFIG, REPORT_CONFIG, get_week_info
6
7def create_folders():
8    """Ensure output folders exist"""
9    os.makedirs(REPORT_CONFIG['output_folder'], exist_ok=True)
10    os.makedirs(REPORT_CONFIG['chart_folder'], exist_ok=True)
11
12def generate_and_send_report():
13    """Main function to generate and send weekly report"""
14    print("\n" + "="*60)
15    print("WEEKLY REPORT AUTOMATION - STARTING")
16    print("="*60 + "\n")
17    
18    # Create necessary folders
19    create_folders()
20    
21    # Generate report
22    generator = WeeklyReportGenerator(
23        data_source=REPORT_CONFIG['data_source'],
24        output_folder=REPORT_CONFIG['output_folder'],
25        chart_folder=REPORT_CONFIG['chart_folder']
26    )
27    
28    report_data = generator.generate_report()
29    
30    if not report_data:
31        print("✗ Failed to generate report")
32        return False
33    
34    # Send email
35    week_info = get_week_info()
36    email_sender = EmailReportSender(EMAIL_CONFIG)
37    
38    success = email_sender.send_report(report_data, week_info)
39    
40    if success:
41        print("\n" + "="*60)
42        print("✓ WEEKLY REPORT SENT SUCCESSFULLY")
43        print("="*60 + "\n")
44    
45    return success
46
47if __name__ == "__main__":
48    generate_and_send_report()

Step 6: Create Sample Data

For testing, create a sample CSV file:

python

1# generate_sample_data.py
2import pandas as pd
3import random
4from datetime import datetime, timedelta
5
6# Generate sample sales data
7dates = []
8revenues = []
9units = []
10categories = []
11
12categories_list = ['Electronics', 'Clothing', 'Home & Garden', 'Sports', 'Books']
13
14for i in range(50):
15    date = datetime.now() - timedelta(days=random.randint(0, 7))
16    dates.append(date)
17    revenues.append(round(random.uniform(50, 500), 2))
18    units.append(random.randint(1, 10))
19    categories.append(random.choice(categories_list))
20
21df = pd.DataFrame({
22    'date': dates,
23    'revenue': revenues,
24    'units_sold': units,
25    'category': categories
26})
27
28df.to_csv('./data/sales_data.csv', index=False)
29print("✓ Sample data generated: ./data/sales_data.csv")

Run this once:

bash

1python generate_sample_data.py

Step 7: Test Your Report

Run the complete workflow:

bash

1python main.py

You should see output like:

Prompt

============================================================
WEEKLY REPORT AUTOMATION - STARTING
============================================================

=== Starting Weekly Report Generation ===

✓ Loaded 50 records
✓ Filtered to last 7 days: 47 records
✓ Calculated metrics:
  revenue: 12458.32
  units_sold: 284
  total_orders: 47
  avg_order_value: 265.07
✓ Generated all charts

=== Report Generation Complete ===

Connecting to smtp.gmail.com:587...
✓ Report sent successfully to 3 recipients

============================================================
✓ WEEKLY REPORT SENT SUCCESSFULLY
============================================================

Step 8: Automate with Scheduling

Now make it run automatically every Friday at 3 PM.

Option 1: Using Python schedule library (simple, but requires script to run continuously)

Create scheduler.py:

python

1# scheduler.py
2import schedule
3import time
4from main import generate_and_send_report
5
6# Schedule job for every Friday at 3 PM
7schedule.every().friday.at("15:00").do(generate_and_send_report)
8
9print("📅 Report scheduler started")
10print("Next run: Every Friday at 3:00 PM")
11print("Press Ctrl+C to stop\n")
12
13while True:
14    schedule.run_pending()
15    time.sleep(60)  # Check every minute

Run it:

bash

1python scheduler.py

Option 2: Using Cron (Mac/Linux) (better for production)

bash

1# Edit crontab
2crontab -e
3
4# Add this line (runs every Friday at 3 PM)
50 15 * * 5 cd /path/to/weekly-report-automation && /usr/bin/python3 main.py

Option 3: Using Windows Task Scheduler (Windows)

Open Task Scheduler
Create Basic Task
Trigger: Weekly, Friday, 3:00 PM
Action: Start Program → Python path → main.py

Advanced Enhancements

Enhancement 1: Pull Data from Database

Replace CSV loading with database queries:

python

1# In report_generator.py
2import psycopg2  # for PostgreSQL
3import pymysql   # for MySQL
4
5def load_data_from_database(self):
6    """Load data from PostgreSQL database"""
7    conn = psycopg2.connect(
8        host="your-db-host",
9        database="sales_db",
10        user="your-username",
11        password=os.getenv('DB_PASSWORD')
12    )
13    
14    query = """
15        SELECT 
16            order_date as date,
17            SUM(total_amount) as revenue,
18            SUM(quantity) as units_sold,
19            product_category as category
20        FROM orders
21        WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
22        GROUP BY order_date, product_category
23    """
24    
25    self.df = pd.read_sql(query, conn)
26    conn.close()

Enhancement 2: Send to Slack Instead of Email

python

1# slack_sender.py
2import requests
3
4def send_to_slack(webhook_url, report_data, week_info):
5    """Send report summary to Slack"""
6    metrics = report_data['metrics']
7    
8    message = {
9        "blocks": [
10            {
11                "type": "header",
12                "text": {
13                    "type": "plain_text",
14                    "text": f"📊 Weekly Report - {week_info}"
15                }
16            },
17            {
18                "type": "section",
19                "fields": [
20                    {"type": "mrkdwn", "text": f"*Revenue:*\n${metrics['revenue']:,.2f}"},
21                    {"type": "mrkdwn", "text": f"*Units Sold:*\n{metrics['units_sold']:,}"},
22                    {"type": "mrkdwn", "text": f"*Orders:*\n{metrics['total_orders']:,}"},
23                    {"type": "mrkdwn", "text": f"*Avg Order:*\n${metrics['avg_order_value']:.2f}"}
24                ]
25            }
26        ]
27    }
28    
29    response = requests.post(webhook_url, json=message)
30    return response.status_code == 200

Enhancement 3: Add Error Notifications

python

1# In main.py, wrap in try-except
2def generate_and_send_report():
3    try:
4        # ... existing code ...
5        return success
6    except Exception as e:
7        # Send error notification
8        send_error_alert(f"Report generation failed: {str(e)}")
9        return False
10
11def send_error_alert(error_message):
12    """Send error notification via email"""
13    import smtplib
14    from email.mime.text import MIMEText
15    
16    msg = MIMEText(f"Weekly report automation failed:\n\n{error_message}")
17    msg['Subject'] = "🚨 Weekly Report Generation Failed"
18    msg['From'] = EMAIL_CONFIG['sender_email']
19    msg['To'] = "admin@company.com"
20    
21    # Send notification...

Troubleshooting Common Issues

Issue 1: SMTP Authentication Failed

For Gmail: Enable "Less secure app access" or use App Password
For Office 365: Use port 587 with STARTTLS
Check firewall isn't blocking SMTP ports

Issue 2: Charts Not Displaying in Email

Ensure Content-ID matches the image src (e.g., cid:revenue_trend)
Use MIMEMultipart('related') not MIMEMultipart('alternative')
Check image files exist before attaching

Issue 3: Data Processing Errors

Add data validation: Check for missing dates, negative values
Handle edge cases: What if no data for the week?
Add logging to identify where failures occur

Issue 4: Scheduler Not Running

For schedule library: Process must stay running
For cron: Check cron logs: grep CRON /var/log/syslog
Ensure Python path is absolute in cron jobs

Real-World ROI

Here's the actual time savings from automating weekly reports:

Before automation (per week):

Manual data collection: 30 min
Analysis and calculations: 25 min
Chart creation: 20 min
Email formatting: 15 min
Distribution and follow-up: 10 min
Total: 100 minutes per week

After automation (per week):

Review automated report: 5 min
Address questions: 10 min
Total: 15 minutes per week

Time saved: 85 minutes per week = 74 hours per year

At $75/hour average salary, that's $5,550 in reclaimed time annually.

Frequently Asked Questions

What if my data source is an API, not a database? Replace the load_data() method with API calls using the requests library. Parse JSON responses into pandas DataFrames.

Automate Weekly Email Reports with Python: Save 5 Hours Every Week

What You'll Build

Prerequisites

The Problem: Manual Reporting is a Time Drain

Solution Architecture

Step 1: Set Up Project Structure

Step 2: Configure Email Settings

Step 3: Build Data Processing Module

Step 4: Build Email Sender Module

Step 5: Create Main Orchestration Script

Step 6: Create Sample Data

Step 7: Test Your Report

Step 8: Automate with Scheduling

Advanced Enhancements

Enhancement 1: Pull Data from Database

Enhancement 2: Send to Slack Instead of Email

Enhancement 3: Add Error Notifications

Troubleshooting Common Issues

Real-World ROI

Frequently Asked Questions

Share this article

Automate Weekly Email Reports with Python: Save 5 Hours Every Week

What You'll Build

Prerequisites

The Problem: Manual Reporting is a Time Drain

Solution Architecture

Step 1: Set Up Project Structure

Step 2: Configure Email Settings

Step 3: Build Data Processing Module

Step 4: Build Email Sender Module

Step 5: Create Main Orchestration Script

Step 6: Create Sample Data

Step 7: Test Your Report

Step 8: Automate with Scheduling

Advanced Enhancements

Enhancement 1: Pull Data from Database

Enhancement 2: Send to Slack Instead of Email

Enhancement 3: Add Error Notifications

Troubleshooting Common Issues

Real-World ROI

Frequently Asked Questions

Share this article