[PM扣頂]來寫個自動產生email工具吧

PM Steven

8 min readJul 31, 2023

最近又開始亂點技能樹，趁工作空檔開始把一些Routine任務寫成自動化工具後分享給同事用，意外的收到好評，所以決定分享寫成文章

Photo by ThisisEngineering RAEng on Unsplash

日常工作總有些Routine 發郵件的任務，不得不去做但因此會占用其他更重要的任務時間。這種低生產力卻一定得做的工作要是有個工具可以一鍵生成郵件就能大幅提升工作效率。

今天來教大家簡單寫幾段code就能做出自動產生信件的工具

假設有以下情境:

每天部門周會結束後需要發信夾帶會議紀錄給大家參考，收件人、CC、信件標題、信件內容大致固定，但現在每次發信都要參考之前發的信件去製作當天的信件

針對這個情境我們可以整理出以下幾點需求:
1.週期性的重複事務需要減少耗時
2.能夠不參考以前發的郵件就產生出帶有完整資訊內容(收件人、CC、信件標題、信件內容、附件)的信件
3.信件內容和主旨能夠根據當天日期自動帶入
4.信件要能選擇夾帶附檔

解決方案:

利用Office 程式間互通，編寫工具自動產生信件並寄出

工具設計:

利用EXCEL儲存信件欄位資訊，一鍵產出要寄送的信件

使用工具:

VBA (Visual Basic for Applications)

什麼是VBA?
一種Windows下的巨集程式語言，語法承襲Visual Basic，可直接在office 軟體裡使用，不用另外安裝。

第一步:開啟開發者環境

開啟EXCEL→檔案→選項→自訂功能區→主要索引標籤→勾選開發人員

回到檔案時就會發現多了開發人員選項

點擊後選擇Visual Basic就會進入編輯頁面了

第二步:設定引用項目讓EXCEL和Outlook溝通

為了讓EXCEL和Outlook溝通，我們得設定Outlook引用項目，類似呼叫函式庫的概念

Visual Basic裡點選工具→設定引用項目→找到並勾選Microsoft Outlook XX.0 Object Library (名稱會因為office 版本不同)

第三步:設定信件內容

我們在EXCEL 填入信件需要的內容，從A1欄位開始寫標題依序為:主旨、To、cc、附件、日期

然後從A2依序填入信件內容

如果要自動產生當天的日期，在日期那欄使用以下TEXT函式:

=TEXT(TODAY(),"yyyy-mm-dd")

若是信件標題要帶入當天日期，在A1插入這段TEXT函式

& TEXT(TODAY(),"yyyy-mm-dd"

日期格式可以自行更改，像是"mm-dd"或"mm/dd/yy"等等

第四步:製作範例信件

為了能夠自動產生出信件，我們需要先製作Outlook範例檔

開啟Outlook 新增電子郵件，寫好固定的信件內容後，點選檔案，另存新檔，檔案選擇Outlook範本

第五步:編寫程式

我們來到Visual Basic頁面在左邊欄位選擇工作表1→點選模組→插入模組

程式部份我們分為兩個部分:選擇附檔跟產生郵件，我們需要附件路徑才能讓程式自動取得附件，之前已經在EXCEL 設定一欄儲存附件資訊

第五-1步:選擇附檔

在程式編輯頁面輸入以下程式

Sub Getfilepath()
FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
'開啟檔案選取視窗取得附件檔案路徑
If FilePath <> "False" Then
'辨識是否有選擇檔案
Cells(2, 4) = FilePath
'將路徑存到指定EXCEL 表格
MsgBox (FilePath)
'顯示路徑
Else
MsgBox ("請選擇檔案")
'若沒有選擇檔案跳出提示
End If
End Sub

VBA 的程式是由Sub 和 End Sub 包起來組成

這裡我們用到GetOpenFilename去取得檔案路徑，並設定只顯示.xlsx檔案供選擇

把選取到的檔案路徑存在FilePath 裡，再輸出到EXCEL 欄位 D2(2,4)

第五-2步:產生信件

這裡我們要先選擇Outlook範本後，再將EXCEL資料按照欄位輸出到編寫的新信件裡

Sub Automail()
Dim SendMail As Object
'定義SendMail為物件
Set SendMail = CreateObject("Outlook.Application")
'宣告SendMail為Outlook.Application型的物件
Dim TemFilePath As String
'定義附件路徑為字串
TemFilePath = Application.GetOpenFilename("Outlook Template Files (*.oft), *.oft")
'開啟舊檔視窗選擇Outlook範本檔案
Dim NewMail As Object
'定義NewMail為物件
Set NewMail = SendMail.CreateItemFromTemplate(TemFilePath)
'將新增電子郵件儲存到NewMail物件
With NewMail
    .Subject = ThisWorkbook.Sheets(1).Cells(2, 1).Value
  '輸出主旨內容
    .To = ThisWorkbook.Sheets(1).Cells(2, 2).Value
  '輸出寄件名單
  .CC = Cells(2, 3).Value
  '輸出副本名單
    .Attachments.Add ThisWorkbook.Sheets(1).Cells(2, 3).Value
  '輸出附檔位置
  .HTMLBody = Replace(.HTMLBody, "Date", Cells(2, 5).Value)
  '更改信件內容中的日期為今天
    .Display
  '顯示信件
End With
Set NewMail = Nothing
Set SendMail = Nothing
'釋放物件空間
End Sub