Automate Sending Email with Embedded Spreadsheet Table Using Python.

Source : Google Image

The ways how to observe company growth are by producing daily, weekly and monthly report that help upper management to get an overview about company growth, if something abnormal in sale data, fast action can be taken to make sure the problem not effect too long and make sure business can run in an efficient way. Therefore, to produce daily, weekly or monthly reports some of us still use a manual way, where we need to download the data first, process it, analyze the data and send the output by email. I’m sure, if you do as the way I mentioned before, you will spend at least 45 minutes to complete the task. But, if you want to save your 45 minutes every day and use the time for an extra breakfast time, you need to read this article until the end.

Spreadsheet Daily Report : The numbers not represent real data.

For example, I need to generate and send the daily report like picture above. When I do it manually, I need to generate the report first, then copy the table and paste into body email. Sometime I feel bored doing the same thing every day….

So Lets move into Automation Process which is very beneficial for us.

In general, this article will help you how to generate a daily report, and send the output by email to upper management by using python. You can refer to the picture below to get a general idea about the workflow.

This guide will use App Scripts and python 3.9 to complete the task, some of you may ask, why not directly use the add-on function inside a spreadsheet to send the email? The answer because email add-on functions have limitations on email body size and my target is to embed the image into the email body so the recipients can directly observe the output instead of the image as an attachment, the recipients need to download the file first before they can observe the image.

Ok Lets Start…😉😉😉

To generate the report by using Apps Scripts, for this step you can refer to this link and follow the step how to generate the report by using Apps Scripts. The guides are easy to follow and I also follow the guide to extract and process the data. After you finish with the link I provide, then we can continue with email automation by using python.

First step : Download the spreadsheet

To download the spreadsheet, we can download the file in any format that we want, but what I can suggest here, download the file in pdf format to make sure the file format are not changed during downloading the file.

from requests import get
def get_spreadsheet_link(id:str = None, sheet_name:str = None,  ext:str = 'csv'):
    if id is None:
        print('No ID is given, cancelling this process')
        return None
    url = f'https://docs.google.com/spreadsheets/d/{id}/export?  exportFormat={ext}&format={ext}&id={id}'
    if ext == 'pdf':
        if sheet_name is not None:
            url = f'{url}&sheet={sheet_name}&gid=0'
    else:
        url = f'{url}&sheet={sheet_name}'
    return url
def write_content(content, file_name):
    with open(file_name, 'wb') as w:
        w.write(content)
def process(id=None, sheet_name=None, file_name=None, ext='pdf'):
    ext = ext.lower()
    url = get_spreadsheet_link(id=id, sheet_name=sheet_name, 
    ext=ext)
    res = get(url)
    res.raise_for_status()
    print('Proceed to download file')
    file_name = f'{file_name}.{ext}'
    write_content(res.content, file_name)
    print('Success')
    return file_name

You can directly rewrite the code above to download the spreadsheet. Just need to call “process” function and pass 4 parameters to make sure the code a working, the important parameters are described as below:-

1. Id : it is spreadsheet id, you can get from the spreadsheet URL link.
2. sheet_name : the sheet that you want to download
3. file_name : give name to downloaded file
4. ext : ext is the extension file format, as default the file will download in pdf format, but you can change into any format that you want, for example csv.

example to download the spreadsheet :

process('DgHjk190njhamsJklMnaplq23', sheet_name='sheet1',file_name='download', ext='pdf')

Second Step : Image Preparation.

For image preparation, there are 2 parts we need to focus on, first to convert the PDF file into PNG file format and second to crop the image in a specific size.

First, we need to convert PDF files into PNG file format. The way I convert PDF into PNG format is by screenshot each page in PDF format and saving it as an image. The code is as below.

def screenshort(page,name,pdffile):
    doc = fitz.open(pdffile)
    page = doc.loadPage(page)  # number of page
    zoom = 10 # image quality
    mat = fitz.Matrix(zoom, zoom)
    pix = page.getPixmap(matrix = mat)
    page = str(page)
    image = f'_{name}' + '.png'
    output = os.path.join(os.getcwd(), image)
    pix.writePNG(output)

