自動化による効率化：Excelマクロの実行からメール送信、整理まで(python)

最近、Python使って、タスクスケジューラーに以下を設定して自動化しているメモ

Excelマクロ実行してデータ更新　→　毎日、自分のメールに送る　→　メールフォルダ、古い日のメール削除　→　メールゴミ箱もクリーンに。

備忘録で。

自動化による効率化：Excelマクロの実行からメール送信、整理まで

今回は、Excelマクロを実行してその結果をメールで送信し、不要になったデータを整理する一連のプロセスを自動化する方法を紹介します。

概要

コードの内容と説明

① Excelマクロの実行とメール送信の自動化

このプロセスでは、指定したExcelファイル内のマクロを実行し、特定のシートの範囲をスクリーンショットで撮影して、その画像をメールに添付して送信します。最後に、送信済みアイテムからメールを削除し、一時保存した画像ファイルも削除します。

主要なステップと関数:

1. 画像をBase64エンコードする関数 (image_file_to_base64):

   • この関数は、指定された画像ファイルをBase64形式の文字列にエンコードします。メールに直接画像を埋め込むために使用します。

2. Excelマクロを実行する関数 (run_excel_macro):

   • ExcelのApplicationオブジェクトを使用して、指定されたマクロを実行します。

3. シートの指定範囲のスクリーンショットを取得し、赤枠で囲む関数 (screenshot_sheet):

   • 指定されたExcelファイルを開き、マクロを実行した後、特定のシートの指定された範囲をスクリーンショットとしてコピーします。その後、画像に赤枠を追加して強調表示し、指定されたパスにPNG形式で保存します。

4. メール送信:

   • win32com.client ライブラリを使ってOutlook Applicationオブジェクトを取得し、メールアイテムを作成します。宛先、件名、本文（HTML形式で画像を含む）を設定し、メールを送信します。

5. 送信済みアイテムからのメール削除と一時ファイルのクリーンアップ:

   • メール送信後、少し待ってからOutlookの送信済みアイテムフォルダにアクセスし、最新のメールを削除します。また、使用した一時的な画像ファイルも削除します。

コードの実行に必要な注意点:

• このスクリプトを実行するには、win32com.client および PIL (Python Imaging Library) が必要です。これらは、通常、pywin32 と Pillow パッケージをインストールすることで使用可能になります。
• Excelファイルのパス、マクロ名、メールの宛先などは、実際の環境に合わせて適宜変更してください。
• システムのセキュリティポリシーによっては、スクリプトからOutlookやExcelを操作することが制限されている場合があります。その場合は、ポリシーの設定を確認するか、システム管理者に相談してください。

import datetime
import time
import win32com.client as win32
import os
import base64
from PIL import ImageGrab, Image, ImageDraw

def image_file_to_base64(file_path):
    with open(file_path, "rb") as image_file:
        return base64.b64encode(image_file.read()).decode("utf-8")

def run_excel_macro(excel, macro_name):
    excel.Application.Run(macro_name)

def screenshot_sheet(file_path, sheet_name, range_str, image_path, macro_name):
    excel = win32.Dispatch('Excel.Application')
    excel.Visible = False
    workbook = excel.Workbooks.Open(file_path)
    run_excel_macro(excel, macro_name)
    time.sleep(30)  # マクロ実行後、データが更新されるのを待つ
    sheet = workbook.Sheets(sheet_name)
    range = sheet.Range(range_str)
    range.CopyPicture(Appearance=win32.constants.xlScreen, Format=win32.constants.xlBitmap)
    image = ImageGrab.grabclipboard()
    image.save(image_path, 'PNG')
    with Image.open(image_path) as img:
        draw = ImageDraw.Draw(img)
        draw.rectangle([(0, 0), img.size], outline="red", width=4)
        img.save(image_path)
    workbook.Close(SaveChanges=True)
    excel.Quit()

# Excelファイルと保存パス
file_path = r"C:\Users\*****\Desktop\dailyチェック\不良内訳データ.xlsm"
image_path1 = r"C:\Users\*****\Desktop\dailyチェック\画像一時保存\temp_image1.png"
image_path2 = r"C:\Users\*****\Desktop\dailyチェック\画像一時保存\temp_image2.png"
macro_name = "データまとめ_ボタン1_Click"

# 2つのシートからスクリーンショットを取得
screenshot_sheet(file_path, "月&号機", "Y23:AO66", image_path1, macro_name)
screenshot_sheet(file_path, "不良グラフ・月別", "V4:AO21", image_path2, macro_name)

# Outlookの準備
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)

# メールの設定
mail.To = "********@co.jp"
mail.Subject = datetime.datetime.now().strftime("%Y-%m-%d") + " 不良内訳データ"
base64_picture1 = image_file_to_base64(image_path1)
base64_picture2 = image_file_to_base64(image_path2)
mail.HTMLBody = f"<p>お疲れ様です。</p><p>{mail.Subject}の不良内訳データです。</p><img src=\"data:image/png;base64,{base64_picture2}\" /><p>2個目の画像はキズ・汚れです。</p><img src=\"data:image/png;base64,{base64_picture1}\" />"

# メール送信
mail.Send()

# 少し待つ（例：15秒）
time.sleep(15)

