Python Automation Projects for Office Work

There is a version of your workday where you spend Monday morning writing the same weekly report you wrote last Monday, and the Monday before that, and every Monday for the last eight months. You copy numbers from one spreadsheet, paste them into another, format the table, attach it to an email, and send it to the same twelve people. The whole thing takes two hours. Nothing about it requires your brain. It just requires your time.

Python can do that entire workflow while you drink your coffee. Not a simplified version of it. The actual thing, pulling the data, formatting the output, attaching the file, and sending the email, automatically, on a schedule, without you touching it.

This guide covers the most useful Python automation projects for office work, with code you can copy, adapt, and run today. No advanced programming background required. If you know basic Python and have installed a library before, you can build all of these.

What You Need Before You Start

Every project in this guide uses standard Python libraries. Install the ones you need for each project using pip. The main ones across all projects are openpyxl for reading and writing Excel files, pandas for data manipulation, smtplib and email for sending automated emails, python-docx for working with Word documents, PyPDF2 or pypdf for PDF handling, os and shutil for file and folder management, and schedule for running scripts on a timer.

pip install openpyxl pandas python-docx pypdf schedule

All of these work on Windows, macOS, and Linux. Python 3.10 or above is recommended for all the projects below.

Project 1: Automated Weekly Report Generator

This is the most immediately useful automation for most office workers. The script reads data from a source file or database, calculates key metrics, builds a formatted Excel report, and saves it with a timestamped filename so every weekly output is stored automatically.

The real power comes when you combine this with a scheduler so it runs every Monday at 7am and the report is waiting in a folder before anyone arrives at the office.

import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from datetime import datetime

# Load source data
df = pd.read_csv('sales_data.csv')
df['order_date'] = pd.to_datetime(df['order_date'])

# Filter to current week
today = datetime.today()
start_of_week = today - pd.Timedelta(days=today.weekday())
weekly_df = df[df['order_date'] >= start_of_week]

# Calculate summary metrics
summary = weekly_df.groupby('region').agg(
    total_sales=('revenue', 'sum'),
    total_orders=('order_id', 'count'),
    avg_order_value=('revenue', 'mean')
).round(2).reset_index()

# Write to Excel with formatting
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Weekly Summary'

# Header styling
header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
header_font = Font(color='FFFFFF', bold=True)

headers = ['Region', 'Total Sales', 'Total Orders', 'Avg Order Value']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal='center')

# Write data rows
for row_idx, row in summary.iterrows():
    ws.append(row.tolist())