code explanations,

1. page : refer to page number of pdf file that you want to screenshot, the
   PDF page count starts from 0,1,2,3 and the rest.
2. name : name of the image you want to save the PNG file.
3. pdffile : pdf file you want to work with.

After doing screenshots of the PDF file, the next step is to crop the image to a specific size. The code as below:

def crop(image, coords):
    image_obj = Image.open(image)
    cropped_image = image_obj.crop(coords)
    cropped_image.save(image)

code explanations,

1. image : path of the image file
2. coords : coordinates or the size of the image, the coords parameters need to pass in list data type with position as (left, upper, right, lower). You can use Paint apps to know the coordinate of the size to crop the image. Refer this link to understand more about coords.

The code above I save the cropped image with the same name as before the image had been cropped. If you want to change or use a different image name after crop the image, you need to change the code as below, image_out as the image name after you crop the image, but don’t forget to put the file extension.

def crop(image, coords,image_out):
    image_obj = Image.open(image)
    cropped_image = image_obj.crop(coords)
    cropped_image.save(image_out)

Example, how to call the function to crop the image,

crop('image.png', (480, 700, 5650,7200 ))

I assume you already know how to download the image from a spreadsheet using python, convert the pdf file into an image and crop the image into a specific size. Then we can focus on how to automate the email automation to send the email every day.

Third Step : Email Automation

For email automation, the list of recipients email I save on excel file. The reason why I save the email list in excel file is because it is easy if we want to add or remove the recipients from the list, and it is more convenient to make changes on the list.

Inside the code below, I use simple html code. The reason I write the html is because I want to embed the image and write a message inside the email body. Therefore, if you want to customize the message inside the email body, to embed an image or anything you like to put inside the email body, you need to write inside the html code. You can try to write the html code here first, before pasting inside the code. I am also not familiar with html, but the link I attached before really helps me and the link also provides a note about html if you need it.

def email(df,subject,image1):
    df = pd.read_excel(df,sheet_name='Sheet1')
    to = df['to'].dropna().tolist()
    cc = df['cc'].dropna().tolist()
    rec = to + cc
    msg = MIMEMultipart('related')
    msg['Subject'] = f'Testing - {subject} : {yesterday}'
    msg['From'] = 'your_email@outlook.com'
    msg['To'] = ",".join(to)
    msg['Cc'] = ",".join(cc)
html = """\
    <html>
    <head></head>
        <body>
        <br>
        <img src="cid:image1" alt="Logo" style="display: block;margin: auto;width: 100%;"><br> 
        <p><h4 style="font-size:15px;">Thank You.</h4></p> 
        <br>
        </body>
    </html>
    """
    part2 = MIMEText(html, 'html')
# Attach parts into message container.
    msg.attach(part2)
    fp = open(os.path.join(os.getcwd(), image1), 'rb')
    msgImage1 = MIMEImage(fp.read())
    fp.close()
    msgImage1.add_header('Content-ID', '<image1>')
    msg.attach(msgImage1)
with smtplib.SMTP('smtp.office365.com', 587) as server:
            server.ehlo()
            server.starttls()
            server.ehlo()
            server.login('your_email@outlook.com', "password")
            server.sendmail('your_email@outlook.com', rec, msg.as_string())

Then you need to call the function to make sure the program run step by step, example as below,

# download spreadsheets
process('DgHjk190njhamsJklMnaplq23', sheet_name='sheet1',file_name='download', ext='pdf')
screenshort(0,'image','file.pdf')
crop('image.png', (480, 700, 5650,7200 ))
email('email_list.xlsx','Daily Report',image1='image.png')
print('complete')

If you have multiple image needs to embed inside the email body, you need to add the code as below,

html = """\
    <html>
    <head></head>
        <body>
        <br>
        <img src="cid:image1" alt="Logo" style="display: block;margin: auto;width: 100%;"><br> 
        <img src="cid:image1" alt="Logo" style="display: block;margin: auto;width: 100%;"><br> 
        <p><h4 style="font-size:15px;">Thank You.</h4></p> 
        <br>
        </body>
    </html>
    """
    part2 = MIMEText(html, 'html')
