CODEWITHSUNDEEP
pythonpandasdataframeemailoutlook
jjccgoh
jjccgoh

Reputation: 35

Python to loop through pandas dataframe, insert different recipients, title and email contents into outlook email

I have a dataframe (df) with the values of each column listed as per below:

Example of Dataframe

How do I loop through the pandas dataframe based on unique values in df['Owning'] and df['Type'] and seperate it to different Outlook e-mails with different Owner and CC list?

Below is the code to send for one 'Owning' type.


TROGY=df[df['Owning']=='TROGY']

TROGY_21=TROGY[[TROGY['Type']=='210s']

TROGY_22=TROGY[[TROGY['Type']=='220s']

TROGY_21C2=TROGY_21[TROGY_21['CLASS']=='CLASS 2']

TROGY_21C3=TROGY_21[TROGY_21['CLASS']=='CLASS 3']

TROGY_22C2=TROGY_22[TROGY_22['CLASS']=='CLASS 2']

TROGY_22C3=TROGY_22[TROGY_22['CLASS']=='CLASS 3']



sender = '[email protected]'

recipient = TROGY['Owner'] ---- group by df['Owning'] and df['Type'] 

subject = 'Highlight For the Day XXX, YYYYY'



XXX referring to df['Owning']

YYYYY referring to df['Type'] 



msg = MIMEMultipart('alternative')

msg['Subject'] = subject

msg['From'] = sender

msg['To'] = ','.join(recipient)


body = ("""\

<html>

  <head></head><h2 align=center>CONVERSION</h2>

  <body>

         <div>Dear Conversion Owners, below is the highlight for the day.</div>

  Class 2

    {0}

  Class 3

    {1}

  </body>

</html>


""".format(TROGY_21C2_Block.to_html(),TROGY_21C3.to_html()))


msg.attach(MIMEText(body, 'html'))

try:
   smtpObj = smtplib.SMTP('relay.xx.com', 'xx')

   smtpObj.sendmail(sender, recipient, msg.as_string()) 
        
   print ("Successfully sent emai")

   smtpObj.quit()

except smtplib.SMTPException:

   print ("Error: unable to send email")

Desired outcome will be total 5 emails be send out to the designated recipients stated in 'Owner' column with different email title.

Email 1 Title: 'Highlight For the Day TROGY, 210s'

Recipient: Kent, lili

Email 2 Title: 'Highlight For the Day TROGY, 220s'

Recipient: Kent,

Email 3 Title: 'Highlight For the Day PHM, 210s'

Recipient: bose

Email 4 Title: 'Highlight For the Day FUSION, 210s'

Recipient: Nikki

Email 5 Title: 'Highlight For the Day FUSION, 220s'

Recipient: gaddam

Upvotes: 1

Views: 286

Answers (1)

n1colas.m
n1colas.m

Reputation: 3989

You can use pandas groupby with ["Owning", "Type"] as arguments. After that, use to_list() over g["Owner"] to create the list of recipients. For the subject text you can use the return of the groupby function which is the name of the groups for each column used.

Input sample.csv

ID,Owning,CLASS,Type,Owner,CC list
GD8235,TROGY,CLASS 2,210s,Kent,John
GD8298,TROGY,CLASS 3,210s,Lili,Ricky
GD8238,PHM,CLASS 3,210s,Bose,Ricky
GD8239,FUSION,CLASS 3,220s,Gaddam,Edmund
GD8240,TROGY,CLASS 2,220s,Kent,John
GD8243,FUSION,CLASS 2,210s,Nikki,John

import pandas as pd

df = pd.read_csv("sample.csv")
print(df)

groups = df.groupby(["Owning", "Type"])
for n, g in groups:
    recipient = ", ".join(g["Owner"].to_list())
    subject = f'Highlight For the Day {n[0]}, {n[1]}'

    print(f"Title: {subject}")
    print(f"Recipient: {recipient}")

Title: Highlight For the Day FUSION, 210s
Recipient: Nikki
Title: Highlight For the Day FUSION, 220s
Recipient: Gaddam
Title: Highlight For the Day PHM, 210s
Recipient: Bose
Title: Highlight For the Day TROGY, 210s
Recipient: Kent, Lili
Title: Highlight For the Day TROGY, 220s
Recipient: Kent

Upvotes: 1

Related Questions