Send newsletters all at once with Gmail

Motivation

I decided to post the story on the radio in earnest. It is essential to post a lot to increase the number of hires, and you have to send a lot. Meanwhile, I thought that the email was annoying. Basically, the material is posted by email. One story for each copy. 10 emails to send 10 stories ... So, I decided to make something that would send all the news items all at once.

Preparation

1. Activate the API of "Google Sheet" and "Google Drive" of the account to send automatically, and get the JSON file
2. Rename the JSON file.
3. Create a file to write the code.
4. Store the json files in the same folder

Spreadsheet

I used two sheets, and the sheet names were "Index" and "Main". The contents are as follows. -Index: Count email address and password, destination, sender, radio name, address, name -Main: Subject, material, creation date, transmission date, status, result

Index Main

Library installation

$ pip install gspread oauth2client

code

send.py

#Email sending relations
from email.mime.text import MIMEText
from email.utils import formatdate
import smtplib
#API cooperation relationship
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime
from pprint import pprint 

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)

client = gspread.authorize(creds)

SPREADSHEET_KEY = '******************************************'
sheet1= client.open_by_key(SPREADSHEET_KEY).worksheet('Index')
sheet2= client.open_by_key(SPREADSHEET_KEY).worksheet('Main')

# Index
_id        = sheet1.cell(2,1).value
_pass      = sheet1.cell(2,2).value
_from      = sheet1.cell(4,1).value
_to        = sheet1.cell(4,2).value
_radioName = sheet1.cell(4,3).value
_name      = sheet1.cell(6,1).value
_address   = sheet1.cell(8,1).value

#All values are assigned to a variable called data.
data = sheet2.get_all_records()

#Get the number of data
last_number = len(data)

# pprint(data)

for row in range(last_number):
    #Transmission judgment
    status = data[row]["result"]
    if status == "Done":
        continue
    #Extract the information required to send an email
    body = data[row]["Neta"]
    radioName = "radio name:" + _radioName
    msg = MIMEText(body + "\n\n" + radioName + "\n\n" + _address + "\n\n" + _name)
    #sheet update
    now = datetime.datetime.now()
    sheet2.update_cell(row + 2, 5 , now.strftime("%Y/%m/%d %H:%M:%S.%f"))
    sheet2.update_cell(row + 2, 6, "sent")
    sheet2.update_cell(row + 2, 7, "Done")

    msg['Subject'] = data[row]["subject"]
    msg['From'] = _from
    msg['To'] = _to
    msg['Date'] = formatdate()

    # pprint(msg)

    smtp = smtplib.SMTP('smtp.gmail.com', 587)
    smtp.ehlo()
    smtp.starttls()
    smtp.ehlo()
    smtp.login(_id, _pass)
    smtp.send_message(msg)
    smtp.close()

Supplement

In Python2 series, the following error appears.

python

Traceback (most recent call last):
  File "send.py", line 6, in <module>
    import gspread
ModuleNotFoundError: No module named 'gspread'

All I have to do is install the 3rd system, but I haven't studied Python enough and I've been stuck for several hours.

reference

https://qiita.com/njn0te/items/4318347f8c4ed5137476