# Attach parts into message container.
    msg.attach(part2)
    fp = open(os.path.join(os.getcwd(), image1), 'rb')
    msgImage1 = MIMEImage(fp.read())
    fp.close()
    msgImage1.add_header('Content-ID', '<image1>')
    msg.attach(msgImage1)
    
    fp = open(os.path.join(os.getcwd(), image2), 'rb')
    msgImage2 = MIMEImage(fp.read())
    fp.close()
    msgImage2.add_header('Content-ID', '<image2>')

And now, I’m sure you are ready to automate your daily email. Overall, I already explained step by step how to automate the daily report. I hope you learn something and are confident to create your first email automation.

Btw, here the full code,

from requests import get
from tempfile import NamedTemporaryFile
import fitz
import os
import pandas as pd
import numpy as np  
from PIL import Image
#### email library #####
import smtplib
from email import encoders
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.mime.image import MIMEImage
def get_spreadsheet_link(id:str = None, sheet_name:str = None, ext:str = 'csv'):
    if id is None:
        print('No ID is given, cancelling this process')
        return None
url = f'https://docs.google.com/spreadsheets/d/{id}/export?exportFormat={ext}&format={ext}&id={id}'
    if ext == 'pdf':
        if sheet_name is not None:
            url = f'{url}&sheet={sheet_name}&gid=0'
    else:
        url = f'{url}&sheet={sheet_name}'
return url
def write_content(content, file_name):
    with open(file_name, 'wb') as w:
        w.write(content)
def process(id=None, sheet_name=None, file_name=None, ext='pdf'):
    ext = ext.lower()
    url = get_spreadsheet_link(id=id, sheet_name=sheet_name, ext=ext)
    res = get(url)
res.raise_for_status()
print('Proceed to download file')
    file_name = f'{file_name}.{ext}'
    write_content(res.content, file_name)
    print('Success')
    return file_name
def screenshort(page,name,pdffile):
    doc = fitz.open(pdffile)
    page = doc.loadPage(page)  # number of page
    zoom = 10 # image quality
    mat = fitz.Matrix(zoom, zoom)
    pix = page.getPixmap(matrix = mat)
    page = str(page)
    image = f'_{name}' + '.png'
    output = os.path.join(os.getcwd(), image)
    pix.writePNG(output)
def crop(image, coords):
    image_obj = Image.open(image)
    cropped_image = image_obj.crop(coords)
    cropped_image.save(image)
def email(df,subject,image1):
    df = pd.read_excel(df,sheet_name='Sheet1')
    to = df['to'].dropna().tolist()
    cc = df['cc'].dropna().tolist()
    rec = to + cc
msg = MIMEMultipart('related')
    msg['Subject'] = 'Daily Report'
    msg['From'] = 'your_email@outlook.com'
    msg['To'] = ",".join(to)
    msg['Cc'] = ",".join(cc)
html = """\
    <html>
    <head></head>
        <body>
        <br>
        <img src="cid:image1" alt="Logo" style="display: block;margin: auto;width: 100%;"><br> 
        <p><h4 style="font-size:15px;">Thank You.</h4></p> 
        <br>
        </body>
    </html>
    """
    part2 = MIMEText(html, 'html')
# Attach parts into message container.
    msg.attach(part2)
    fp = open(os.path.join(os.getcwd(), image1), 'rb')
    msgImage1 = MIMEImage(fp.read())
    fp.close()
    msgImage1.add_header('Content-ID', '<image1>')
    msg.attach(msgImage1)
with smtplib.SMTP('smtp.office365.com', 587) as server:
            server.ehlo()
            server.starttls()
            server.ehlo()
            server.login('your_email@outlook.com', "password")
            server.sendmail('your_email@outlook.com', rec, msg.as_string())
# download spreadsheets
process('DgHjk190njhamsJklMnaplq23', sheet_name='sheet1',file_name='download', ext='pdf')
screenshort(0,'image','file.pdf')
crop('image.png', (480, 700, 5650,7200 ))
email('email_list.xlsx','Daily Report',image1='image.png')
print('complete')

Thank you.👏👏👏👏👏