How to send Excel as an attachment in email Using mandrill with Python 3.6.8

Question

Can anyone help on how to send an email with excel attachment using mandrill in python. I am using python 3.6.8 version as my project is old version and has dependencies which do not allow me to upgrade the python version. Here is the code I am using

 def _send_message(to, subject, htmlbody, add_header=True, cc=None, 
    bcc=None, attachment=None, attachment_content=None):
   if os.getenv('MGC_ENV') != 'prod':
       subject = '[{0}] '.format(os.getenv('MGC_ENV')) + subject

   if add_header:
       subject = 'MAP {0}'.format(subject)
    
    recipients = _generate_recipients(to, cc, bcc)

    if len(recipients) == 0:
       return

    bundle = {
       'to': recipients,
       'from_email': os.getenv('FROM_EMAIL'),
       'subject': subject,
       'html': htmlbody,
       'tags': _MANDRILL_TAGS,
       'preserve_recipients': True
     }

    if attachment and attachment_content:
        attachment = [{
          "type": 'application/xlsx',
          "name": 'OrderConfirmation.xlsx',
          "content": attachment_content.toString('base64')
        }]
        bundle["attachments"] = attachment

    if os.getenv('ENABLE_NOTIFICATIONS') == 'false':
       return

    _mandrill_client.messages.send(bundle)

The issue in the above code is the attachment is expecting the content as string encoded with base64 and when tried to set bytes array it wont work.

When we try to convert the excel into the type string it is not able to read and is breaking.

def export(licenses, plain_str=True): workbook = xlwt.Workbook()

    products_sheet = _create_product_sheet(workbook)

    product_row_offset = 1

    cell_style = xlwt.easyxf('font: underline single; font: color 
    blue')

    if licenses and len(licenses) > 0:
      for lic in licenses:
          row = products_sheet.row(product_row_offset)
          _write_product_row(row, lic, cell_style=cell_style)
          product_row_offset += 1

    if plain_str:

        content = utils.workbook_to_str(workbook)
     else:
        content = utils.workbook_to_bytes(workbook)
        return content



   def workbook_to_str(workbook):
       io_buffer = io.StringIO()
       workbook.save(io_buffer)

       return io_buffer.read()



   def workbook_to_bytes(workbook):
       io_buffer = io.BytesIO()
       workbook.save(io_buffer)

       return io_buffer.getvalue()

Thanks in advance for your answer.

Answer 1

I'm no Python wizard (yet) but I've got the MailChimp integration working (pretty much the same as Mandril) using the below.

The parts that I struggled with were encoding the xlsx - base64_encoded = base64.b64encode(df_xlsx).decode('UTF-8') and finding the correct attachment type 'type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

import mailchimp_transactional as MailchimpTransactional
from mailchimp_transactional.api_client import ApiClientError
import base64
from io import BytesIO
import pandas as pd

def send_email_notification():
    MANDRILL_API_KEY = Secret.load("mandrill-api-key")
    MAILCHIMP_API_KEY = MANDRILL_API_KEY.get()

    try:
        mailchimp = MailchimpTransactional.Client(MAILCHIMP_API_KEY)
        response = mailchimp.users.ping()
        print('API called successfully: {}'.format(response))
    except ApiClientError as error:
        print('An exception occurred: {}'.format(error.text))

    df = pd.DataFrame([['a', 'b'], ['c', 'd']],
                       index=['row 1', 'row 2'],
                       columns=['col 1', 'col 2'])

    def to_excel(df):
        output = BytesIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        df.to_excel(writer, sheet_name='Sheet1')
        writer.save()
        processed_data = output.getvalue()
        return processed_data

    df_xlsx = to_excel(df)

    base64_encoded = base64.b64encode(df_xlsx).decode('UTF-8')



    message = {
        "from_email": "noreply@emailtest.com",
        "from_name": "Test",
        "subject": "The model has run successfully",
        'attachments': [{'name': 'test.xlsx',
                         'type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                         'content': base64_encoded
                         }],
        "to": [
            {
                "email": emailaddress,
                "name": "",
                "type": "to"
            }
        ]
    }


    try:
        mailchimp = MailchimpTransactional.Client(MAILCHIMP_API_KEY)
        response = mailchimp.messages.send({"message": message})
        print('API called successfully: {}'.format(response))
    except ApiClientError as error:
        print('An exception occurred: {}'.format(error.text))


if __name__ == "__main__":
    send_email_notification()