# Auto-fit column widths
for col in ws.columns:
    max_length = max(len(str(cell.value or '')) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_length + 4

# Save with timestamp
filename = f"weekly_report_{today.strftime('%Y_%m_%d')}.xlsx"
wb.save(filename)
print(f"Report saved: {filename}")

What this replaces: Two hours of manual data pulling, copying, formatting, and saving every week. Once the script is set up and scheduled, it runs without anyone touching it.

Project 2: Automated Email Sender With Attachments

This script sends emails automatically from your Python script, including file attachments. The most common office use case is sending a report to a distribution list after the report generator above creates it. Chain these two scripts together and you have a fully automated weekly reporting pipeline.

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import os

def send_report_email(recipient_list, subject, body, attachment_path):
    sender_email = 'your_email@gmail.com'
    sender_password = 'your_app_password'  # Use app password, not main password

    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = ', '.join(recipient_list)
    msg['Subject'] = subject

    msg.attach(MIMEText(body, 'plain'))

    # Attach the file
    if attachment_path and os.path.exists(attachment_path):
        with open(attachment_path, 'rb') as f:
            part = MIMEBase('application', 'octet-stream')
            part.set_payload(f.read())
            encoders.encode_base64(part)
            part.add_header(
                'Content-Disposition',
                f'attachment; filename={os.path.basename(attachment_path)}'
            )
            msg.attach(part)

    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
        server.login(sender_email, sender_password)
        server.sendmail(sender_email, recipient_list, msg.as_string())
        print(f"Email sent to {len(recipient_list)} recipients.")

# Example usage
recipients = ['manager@company.com', 'team@company.com']
send_report_email(
    recipient_list=recipients,
    subject='Weekly Sales Report - Auto Generated',
    body='Hi team,\n\nPlease find this week\'s sales report attached.\n\nThis report was generated automatically.',
    attachment_path='weekly_report_2026_05_11.xlsx'
)

Important note on Gmail: Google requires you to use an App Password rather than your main account password for scripts that access Gmail via SMTP. You generate an App Password from your Google Account security settings under two-factor authentication. Store it as an environment variable rather than hardcoding it in your script.

What this replaces: Manually composing the same email to the same distribution list week after week. Combined with the report generator above, the entire reporting workflow becomes zero-touch.

Project 3: Bulk Excel File Merger

If you work in a role where multiple people submit data in separate Excel files and you are the person who combines them all into one master file every week or month, this script eliminates that entire task.

It reads every Excel file in a folder, appends them into a single DataFrame, adds a column showing which file each row came from, and saves the merged result.

import pandas as pd
import os
from pathlib import Path

def merge_excel_files(folder_path, output_filename='merged_output.xlsx'):
    folder = Path(folder_path)
    all_files = list(folder.glob('*.xlsx')) + list(folder.glob('*.xls'))

    if not all_files:
        print("No Excel files found in the specified folder.")
        return

    dataframes = []

    for file in all_files:
        try:
            df = pd.read_excel(file)
            df['source_file'] = file.name  # Track which file each row came from
            dataframes.append(df)
            print(f"Loaded: {file.name} ({len(df)} rows)")
        except Exception as e:
            print(f"Could not read {file.name}: {e}")

    merged_df = pd.concat(dataframes, ignore_index=True)

    output_path = folder / output_filename
    merged_df.to_excel(output_path, index=False)
    print(f"\nMerged {len(all_files)} files into {output_filename}")
    print(f"Total rows: {len(merged_df)}")

# Run it
merge_excel_files('path/to/your/reports/folder')

What this replaces: Opening each file individually, copying the rows, pasting into a master file, closing, opening the next one, repeating. With 20 files that is an hour of copy-paste. With this script it is ten seconds.

Project 4: Automatic File Organizer

Downloads folders, desktop folders, and shared drives where nobody maintains any structure are a universal office problem. This script monitors a folder and automatically moves files into subfolders organized by file type, or by date, or by any naming pattern you define.

import os
import shutil
from pathlib import Path
from datetime import datetime

# Define where each file type should go
FILE_TYPE_MAP = {
    'Documents': ['.pdf', '.docx', '.doc', '.txt', '.pptx', '.ppt'],
    'Spreadsheets': ['.xlsx', '.xls', '.csv'],
    'Images': ['.jpg', '.jpeg', '.png', '.gif', '.bmp', '.svg'],
    'Archives': ['.zip', '.rar', '.7z', '.tar', '.gz'],
    'Data': ['.json', '.xml', '.sql'],
}

def organize_folder(folder_path):
    folder = Path(folder_path)

    for file in folder.iterdir():
        if file.is_file():
            file_ext = file.suffix.lower()
            destination_folder = None

            for folder_name, extensions in FILE_TYPE_MAP.items():
                if file_ext in extensions:
                    destination_folder = folder / folder_name
                    break

            if destination_folder is None:
                destination_folder = folder / 'Other'

            destination_folder.mkdir(exist_ok=True)

            # Handle filename conflicts
            destination = destination_folder / file.name
            if destination.exists():
                timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
                destination = destination_folder / f"{file.stem}_{timestamp}{file.suffix}"

            shutil.move(str(file), str(destination))
            print(f"Moved: {file.name} -> {destination_folder.name}/")

organize_folder('C:/Users/YourName/Downloads')

What this replaces: Manually sorting files into folders. Run this on a messy downloads folder and a years-worth of filing happens in under a minute. Schedule it to run daily and the folder never gets cluttered again.

Project 5: PDF Report Generator From Data

Many office workflows produce reports as PDFs because they are easier to share and harder to accidentally edit. This script takes data from a CSV or Excel file and generates a formatted PDF report automatically using the reportlab library.

pip install reportlab

from reportlab.lib.pagesizes import A4
from reportlab.lib import colors
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
import pandas as pd
from datetime import datetime

def generate_pdf_report(data_file, output_pdf):
    df = pd.read_csv(data_file)

    doc = SimpleDocTemplate(output_pdf, pagesize=A4)
    styles = getSampleStyleSheet()
    elements = []

    # Title
    title = Paragraph(f"Sales Report - {datetime.today().strftime('%B %Y')}", styles['Title'])
    elements.append(title)
    elements.append(Spacer(1, 20))

    # Summary paragraph
    total_revenue = df['revenue'].sum()
    total_orders = len(df)
    summary_text = f"This report covers {total_orders} orders with total revenue of ${total_revenue:,.2f}."
    elements.append(Paragraph(summary_text, styles['Normal']))
    elements.append(Spacer(1, 20))

    # Build table data
    table_data = [df.columns.tolist()] + df.values.tolist()

    table = Table(table_data)
    table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1F4E79')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#EBF3FB')]),
        ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
    ]))

    elements.append(table)
    doc.build(elements)
    print(f"PDF report generated: {output_pdf}")

