pythonemail

AttributeError: 'list' object has no attribute 'encode'. How do i send Email via Excel? Python


So I'm trying to send an e-mail from Excel with Python. Right now I just have all the e-mails set up in column A1 separated by semicolons. If I put the e-mails into Python directly, they send out but when I use the Python call-out, it freaks out giving me this error. How exactly can I make this work?

import smtplib
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import pandas
from datetime import datetime

server = smtplib.SMTP('relay.xxx.com')
email_df = pandas.read_excel(f"E-mails.xlsx", sheet_name="Emails", nrows=1, usecols = "A")
sender = 'email@email.com'

receivers = 'Email'
receivers = list(email_df)

print(receivers)
msg = MIMEMultipart('related')

msg['Subject'] = 'Test mail'
msg['From'] = sender

with smtplib.SMTP('relay.xxx.com') as server:
    msg['To'] = receivers
    server.sendmail(sender, receivers, bytes(msg.as_string()))
    print(f"Successfully sent email to: {receivers}")

I don't want multiple e-mails to be sent out. I want 1 single e-mail with a bunch of e-mails in the address box.

Alternatively, if there's a way can read it by column, (addr1 in a1, addr in a2) that'd also be better.

Appreciate it.


Solution

  • If you can get your email addresses (from your df) into a list in the desired format, e.g.:

    print(receivers)    
    ['email1@email1.com', 'email2@email2.com', 'email3@email3.com']
    

    then you can add this after its assignment to turn it into a string for send_mail (as it requires a string instead of a list):

    receivers = ", ".join(receivers)
    

    You mention that it would be ideal if you had an email address per row under column A in your spreadsheet. So if you can get your Excel spreadsheet into this format (with a column heading of "Email") then you can do this to get the aforementioned list:

    email_df = pandas.read_excel(f"E-mails.xlsx", sheet_name="Emails", usecols = "A")
    receivers = email_df['Email'].tolist()
    

    So, for completeness (with Excel spreadsheet in its new format of one email address per row under column A), your code would change to:

    import smtplib
    from email.mime.image import MIMEImage
    from email.mime.text import MIMEText
    from email.mime.multipart import MIMEMultipart
    import pandas
    from datetime import datetime
    
    server = smtplib.SMTP('relay.xxx.com')
    
    email_df = pandas.read_excel(f"E-mails.xlsx", sheet_name="Emails")
    receivers = email_df['Email'].tolist()
    
    print(receivers)  # Check list is one valid email address per element
    
    receivers = ", ".join(receivers)
    
    sender = 'email@email.com'
    msg = MIMEMultipart('related')
    
    msg['Subject'] = 'Test mail'
    msg['From'] = sender
    
    with smtplib.SMTP('relay.xxx.com') as server:
        msg['To'] = receivers
        server.sendmail(sender, receivers, bytes(msg.as_string(), encoding='utf-8'))
        print(f"Successfully sent email to: {receivers}")