send email with a pandas dataframe as attachment

Question

I have a pandas dataframe that I am looking to convert into an xlsx and attach to an email. I can send emails based on outlook(this is the only way I can do it). I am able to convert the dataframe into an xlsx and save it on my drive and afterwards send it as attachment but I am looking to attach it directly without having to save it on my drive. see bellow my function to send emails:

def email():
olMailItem = 0x0
obj = win32com.client.Dispatch("Outlook.Application")
newMail = obj.CreateItem(olMailItem)
newMail.Subject ="FRANCE SO"
newMail.Body =' '
newMail.To = "email adress"
newMail.Attachments.Add(attachment)
newMail.Send()
return

attachment is the dataframe that has been transformed into xlsx

Answer 1

Have you tried to play with the io module in Python 3? It allows you to use streams as file-like objects, so that APIs that expect a file can read from or save their content to the stream instead.

That works nicely, using a StringIO along with pandas.DataFrame.to_csv:

import io

def export_csv(df):
  with io.StringIO() as buffer:
    df.to_csv(buffer)
    return buffer.getvalue()

That works, because to_csv expects a string (interpreted as a path) or a file handle, and StringIO can be used like a file handle. Unfortunately, pandas.DataFrame.to_excel works with either a string (interpreted as a path) or an ExcelWriter. In that case, we need to create the ExcelWriter ourselves, and wrap a BytesIO with it.

import io
import pandas as pd

def export_excel(df):
  with io.BytesIO() as buffer:
    with pd.ExcelWriter(buffer) as writer:
        df.to_excel(writer)
    return buffer.getvalue()

I am not familiar with the Outlook Python tools for sending emails, I use SMTP:

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

SEND_FROM = '[email protected]'
EXPORTERS = {'dataframe.csv': export_csv, 'dataframe.xlsx': export_excel}

def send_dataframe(send_to, subject, body, df):
  multipart = MIMEMultipart()
  multipart['From'] = SEND_FROM
  multipart['To'] = send_to
  multipart['Subject'] = subject
  for filename in EXPORTERS:    
    attachment = MIMEApplication(EXPORTERS[filename](df))
    attachment['Content-Disposition'] = 'attachment; filename="{}"'.format(filename)
    multipart.attach(attachment)
  multipart.attach(MIMEText(body, 'html'))
  s = smtplib.SMTP('localhost')
  s.sendmail(SEND_FROM, send_to, multipart.as_string())
  s.quit()

I hope this helps, good luck!