Hello!
In one of my previous blog posts, I wrote about how to send personalized mass emails. However, in that blog post, we were taking the list of emails from a csv file. What if you have your list of emails in the PostgreSQL database?
Imagine that you have a schema and under that schema, there is a table that includes the list of emails and the names of the people. You want to send an email to them but in the email message, you want to call them by their names (i.e., personalized content). In this example, the sender email is Gmail but the script would work with any email — it would just require a small modification in the SMTP part (e.g., instead of 465, you will need to write the SSL port number of your email provider).
import smtplib
import ssl
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import psycopg2 as ps
import pandas as pd
import re
message = MIMEMultipart("alternative")
message["Subject"] = "Your email subject"
text = """\
"""
html = """\
<html>
<body>
<br>
Hello!,
<p> {} how are you? </p>
<br>
</body>
</html>
"""
part1 = MIMEText(text, "plain")
part2 = MIMEText(html, "html")
message.attach(part1)
message.attach(part2)
context = ssl.create_default_context()
## connection to the database
host = "....."
database = "...."
user = "....."
password = "....."
conn = ps.connect(host=host, dbname=database, user=user, password=password)
cur = conn.cursor()
sql = 'SELECT * from schemaname."tablename"'
cur.execute(sql)
conn.commit()
sender_email = "your email"
password_email = "your email password"
email_pattern = re.compile("^.+@.+\..+$")
with smtplib.SMTP_SSL("smtp.gmail.com", 465, context=context) as server:
server.login(sender_email, password_email)
data = pd.read_sql(sql, conn)
Mails = data['email'].to_list()
Receivers = data['names'].to_list()
for i in range(0, len(Mails)):
del message['To']
message['To'] = Mails[i]
server.sendmail(sender_email, Mails[i], message.as_string().format(Receivers[i]))
cur.close()
conn.close()
Cheers!
Category: Uncategorized