[FIXED] attach pandas dataframe as excel file in email triggered from python

Issue

I have a pandas dataframe which is basically reult of a sql query. I want attach as xlsx in an automated email triggered from python and that excel file should have the dataframe mentioned. How can this be done.

I am able to send the email without attachment successfully but not with the attachment.

import pandas as pd

data= pd.DataFrame(output of sqlquery) # this data is dataframe output of a sql query. 

# import necessary packages
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import smtplib
# create message object instance
msg = MIMEMultipart()
password = "password"
msg['From'] = "[email protected]"
msg['To'] = "[email protected]"
msg['Subject'] = "Messgae"
server = smtplib.SMTP('smtp.gmail.com:587')
server.starttls()
# Login Credentials for sending the mail
server.login(msg['From'], password)
server.sendmail(msg['From'], msg['To'], msg.as_string())

I dont want to attach the excel file from the file location. Firstly, have to write that dataframe into excel and then attach that.

Solution

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

fromaddr = '@gmail.com'
toaddr = ['@gmail.com', '@gmail.com', 
'@gmail.com']

msg = MIMEMultipart()

msg['From'] = fromaddr
msg['To'] = ", ".join(toaddr)
msg['Subject'] = "Hello"

body = "Sample Mail"

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

filename = "Data.xlsx"
attachment = open("C:\\Users\\Desktop\\file.xlsx", 
"rb")

part = MIMEBase('application', 'octet-stream')
part.set_payload((attachment).read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', "attachment; filename= %s" % filename)

msg.attach(part)

server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(fromaddr, "TYPEPASSWORD") #Type Password
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
server.quit()

Answered By - BALAJI R