CODEWITHSUNDEEP
python-3.xamazon-web-servicesaws-lambdaboto3amazon-ses
jimiclapton
jimiclapton

Reputation: 889

Send xlsx file using SES in AWS lambda function

SITUATION

I have created a lambda function the output of which is a an Excel file that gets saved to an S3 bucket. This part of the function works as expected. As part of the functions operation I would also like to be able to email the generated Excel file to selected recipients.

CODE:

#IMPORT MODULES 
import boto3 

import pandas as pd

import io 
from io import BytesIO
from io import StringIO 

from datetime import date

import email
import email.header
import email.policy
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart

def lambda_handler(event, context):

# GENERATE CURRENT DATE TO APPEND TO FILE 
    today = date.today()
    date_val = today.strftime("%B %d, %Y")

# CREATE DATAFRAME
    df = pd.DataFrame({'Data': [10, 22, 31, 43, 57, 99, 65, 74, 88]})

# EVALUATE VARIABLES AS ODD OR EVEN INTEGERS 
    even = df.loc[df['Data']%2 == 0]
    odd = df.loc[df['Data']%2 != 0]

# SPECIFY BUKCET NAME AND OUTPUT FILE PATH 
    bucket = 'my-bucket'
    filepath = 'output/My_Excel_File_{}.xlsx'.format(date_val)

# EXPORT MULTI-SHEET EXCEL FILE AND SEND TO S3 BUCKET 
    with io.BytesIO() as output:
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            even.to_excel(writer, sheet_name = 'Even')
            odd.to_excel(writer, sheet_name = 'Odd')
        data = output.getvalue()
    s3 = boto3.resource('s3')
    s3.Bucket(bucket).put_object(Key=filepath, Body=data)

WHAT I HAVE TRIED

I have tried to achieve my aim by appending the following code to my function by referring to various documentation, however this does not achieve the desired result.

# EXPORT MULTI-SHEET EXCEL FILE AND SEND TO S3 BUCKET 
message = MIMEMultipart()
message['Subject'] = 'Email subject'
message['From'] = '[email protected]'
message['To'] = '[email protected]')

# MESSAGE BODY
part = MIMEText('Thus is the email body string', 'html')
message.attach(part)
# ATTACHEMENT
if attachment_string:   # if bytestring available
    part = MIMEApplication(str.encode('attachment_string'))
else:    # if file provided
    part = MIMEApplication(s3.get_object(Bucket='my-bucket', Key=My_Excel_File_{}.xlsx'.format(date_val)).read())
part.add_header('Content-Disposition', 'attachment', filename='My_Excel_File_{}.xlsx'.format(date_val)')
message.attach(part)
response = client.send_raw_email(
    Source=message['From'],
    Destinations=['[email protected]'],
    RawMessage={
        'Data': message.as_string()
    }
)

Upvotes: 0

Views: 2182

Answers (1)

smac2020
smac2020

Reputation: 10734

There are AWS examples that dynamically create excel docs and email them. In this use cases, they are implemented in Java and the app is a web app. See this:

Creating the DynamoDB web application item tracker

Although this example uses the AWS SDK for Java V2, it will give you an idea and hopefully you can port to the programming language you are using.

Upvotes: 1

Related Questions