Unable to attach Excel file to email using python - TypeError: expected bytes-like object, not Workbook

Question

I am working on python code which will add one string in excel file and will attach that file to email. I am sending this email using AWS SES.

When I am trying to run my code it is giving me below error -

TypeError: expected bytes-like object, not Workbook

Below is my code-

import boto3
import xlsxwriter
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart


def lambda_handler(event, context):
    client = boto3.client('ses',region_name=AWS_REGION)
    sender = "xxx@gmail.com"
    to = "aaa@gmail.com"
    workbook = xlsxwriter.Workbook('abc.xlsx') 
    worksheet = workbook.add_worksheet() 
    worksheet.write('A1', 'Hello..')
    #send email with attachment
    msg = MIMEMultipart()
    msg['Subject'] = 'Test Email'
    msg['From'] = sender
    msg['To'] = to
    body_text = MIMEText(BODY_TEXT, "html")
    attachment = MIMEApplication(workbook)
    attachment.add_header('Content-Disposition', 'attachment', filename='Expenses01.xlsx')
    msg.attach(attachment)
    msg.attach(body_text)
    response = client.send_raw_email(
        Source=sender,
        Destinations=[to],
        RawMessage={"Data": msg.as_string()}

    )

I know there is something wrong with workbook object. But I don't know how to resolve this issue. Can someone please help me out?

Answer 1

After reading XLSXWriter documentation, I found answer for this. Link - https://xlsxwriter.readthedocs.io/workbook.html

I am posting this answer, so that it can help other new python developers like me. Earlier MIMEApplication() was not accepting workbook object so we need to convert it. I have updated my code. I have used BytesIO to create Workbook object and then added that object to MIMEApplication(). This example will create excel file and will attach that file to email. New code-

from io import BytesIO
output = BytesIO()
    workbook = xlsxwriter.Workbook(output) 
    worksheet = workbook.add_worksheet() 
    worksheet.write('A1', 'Hello..')
attachment = MIMEApplication(output.getvalue())
    attachment.add_header('Content-Disposition', 'attachment', filename='abc.xlsx')
    attachment.add_header('Content-Type', 'application/vnd.ms-excel; charset=UTF-8')

Answer 2

Your question did not say where the error occurs, but I think it happens here:

attachment = MIMEApplication(workbook)

You should pass in bytes, rather than a complex python object.

Call workbook.close() to write out the 'abc.xlsx' file, and send that binary file as an attachment.