ExcelVBAでOutlookメールを送る4

前回まででExcel　VBAでOutlookメールを送る方法を勉強しました。

ここまではPower Automate Desktopでも可能なのですがExcelVBAを使うメリットも紹介したいと思います。

今回はVBAを用いて本文の内容を置換する方法を紹介します。

■今回の内容
■関数とコード
■コードの説明
■ここでのポイント

■今回の内容

・先月の情報を別セルに用意する

・[先月]のところをyyyy年mm月に置換する

■関数とコード

・先月を表示する関数

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

E1セルに対し今日の日付を表すToday()を利用して年月日を表すDate関数にてひと月引いた年月日を表示させます。なお表示形式はyyyy年m月としておきます。

・[先月]の情報を取得し、文字を置換する

'先月の取得
Dim lastmonth As String
lastmonth = wb.Worksheets("Sheet1").Range("E1").Value
 
' 変数の変換
  subjectStr = Replace(subjectStr, "[先月]", lastmonth)
  bodyStr = Replace(bodyStr, "[先月]", lastmonth)

■コードの説明

Dim lastmonth As String
lastmonth = wb.Worksheets("Sheet1").Range("E1").Value

先月という変数を定義し、E1セルの情報を取得します。

'変数の変換
 subjectStr = Replace(subjectStr, "[先月]", lastmonth)
 bodyStr = Replace(bodyStr, "[先月]", lastmonth)

Excel VBAのReplace関数は文字列を置換する便利な関数です。Replace(対象文字列,検索文字,置換文字）で任意の文字を変更できます。

ここでは設定した変数(subjectStr, bodyStr)の中の[先月]という文字をlastmonthに変換し、再度subjectStr,bodyStrに代入しています。

変数については変数にさらに操作を加えて同じ変数を定義させることも可能です。Power Automateでもできると思いますがExcelは多くの関数を持っておりVBAでも使用が可能なのでこういった操作も簡単にできます。

最終的なメールは以下のようになります。メールのタイトルにもしっかりと年月が入るようになりました。

■ここでのポイント

今回のコードをまとめると以下になります。Replace関数による置換の操作を利用すれば他の項目も自由に変更可能なので使ってみてください。

Sub 報告メール()
 
'Excelファイルを開く
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\Public\報告メール.xlsx")
 
'変数を定義する
Dim toStr As String 'To
Dim ccStr As String 'Cc
Dim bccStr As String 'bcc
Dim subjectStr As String '件名
Dim bodyStr As String '本文
Dim attach As String '添付ファイル
 
'Excelワークシートから値を読み取る
toStr = wb.Worksheets("Sheet1").Range("B2").Value
ccStr = wb.Worksheets("Sheet1").Range("B3").Value
bccStr = wb.Worksheets("Sheet1").Range("B4").Value
subjectStr = wb.Worksheets("Sheet1").Range("B5").Value
bodyStr = wb.Worksheets("Sheet1").Range("B6").Value
attach = wb.Worksheets("Sheet1").Range("B7").Value
 
'先月の取得
Dim lastmonth As String
lastmonth = wb.Worksheets("Sheet1").Range("E1").Value
 
' 変数の変換
  subjectStr = Replace(subjectStr, "[先月]", lastmonth)
  bodyStr = Replace(bodyStr, "[先月]", lastmonth)
 
'Outlookを起動してインスタンスを作成する
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
 
'新規メールのオブジェクトを取得する'
Dim objMail As Object
Set objMail = objOutlook.CreateItem(0)
                 
'オブジェクトの各プロパティにExcelワークシートから読み取った値を代入する
objMail.To = toStr
objMail.CC = ccStr
objMail.BCC = bccStr
objMail.Subject = subjectStr
objMail.Body = bodyStr
objMail.attachments.Add (attach)
 
 
'メールを表示させる
objMail.Display
 
'Outlookのオブジェクトの開放
Set objMail = Nothing
Set objOutlook = Nothing
   
End Sub