Script does not send an email for each row of an excel spreadsheet

Question

First post and first coding project here.

I'm trying to send an email using Python for each row in an excel spreadsheet. I have 5 rows... a header row and four data rows.

For some reason, only the last row is getting sent. How do I get each row to send?

I have spent hours trying to figure it out, but haven't had any luck. Any help would be appreciated.

Thanks in advance!

import pandas as pd
import smtplib

# From https://medium.com/swlh/automate-sending-emails-with-python-using-a-spreadsheet-e9763b3c9559

'''
Change these to your credentials and name
'''
your_name = "John Smith"
your_email = "XXXXX@gmail.com"
gmail_password = input("Type your password and hit enter:")

# If you are using something other than gmail
# then change the 'smtp.gmail.com' and 465 in the line below

server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
server.ehlo()
server.login(your_email, gmail_password)

# Read the file

email_list = pd.read_excel("20200415-xxxx-Test-Spreadsheet.xlsx")

# Get all the Names, Email Addresses, Subjects and Messages
all_clients = email_list['Client']
all_staff = email_list['Staff']
all_types = email_list['Form Type']
all_due_dates = email_list['Due In X Days']
all_emails = email_list['Email Address']


# Loop through the emails
for idx in range(len(all_emails)):

    # Get each records name, email, subject and message
    client = all_clients[idx]
    staff = all_staff[idx]
    form_type = all_types[idx]
    due_in_x_days = all_due_dates[idx]
    email_address = all_emails[idx]


# Creating the Subject Heading and Message
subject = f"The {form_type} Is Due In {due_in_x_days} Days For {client.upper()}" 
message = f"Hi {staff.title()}, \n\nThe {form_type} is due in {due_in_x_days} days for {client.upper()}.  Please turn it in before the due date. \n\nThanks, \n\nJudy"

# Create the email to send
full_email = ("From: {0} <{1}>\n"
                  "To: {2} <{3}>\n"
                  "Subject: {4}\n\n"
                  "{5}"
                  .format(your_name, your_email, staff, email_address, subject, message))

# In the email field, you can add multiple other emails if you want
# all of them to receive the same text
try:
        server.sendmail(your_email, [email_address], full_email)
        print('Email to {} successfully sent!\n\n'.format(email_address))
except Exception as e:
        print('Email to {} could not be sent :( because {}\n\n'.format(email_address, str(e)))

# Close the smtp server
server.close()

The code to send the email, namely, this line `server.sendmail(your_email, [email_address], full_email)` is outside the for loop the way you've shown in your post. Hence, it is sending an email to the last entry of your excel sheet since `[email_address]` contains just that value. — tidakdiinginkan, Apr 15 '20 at 20:20
Indent all of the code from `subject = f"The ....` to be within the `for` loop and have a go at it. — tidakdiinginkan, Apr 15 '20 at 20:21
As an aside, using `except Exception` like this is a bad idea, see https://stackoverflow.com/questions/54948548/what-is-wrong-with-using-a-bare-except. — AMC, Apr 15 '20 at 20:39
Thank you @CavinDsouza for responding so quickly! I appreciate it. That worked. I'm unable to upvote, otherwise I would... — MercuryThrives, Apr 15 '20 at 21:29

Script does not send an email for each row of an excel spreadsheet

0 Answers0