Send emails according to the list from Google Spreadsheet with SMTP and put them into 'Sent' folder with IMAP.

send-invites.py

import imaplib
import time
from email.mime.text import MIMEText
from email.utils import formataddr
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
import json
import pandas as pd


def send_invitation(toaddr, toname, subj, text, do_not_send=False):
    # Prepare the message

    new_message = MIMEText(text, 'plain', 'utf-8')
    new_message['Subject'] = subj
    new_message['From'] = from_addr
    new_message['To'] = formataddr((toname, toaddr))

    if not do_not_send:
        # Send message with SMTP
        with smtplib.SMTP(host) as smtp:
            smtp.login(login, passwd)
            smtp.sendmail(new_message['From'], [toaddr], new_message.as_string())

    # Put message to outgoing folder via IMAP

    with imaplib.IMAP4(host) as imap:
        imap.login(login, passwd)
        res, data = imap.append('Sent', '', imaplib.Time2Internaldate(time.time()), new_message.as_bytes())
    
login = "login"
passwd = r"password"
imaphost = 'somehost'
smtphost = imaphost
from_addr = "[email protected]"
spreadsheet_url = "https://..."

# login to google

json_key = json.load(open('google_api_key.json'))
# you can obtain this json file from Google
# your spreadsheet should be shared with email in this json file

scope = ['https://spreadsheets.google.com/feeds']

credentials = SignedJwtAssertionCredentials(json_key['client_email'], bytes(json_key['private_key'],'UTF-8'), scope)

# read the spreadsheets
gc = gspread.authorize(credentials)
sht=gc.open_by_url(spreadsheet_url)

# get the first worksheet
table = sht.worksheets()[0]

# download all data
data = table.get_all_values()

# convert it to dataframe
df = pd.DataFrame(data[1:], columns=data[0])

# select people to send emails
recps = df[df['Invitation status'] == 'invite_now']

msg_tpl = "Dear {{FIRSTNAME}},\n\nWe are happy to invite you to participate in our conference.\n"
subj = "Invitation"

for i, row in recps.iterrows():
    first_name = row['First name']
    last_name = row['Last name']
    email = row['E-mail']
    msg = msg_tpl.replace('{{LASTNAME}}', last_name)
    full_name = first_name + " " + last_name
    send_invitation(email, full_name, subj, msg)
    print("Invitation sent to " + full_name)