Revision history [back]

There are several ways to create an Excel attachment for an email using Python:

1. Using the pandas library:

import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

# Create DataFrame
data = {'Name': ['John', 'Jane', 'Alice', 'Bob'], 'Age': [25, 30, 35, 40]}
df = pd.DataFrame(data)

# Create Excel file
writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

# Create email message
msg = MIMEMultipart()
msg['Subject'] = 'Excel Attachment'
msg['From'] = 'sender@email.com'
msg['To'] = 'recipient@email.com'

# Attach Excel file
with open('data.xlsx', 'rb') as f:
    attach = MIMEApplication(f.read(), _subtype='xlsx')
    attach.add_header('Content-Disposition', 'attachment', filename='data.xlsx')
    msg.attach(attach)

# Send email
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login('sender@email.com', 'password')
server.sendmail(msg['From'], msg['To'], msg.as_string())
server.quit()

1. Using the openpyxl library:

import openpyxl
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

# Create Excel file
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Sheet1'
ws.cell(row=1, column=1, value='Name')
ws.cell(row=1, column=2, value='Age')
ws.append(['John', 25])
ws.append(['Jane', 30])
ws.append(['Alice', 35])
ws.append(['Bob', 40])
wb.save('data.xlsx')

# Create email message
msg = MIMEMultipart()
msg['Subject'] = 'Excel Attachment'
msg['From'] = 'sender@email.com'
msg['To'] = 'recipient@email.com'

# Attach Excel file
with open('data.xlsx', 'rb') as f:
    attach = MIMEApplication(f.read(), _subtype='xlsx')
    attach.add_header('Content-Disposition', 'attachment', filename='data.xlsx')
    msg.attach(attach)

# Send email
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login('sender@email.com', 'password')
server.sendmail(msg['From'], msg['To'], msg.as_string())
server.quit()

Note: In both examples, you will need to replace the email addresses and password with your own credentials. Also, make sure to have the necessary libraries installed before running the code.