【Google Colab】Googleスプレッドシートを使ってメール送信する

2023年2月28日 21:57

宛先・件名・メッセージが少しずつ違うメールを一々手で編集するのが面倒なのでスプレッドシートの内容を順次送信するノートブックを作ってみました。

サンプル

本体（ノートブック）

詳細は後述の解説参照

スプレッドシート1（メールサーバ情報）

シート1の1行目にserver_hostname、server_port、sever_account、sever_password列を、2行目にサーバ名、ポート、アカウント名、パスワードを記述します。
必ずしもA〜D列に順に書く必要はありません。

スプレッドシート2（メール内容）

シートの1行目にto_text、cc_text、subject、message列を、2行目以降に1行ずつ宛先、CC、件名、本文を記述します。
宛先、CCは改行して複数記述できます。
列の順番を変えてもいいし、文字列操作用に他の列を追加しても大丈夫です。

解説

スプレッドシート定義

### メール設定
# URL
setting_url = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/"
# シート
setting_sheet = "シート1"

### メール内容
# URL
contents_url = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/"
# シート
contents_sheet = "シート1"

URLはedit〜やshare〜以降を省いたものになります。
シート名はスプレッドシートと合っていれば変更可能です。
2つのシートを1つのスプレッドシートにまとめる場合はURLは1つにできます。

インポート

# スプレッドシート関連をimport
from google.colab import auth
auth.authenticate_user()
import gspread
from google.auth import default
# メール関連をimport
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import  MIMEMultipart

ただのインポート文なので特に変更する点はありません。

メールサーバ設定

### メールサーバ設定
# スプレッドシートを読み込む
credential1, _ = default()
gc1 = gspread.authorize(credential1)
spread_sheet１ = gc1.open_by_url(setting_url)

# 認証情報を'認証情報'シートから取得する
sheet1 = spread_sheet１.worksheet(setting_sheet)
certification_dictonary = sheet1.get_all_records()

server_hostname = certification_dictonary[0]['server_hostname']
server_port = certification_dictonary[0]['server_port']
sever_account = certification_dictonary[0]['sever_account']
sever_password = certification_dictonary[0]['sever_password']

スプレッドシート1の内容を辞書形式で読み込みます。

ログイン処理

# メールサーバにログイン
server = smtplib.SMTP_SSL(server_hostname, server_port)
server.login(sever_account, sever_password)

前ブロックで取得した情報でログインします。
SMTP_SSL()はSSLを使用する場合なので、メールサーバのプロトコルに合わせてメソッドを変更する必要があります。

メール内容読み込み

### メール内容読み込み
# スプレッドシートを読み込む
credential2, _ = default()
gc2 = gspread.authorize(credential2)
## スプレッドシートを開く（シートURLから）

spread_sheet2 = gc2.open_by_url(contents_url)
sheet2 = spread_sheet2.worksheet(contents_sheet)

# メール内容をシートから取得する
mail_contents_list = sheet2.get_all_records()

スプレッドシート2からメール内容をリスト形式で取得します。
スプレッドシートを1つにまとめる場合はgc2のあたりを修正します。

メール送信

# メール内容を回してメール送信する
for r in mail_contents_list:
  mail_to = r['to_text'].split('\n')
  mail_cc = r['cc_text'].split('\n')
  mail_subject = r['subject']
  mail_message = r['message']

  message = MIMEText(mail_message, 'plain')
  message['subject'] = mail_subject
  message['to'] = ",".join(mail_to)
  message['from'] = sever_account
  message['cc'] = ",".join(mail_cc)
  # message['bcc'] = sever_account

  server.send_message(message)

前ブロックで取得したリストをfor文で回して順次メールを送信していきまかす。
改行で分割してから結合していますが、見返すとこれは文字列を置換すれば1行で澄みますね。
あるいは最初からスプレッドシートの方で改行ではなくカンマ区切りでアドレスを記述することでも単純化できます。

コメントアウト箇所はBCCに自分のアドレスを設定をしていますが、スプレッドシート2にBCC用の列を設定してTOやCCと同じ処理にすればBCCも変更可能になります。

まとめ

適当にColabでスプレッドシートを読み込むコードとPythonのメール送信のコードを探すだけで簡単に作ることができました。
昔は定型文を送るだけでもレンタルサーバを借りてPHPスクリプトを置いて・・・みたいにもっと面倒だったので、今は相当楽な世になったな〜と実感しました。■