0

I've wrote some code below that queries MySQL, Puts it into a Dataframe using Pandas, Gets the Top 5 rows and then puts this information into an Email using MimeMultipart.

In my email from my dataframe from SQL i'm want it attaching as a CSV (this works), then inputting hand written HTML text & getting a HTML table from the dataframe as previously mentioned.

However, the second HTML attachment i make using MIMEtext - it always ends up as a HTM attachment on the email rather than embedding into the body of the text. I've changed what gets attached first and this changes depending on the order. More strangely, this ONLY happens on the outlook desktop app and is working normally on my Iphone email app.

Is there a problem with my Code or just how the email is receiving the HTML info? Heads mashed with it!

### getting dataframe from MySQL
from mysql import connector
import pandas as pd
connection = connector.connect(host='localhost',user='root', password='', database='World')
### getting query
query = 'select * from city'
### getting df
data1 = pd.read_sql(query, con=connection)
### sorting values
data = data1.sort_values(by = 'Population', ascending = False)
print(data)
### getting top 10
data_new = data.head(5)
print(data_new)

### creating email function
### libaries
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.message import EmailMessage
from email import encoders
import smtplib
import os
import json
import pyodbc
import pandas as pd
from pretty_html_table import build_table
import io

### Creating our Bodies
html_body = """
<html>
    <head></head>
    <body>
    <p> Hi!<br><br>

        Hope all is well.<br><br>
        
        Here is the top 5 countries with the highest population figures.<br><br>
        
        The information is also attached above in an excel file.
        </p>
    </body>
</html>
"""

# 2. Creating a CSV attachment for our Dataframe
def export_csv(data_new):
    with io.StringIO() as buffer:
        data_new.to_csv(buffer, index=False)
        return buffer.getvalue()
    
EXPORTERS = {'Weekly Product Report.csv': export_csv}

# 3. Creating Table of Data
table1 = build_table(data_new, "blue_light")

### sending mail
def send_email_mb(table, body, df):
    # constructing your email
    multipart = MIMEMultipart()
    multipart['Subject'] = 'Please find attached your weekly report!'
    multipart['From'] = ''
    multipart['To'] = ''
    
    # creating body variables
    html_text = body
    table_ins = table

    # attaching df to email
    for filename in EXPORTERS:
        attachment = MIMEApplication(EXPORTERS[filename](df))
        attachment['content-Disposition'] = 'attachement; filename={}'.format(filename)
        multipart.attach(attachment)

    # adding email bodies to the constructor
    multipart.attach(MIMEText(html_text, 'html'))
    multipart.attach(MIMEText(table_ins, 'html'))

    ### convert message container to string
    msg = multipart.as_string()

     # create server variable
    server = smtplib.SMTP('', 000)
    
    # for outlook/external only, connect to server, test and start tls
    server.connect('', 000)
    server.ehlo()
    server.starttls()
    server.ehlo()
    ### end 
    
    # login to email/server
    server.login('', '')
    print("Logged into to server")
    
    # creating sender & reciever variable for email
    sender = ''
    receiver = ''
    print("Sending mail from " + sender, " Sending mail to " + receiver)
    
    # send mail
    server.sendmail(sender, receiver, msg)
    print("Mail Sent")
    
    # close connection
    server.quit()
    print("Left the server")

## calling the email
send_email_mb(table1, html_body, data_new)

I've tried changing the MIME Text Attachments around and this just changed what got attached and what didn't.

I've also tried changing to 'alternative' on the Multipart('') but i didnt expect that to work

Thanks

ZachtPy
  • 1
  • 1
  • I suspect this isn't a code issue if the behavior changes based on email client. I do recall Outlook having StrongOpinions™ about HTML in emails. – Michael Ruth Aug 15 '23 at 18:44
  • Interesting, thanks Michael. I thought as much, very annoying! I might have to attach as plain text if it wont allow both HTML MIME Text attachments – ZachtPy Aug 15 '23 at 19:07
  • It's been a long time since I've dealt with multipart emails and Outlook, so I don't have any specific answers. I remember something about Outlook expecting valid XHTML and messing with the content if it is not, but I could be mistaken. Regardless, it's best practice to provide plaintext with multipart emails. Some servers straight up forbid anything but plaintext. – Michael Ruth Aug 15 '23 at 20:34

0 Answers0