Python/Smtp - sending multiple emails

Question

Hello, I am developing an email sending automation for a billing department that consists of checking a spreadsheet for customers who have not made a payment and sending an automatic email to those who have not made it. The code itself works, but it only sends emails to one person on the list and not all. Follow the code below:

import openpyxl, smtplib, sys
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

#Abre a planilha e obtém o status do último pagamento.

wb = openpyxl.load_workbook('C:/temp/cobranca.xlsx')
sheet = wb['Sheet1']

lastCol = sheet.max_column
#latestMonth = sheet.cell(row=1, column=lastCol).value

#Verifica o status de pagamento de cada cliente.

unpaidMembers = {}
for r in range(2, sheet.max_row + 1):
  for c in range(3, lastCol + 1):
    payment = sheet.cell(row=r, column=c).value
    if payment != 'ok':
        cliente = sheet.cell(row=r, column=1).value
        email = sheet.cell(row=r, column=2).value
        mes = sheet.cell(row=1, column=c).value
        unpaidMembers[cliente] = email
        print('Linha:',r,'Coluna:',c,'Cliente:',cliente,'Email:',email,'Mês:',mes)

#Faz login na conta de email.

for cliente, email, in unpaidMembers.items():
    body = "cliente: %s | mes: %s" % (cliente, mes)
    #print('sending email to %s...' % (email))
    print(body)

# create message object instance
msg = MIMEMultipart()

# setup the parameters of the message
password = "sua_senha"
msg['From'] = "seu_email"
msg['To'] = email
msg['Subject'] = "%s - Honorário em aberto." % (cliente, mes)

# add in the message body
msg.attach(MIMEText(body))

# create server
server = smtplib.SMTP('smtp.gmail.com: 587')

server.starttls()

# Login Credentials for sending the mail
server.login(msg['From'], password)

# send the message via the server.
server.sendmail(msg['From'], msg['To'], msg.as_string())

server.quit()

print("email enviado com sucesso  %s:" % (msg['To']))

Sheet template used: https://prnt.sc/uygcrp

Answer 1

Hello, you need to just correctly sort the instructions inside the loop:

....
# Faz login na conta de email.

# create message object instance
msg = MIMEMultipart()

password = "sua_senha"
msg['From'] = "seu_email"

for cliente, email, in unpaidMembers.items():
    body = f"cliente: {cliente} | mes:{mes}" 
    # print('sending email to %s...' % (email))
    print(body)

    # setup the parameters of the message
    msg['To'] = email
    msg['Subject'] = f"{cliente} - Honorário em aberto {mes}."

    # add in the message body
    msg.attach(MIMEText(body))

    # create server
    server = smtplib.SMTP('smtp.gmail.com: 587')

    server.starttls()

    # Login Credentials for sending the mail
    server.login(msg['From'], password)

    # send the message via the server.
    server.sendmail(msg['From'], msg['To'], msg.as_string())

    server.quit()

As it was before the loop iterations occurred but as the instructions were out of it the values of cliente and mes were only for the last iteration, which is also responsible for the message being sent only once. I added f'string because I find it more elegant to work.