メール送信業務のDXをしたいと相談を受けてやってあげたこと

はじめに

アンケートに回答してくれた人へメールを送信する業務が大変過ぎるのでなんとかしてもらいたいという相談があったのでお手伝いしてみた

要件整理

• メールアドレスと氏名が書かれたExcelファイルを作成している
• 本文は同じだけれど、メールの冒頭に〇〇様と入力する
• 現在はコピペで作業をしている
• Excelファイルに書かれているメールアドレスに氏名を変更したメールを簡単に送りたい
• OSはWindows

実行環境

OSがWindowsとのことなので、色々なものをインストールしてもらうのが大変そうだったのでPowerShellを採用

ファイル構成

• 本文用テキストファイル（emailBody.txt）
• メール送信情報テキストファイル（smtpSettings.txt）
• 送信先一覧Excelファイル（mailList.xlsx）
• 送信スクリプト（mailsender.ps1）

本文用テキストファイル

以下のように作成し、スクリプトで{Name}を書き換える

{Name}様

ここに本文を記入して下さい。

株式会社〇〇
担当：△△

メール送信情報テキストファイル

メール送信のために必要な情報を以下のように記述

smtpServer=[メールサーバーアドレス]
smtpUser=[メール送信ユーザー]
smtpPassword=[メール送信ユーザーのパスワード]

smtpFrom=[送信元メールアドレス]
smtpSubject=[メールタイトル]

送信先一覧Excelファイル

以下のような構成でファイルを作成
ただし、ヘッダーは入力しない

メールアドレス	氏名
[e-mail]	□□□□
[e-mail]	◇◇◇◇

送信スクリプト

以下の通り作成

# メール送信スクリプト

write-host "********************"
write-host "メール送信スクリプト"
write-host "********************"

# スクリプトのフルパスを取得し、スクリプトのディレクトリに移動
$scriptPath = $MyInvocation.MyCommand.Definition
$scriptDirectory = Split-Path $scriptPath
Set-Location -Path $scriptDirectory

# Excelファイルと本文テンプレートファイル
$excelFilePath = $scriptDirectory + "\mailList.xlsx"
$bodyTemplatePath = $scriptDirectory + "\emailBody.txt"

# SMTP設定ファイルの相対パス
$settingsFile = $scriptDirectory + "\smtpSettings.txt"

# Excelファイルを開く
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $true

$workbook = $excel.Workbooks.Open($excelFilePath)
$worksheet = $workbook.Sheets.Item(1)

# SMTP設定を読み込む
$settings = @{}
Get-Content $settingsFile | ForEach-Object {
    $key, $value = $_.Split('=')
    $settings[$key] = $value
}

# 送信者メールアドレス
$smtpFrom = $settings["smtpFrom"]

# SMTP認証情報
$smtpServer = $settings["smtpServer"]
$smtpUser = $settings["smtpUser"]
$smtpPassword = $settings["smtpPassword"]

# メールの件名
$smtpSubject = $settings["smtpSubject"]

# メールの本文テンプレートを読み込む
$bodyTemplate = Get-Content $bodyTemplatePath -Raw

write-host "*** 設定読込完了 ***"

# メールアドレスのリストを取得してメールを送信
$row = 1
while ($worksheet.Cells.Item($row, 1).Value2 -ne $null) {
    
    write-host ""
    write-host $row, "件目"
    
    $smtpTo = $worksheet.Cells.Item($row, 1).Value2
    write-host "E-Mail :" $smtpTo

    $recipientName = $worksheet.Cells.Item($row, 2).Value2

    # プレースホルダーを実際の値で置き換える
    $smtpBody = $bodyTemplate -replace '{Name}', $recipientName

    $mail = New-Object System.Net.Mail.MailMessage
    $mail.From = $smtpFrom
    $mail.To.Add($smtpTo)
    $mail.Subject = $smtpSubject
    $mail.Body = $smtpBody

    $smtp = New-Object System.Net.Mail.SmtpClient($smtpServer, 587)
    $smtp.EnableSsl = $true
    $smtp.Credentials = New-Object System.Net.NetworkCredential($smtpUser, $smtpPassword)
    $smtp.Send($mail)

    $row++
}

write-host ""
write-host "*** 送信完了 ***"

# リソースの解放
$workbook.Close()
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

実行

Windows Powershellを起動して、以下のコマンドで実行したら無事に送信成功！

> mailsende.ps1

失敗と対策

「これはどうやって使えばいいですか？」という質問があり、説明したら「難しくてできません」とのこと...

以下のバッチファイルを作成して、ダブルクリックで送信できるように対応

powershell ./mailsender.ps1

pause

できた！