Automate Your Daily Reports with a Python Email Bot

Introduction

Manually creating and sending daily reports can quickly become a repetitive task — especially if your reports rely on CSV data exports. Automating this process with Python saves time, reduces human error, and ensures consistency. In this post, we’ll walk through building a Python email bot that collects CSV data, summarizes it using pandas, and automatically emails the formatted results every morning.

Section 1: Setting Up Your Project Environment

Before diving in, make sure you have the following dependencies installed:

pip install pandas smtplib email schedule

We’ll use pandas for data summarization, smtplib and email for sending emails, and schedule (optional) for automating the daily task. Here’s the basic script skeleton:

import pandas as pd
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import schedule
import time

This ensures our script is ready to handle CSV data processing, email generation, and timing.

Section 2: Reading and Summarizing CSV Data with Pandas

Suppose you receive a daily CSV file named sales_data.csv. Let’s load and summarize it:

def summarize_data(csv_path):
    df = pd.read_csv(csv_path)
    summary = df.groupby('region')['sales'].sum().reset_index()
    total_sales = df['sales'].sum()
    summary_html = summary.to_html(index=False)

    return f"Sales Summary
{summary_html}Total Sales: ${total_sales}"

# Example usage:
report_html = summarize_data('sales_data.csv')

Here’s what’s happening:

We group sales by region for clarity.
We compute the total sales for a quick overview.
We convert the summary DataFrame to HTML for email-friendly formatting.

Section 3: Sending Emails Automatically

To send an email in Python, we can use the smtplib and email.mime libraries. Here’s how to structure the sending logic:

def send_email(sender, password, recipient, subject, html_content):
    msg = MIMEMultipart('alternative')
    msg['From'] = sender
    msg['To'] = recipient
    msg['Subject'] = subject

    msg.attach(MIMEText(html_content, 'html'))

    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
        server.login(sender, password)
        server.send_message(msg)

    print('Email sent successfully!')

Use SMTP_SSL for secure email sending. Make sure you’re using an app-specific password (if on Gmail). For best practice, store credentials in environment variables rather than in plain text:

import os
sender = os.getenv('EMAIL')
password = os.getenv('EMAIL_PASSWORD')

Section 4: Scheduling the Automation

Once we can send emails on demand, let’s automate it using the schedule library so that it runs every morning:

def job():
    report_html = summarize_data('sales_data.csv')
    send_email(os.getenv('EMAIL'), os.getenv('EMAIL_PASSWORD'), 'manager@company.com', 'Daily Sales Report', report_html)

# Schedule at 8 AM every day
schedule.every().day.at('08:00').do(job)

while True:
    schedule.run_pending()
    time.sleep(60)

This loop runs indefinitely, executing the report-sending function at 8 AM each day. You can run this script as a background service or in a cloud environment like AWS Lambda or a small VPS.

Section 5: Making It More Robust and Production-Ready

To improve reliability and scalability, consider:

Error Handling: Use try...except around your email and file-reading functions to handle missing files or network issues.
Logging: Use logging instead of print statements for better observability.
Dynamic Filenames: Automate detection of the latest CSV report using glob.glob('*.csv').
Deployment: Run the script via cron on Linux or Task Scheduler on Windows to ensure reliability.

import logging

logging.basicConfig(level=logging.INFO, filename='report_bot.log')
try:
    job()
except Exception as e:
    logging.error(f'Error in job: {e}')

With these improvements, your email bot becomes a robust component of your business automation workflow.

Conclusion

By combining pandas, smtplib, and schedule, we’ve created a fully automated system that summarizes and emails CSV reports daily. This approach can be adapted to other reporting workflows — such as inventory tracking, finance summaries, or performance dashboards — giving you more time to focus on analysis rather than manual tasks.

Useful links: