How to send emails to different reciver address with different subject from a pandas dataframe using python

Question

I have dataframe I like below df. I want to send an automated unique email with a unique subject.

I am not sure how to iterate the subject line and receiver address. Can you have look at my code?

Name   Invoice_Number     Invoice_Date    Currency   Amount   Email_Address
Ali     23456              2020-06-11     GBP         200      Ali@gmail.com
Charly  6789               2020-05-12     Euro        600      Charly@hotmail.com
Tom     7823               2020-06-18     Euro        400      Tom@gmail.com
Chang   8950               2020-04-13     SGD         600      Chang@yahoo.com
David   8934               2020-06-16     USD         500      NaN
Mia     78909              2019-12-23     GBP         600      Mia@gmail.com

Subject

Subject: [Name] / REJECTED Invoice [Invoice_Number] Dated [Invoice_Date] Amount [Currency + Amount]

example: Ali/ REJECTED Invoice [23456 ] Dated [2020-06-11] Amount [GBP+ 200]

How can this be done .

My code

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
from datetime import date
import os
import boto3
from os.path import basename
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.utils import formatdate, COMMASPACE

dict1 = {"Non-empty": { 'receiver': df[Email_Address], 
                        "mail_content":''' Invoice is inncorect''' } ,
        
         
        "Empty": { "receiver": 'address_not_found@gmail.com', 
                  "mail_content":" No data Found " }}


receiver_address = ""
mail_content = ""

if data1['email'].isnull:
    receiver_address = dict1["Empty"]["receiver"]
    mail_content = dict1["Empty"]["mail_content"]
else:
    receiver_address = dict1["Non-empty"]["receiver"]
    mail_content = dict1["Non-empty"]["mail_content"]

def create_message(send_from, send_to, subject,plain_text_body):
    message = MIMEMultipart('alternative')
    message['From'] = send_from
    message['To'] = COMMASPACE.join(send_to)
    message['Subject'] = subject
    message.attach(MIMEText(plain_text_body, 'plain'))
    return message

def send_message(message):
    client = boto3.client("ses",region_name='eu-west-1')
    response = client.send_raw_email(RawMessage = {'Data': message.as_string()})


message = create_message(sender_address,[receiver_address ],subject,mail_content)
send_message(message)

Answer 1

So one way you can generate a subject line for all these rows in your DataFrame is by making a new column that is a constructed string built from the other columns in the DataFrame:

import pandas as pd

df = pd.DataFrame(dict(
    Name = ['A','B'],
    Invoice_Number = [1,2],
    Invoice_Date = ['2020-06-11','2020-05-12'],
    Currency = ['GBP','Euro'],
    Amount = [200, 600],
    Email_Address = ['A@email.com','B@emial.com'],
))

df['Subject'] = (
    "["+
    df['Name']+
    "] / REJECTED Invoice ["+
    df['Invoice_Number'].astype(str)+
    "] Dated ["+
    df['Invoice_Date']+
    "] Amount ["+
    df['Currency']+
    df['Amount'].astype(str)+
    "]"
)

print( df['Subject'].values)

Now you have a subject line ready to go for all the emails.

Example Code in python Tutor

Edit: OP asked the follow up question:

@PhillyCaluse89, Do how do I pass this value to the send_email function

I suppose you could loop through df.values and send each message like so:

import boto3
import pandas as pd

# I get an import error with `from email.utils import formatdate, COMMASPACE`
# I think this should be a constant not an import
# COMMASPACE = ", "
# I don't think you need this at all though, since you appear to want to send the emails to individual recipients.
# SENDER_ADDRESS might be a good constant to have though
SENDER_ADDRESS = "youremail@email.com"


# Moved all functions to the top of the script
# I don't think you need to use email imports, so try cutting it down to just one send_message function
def send_message(send_from, send_to, subject, plain_text_body):
    client = boto3.client("ses", region_name='eu-west-1')
    response = client.send_email(
        Destination={
            'BccAddresses': [
            ],
            'CcAddresses': [
            ],
            'ToAddresses': [
                send_to
            ],
        },
        Message={
            'Body': {
                'Text': {
                    'Charset': 'UTF-8',
                    'Data': plain_text_body,
                },
            },
            'Subject': {
                'Charset': 'UTF-8',
                'Data': subject,
            },
        },
        ReplyToAddresses=[
        ],
        ReturnPath='',
        ReturnPathArn='',
        Source=send_from,
        SourceArn='',
    )
    print(response)


# Adding a test df
df = pd.DataFrame(dict(
    Name=['A', 'B'],
    Invoice_Number=[1, 2],
    Invoice_Date=['2020-06-11', '2020-05-12'],
    Currency=['GBP', 'Euro'],
    Amount=[200, 600],
    Email_Address=['A@email.com', 'B@emial.com'],
))

# Adding a new column to the df containing the constructed subject line string:
df['Subject'] = (
        "[" +
        df['Name'] +
        "] / REJECTED Invoice [" +
        df['Invoice_Number'].astype(str) +
        "] Dated [" +
        df['Invoice_Date'] +
        "] Amount [" +
        df['Currency'] +
        df['Amount'].astype(str) +
        "]"
)

# Start a loop through the df.values (Ensure the variables you unpack df.values into match the order of your columns:
for email, subject in df[['Email_Address', 'Subject']].values:
    #Not sure why you need that dict, seemed a bit overkill to me, could just do:
    if email and not pd.isna(email):
        receiver_address = email
        mail_content = ''' Invoice is inncorect'''
    else:
        receiver_address = 'address_not_found@gmail.com'
        mail_content = " No data Found "
    #calling the function with these params should work if I'm undersanding the docs correctly
    send_message(SENDER_ADDRESS, receiver_address, subject, mail_content)

But this is all speculation based on skimming the docs you linked me to in your follow up comment. I most certainly could be overlooking something here.