generate_pdf_report('sales_data.csv', 'monthly_report.pdf')

What this replaces: Formatting tables in Word, exporting to PDF, adjusting margins, realizing a column is cut off, fixing it, exporting again. The script produces consistent, clean output every time.

Project 6: Scheduled Task Runner

All five projects above become significantly more powerful when they run automatically on a schedule without you manually executing the script. The schedule library makes this straightforward.

import schedule
import time
from datetime import datetime

def run_weekly_report():
    print(f"Running weekly report at {datetime.now()}")
    # Call your report generator function here

def run_file_organizer():
    print(f"Organizing files at {datetime.now()}")
    # Call your file organizer function here

# Schedule tasks
schedule.every().monday.at("07:00").do(run_weekly_report)
schedule.every().day.at("18:00").do(run_file_organizer)

print("Scheduler running. Press Ctrl+C to stop.")
while True:
    schedule.run_pending()
    time.sleep(60)

For this to run even when you are not logged in, use Windows Task Scheduler on Windows or a cron job on macOS and Linux to launch the Python script at startup. Once that is set up, the entire automation stack runs independently and you only interact with the outputs.

Common Mistakes to Avoid

Hardcoding file paths instead of using relative paths or environment variables. A script with C:/Users/YourName/Documents hardcoded breaks the moment anyone else runs it or you rename a folder. Use pathlib and relative paths so the script works from any location.

Not handling errors. Automation scripts that crash silently are worse than no automation at all because nobody knows the task failed until the report is missing three weeks later. Wrap every file operation and email send in a try-except block and log failures to a text file so you have a record of what went wrong and when.

Storing passwords as plain text in scripts. Use environment variables or a secrets manager instead of writing credentials directly into your code. On Windows, set environment variables through System Properties. In Python, access them with os.getenv(‘EMAIL_PASSWORD’).

Building complex automation before testing the simple version. The first version of every automation script should do one thing: run correctly once on a test file. Add scheduling, error handling, and email notifications after the core logic is confirmed to work. Building everything at once makes debugging painful.

Automating a broken process. If the manual workflow is inefficient or produces wrong outputs, automating it just produces wrong outputs faster. Before automating any process, make sure the manual version works correctly and the logic is sound. Automation amplifies whatever is already there, good and bad.

Python Office Automation Cheat Sheet

Project	Library	What It Automates	Time Saved Per Week
Weekly report generator	pandas, openpyxl	Data pulling, formatting, saving	2 to 4 hours
Automated email sender	smtplib, email	Report distribution, follow-ups	30 to 60 minutes
Excel file merger	pandas	Combining team submissions	1 to 2 hours
File organizer	os, shutil, pathlib	Sorting downloads and shared folders	30 minutes
PDF report generator	reportlab	Formatted PDF output from data	1 to 2 hours
Scheduled task runner	schedule	Running all scripts automatically	All of the above

The argument for learning Python in an office context is not that it makes you a programmer. It is that it makes every tedious, repetitive thing someone currently spends hours doing every week something a script handles in seconds. The people in any organization who can build these scripts become the people everyone else asks for help. And more importantly, they get their time back to do the work that actually requires thinking.

FAQs