[PYTHON] Send newsletters all at once with Gmail


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.


  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


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 スクリーンショット 2020-09-06 17.17.54.png Main スクリーンショット 2020-09-06 17.16.34.png

Library installation

$ pip install gspread oauth2client



#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":
    #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.login(_id, _pass)


In Python2 series, the following error appears.


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.



Recommended Posts

Send newsletters all at once with Gmail
Replace all at once with sed
Upgrade all at once including dependencies with pip
Send using Python with Gmail
Easily send emails with Gmail with Django
Send email via gmail with Python 3.4.3.
Convert memo at once with Python 2to3
Delete all unnecessary Gmail emails with API
How to save all Instagram photos at once
Erase image files at once with one liner
Send Gmail at the end of the process [Python]
Update multiple tables at once with pandas to_sql
Convert multiple proto files at once with python
Get corporate number at once via gbizinfo with python
Limits that can be analyzed at once with MeCab
Send email with Django
Send email with Python
Send Gmail in Python
[Python] Send an email from gmail with two-step verification set
Download Wikipedia flag images all at once [Python] [Beautiful Soup]
Create multiple users with serial numbers at once with Ansible Playbook