# Outlookの送信済アイテムフォルダにアクセスし、最新のメールを削除
sent_items = outlook.GetNamespace("MAPI").GetDefaultFolder(5)
sent_items.Items.GetLast().Delete()

# 一時ファイルの削除
os.remove(image_path1)
os.remove(image_path2)

---

② 古いメールの自動削除

このプロセスは、指定したOutlookフォルダ内の古いメールを自動的に削除することを目的としています。特に、前日よりも古いメールを検索し、自動的に削除します。この自動化により、メールボックスの整理を効率的に行うことができます。

コードの説明

import win32com.client
import datetime

# Outlookのセッションを開始
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

# PSTファイルまたはフォルダの名前
pst_file_name = "ローカルフォルダ1"

# Outlookの全てのフォルダを検索して、指定されたPSTファイルまたはフォルダを見つける
for folder in outlook.Folders:
    if folder.Name == pst_file_name:
        local_folder = folder
        break

# 特定のサブフォルダを取得
# 例: 「97.Python」フォルダ内の「01.加工」フォルダ
pn_folder = local_folder.Folders["97.Python"].Folders["01.加工"]

# 前日の日付を取得
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
yesterday = yesterday.date()

# メールを一つずつチェックして、前日より前のものを削除
messages = pn_folder.Items
for index in range(messages.Count, 0, -1):
    message = messages.Item(index)
    if message.ReceivedTime.date() < yesterday:
        message.Delete()

主要なステップと関数:

1. Outlookセッションの開始:

   • win32com.client ライブラリを使用してOutlookのセッションを開始し、MAPI（Messaging Application Programming Interface）を介してメールアイテムにアクセスします。

2. 指定フォルダ内のメールを検索:

   • 指定されたPSTファイルまたはOutlookのフォルダを検索します。この例では、"ローカルフォルダ1" が対象のフォルダ名です。

3. 古いメールの自動削除:

   • 特定のサブフォルダ（この例では "97.Python" 下の "01.加工"）内のメールアイテムを走査し、前日よりも古いメールを削除します。これにより、不要になった情報の蓄積を防ぎ、メールボックスを整理します。

コードの実行に必要な注意点:

• このスクリプトを実行するには、Outlookがインストールされている環境である必要があります。
• win32com.client ライブラリが必要です。Python環境にインストールされていない場合は、pip install pywin32 を実行してインストールしてください。
• 実際にメールを削除する前に、削除対象のメールが正しいことを確認するために、実際の環境でのテストを慎重に行ってください。
• PSTファイル名やサブフォルダのパスは、実際の環境に合わせて変更する必要があります。

---

③ ローカル内で削除したメールの更なる削除

このスクリプトは、Outlookの「削除済みアイテム」フォルダ内のメールを清掃するために使用されます。これは、メールボックスをさらに整理し、不要なメールを完全に削除することを目的としています。

コード:

import win32com.client

# Outlookのセッションを開始
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

# PSTファイルの名前（この例では「ローカルフォルダ1」）
pst_file_name = "ローカルフォルダ1"

# Outlookの全てのフォルダを検索して、指定されたPSTファイルを見つける
for folder in outlook.Folders:
    if folder.Name == pst_file_name:
        local_folder = folder
        break
else:
    raise Exception("PSTファイルが見つかりませんでした。")

# ローカルフォルダ内の「削除済みアイテム」フォルダを取得
deleted_items = local_folder.Folders["削除済みアイテム"]

# 削除するメールのリストを作成
messages_to_delete = [message for message in deleted_items.Items]

# メールを一つずつ削除
for message in messages_to_delete:
    message.Delete()

説明:

• このスクリプトもOutlookのCOMオブジェクトを使用しています。
• 特定のPSTファイル（ここでは「ローカルフォルダ1」）内の「削除済みアイテム」フォルダにアクセスします。
• そのフォルダ内のすべてのメールをリストアップし、それぞれを削除します。これにより、削除済みアイテムフォルダが空になり、ストレージの使用量が減少します。

---

④ ゴミ箱のクリーンアップ

このスクリプトは、Outlookの「削除済みアイテム」フォルダ内の内容を定期的にクリアし、不要になったメールやファイルを完全に削除するために使用されます。これにより、メールボックスを整理し、ストレージの使用量を最適化することができます。

コード:

import win32com.client

# Outlookのセッションを開始
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

# 削除済みアイテムフォルダを取得
deleted_items = outlook.GetDefaultFolder(3)  # 3は削除済みアイテムのフォルダ番号

# メールを一つずつ削除
messages = deleted_items.Items
count = messages.Count
while count > 0:
    messages.Item(count).Delete()
    count -= 1

説明:

• このスクリプトでは、win32com.client を使用してOutlookのセッションを開始します。
• Outlookの「削除済みアイテム」フォルダにアクセスし、このフォルダ内のすべてのアイテム（メール、フォルダ、その他のオブジェクト）を削除します。
• アイテムは、コレクションの末尾から順に削除していきます。これは、コレクションのインデックスが変更されることを避けるためです。
• この処理により、「削除済みアイテム」フォルダの内容が完全にクリアされ、メールボックスの整理とストレージの最適化が行われます。

--

---

という感じ