IT女子がお届けするオフィスワーク効率化・VBA技術紹介

ノンプログラマーによるノンプログラマーのためのやさしい解説付き

スポンサーリンク

【VBAでOutlook操作】Excelシートのデータからメールを一括作成する②

f:id:excel-accounting:20180629204252p:plain:w400

前回の記事では送信相手によってメールの文面を変えて下書きを一括作成するマクロを紹介しました。

www.excel-prog.com


今回の記事では、下書き作成&添付ファイルの付与までしちゃいますよ~

(連載予定ではなかったのですが、仕事で急遽作ったので連載にしちゃいます)

この記事で紹介すること

・エクセルシートに用意したデータ
・任意のフォルダに用意したExcelファイル

を用いて、Outlookのメールアイテムを一括作成します。

f:id:excel-accounting:20180723201007p:plain:w450

↓下書きアイテムに添付する

f:id:excel-accounting:20180723201129p:plain:w450

作業手順の解説

※この記事はVBAの基本操作を習得している方向けに書いているので、全般的な解説は省略しています。

エクセルシートにデータを用意

前回作成したフォーマットに、添付ファイルの情報を追加します。
※ご自身の業務要件に応じてフォーマットは調整してください。

f:id:excel-accounting:20180723201628p:plain

  • F列:添付ファイルのキーワードを追加
  • J列:添付ファイルを格納しているパスを追加

J列のパスに格納されているファイルの中から、

・佐藤さん宛てのメールには、ファイル名に”佐藤”が含まれるファイルを添付する

・鈴木さん宛てのメールには、ファイル名に”鈴木”が含まれるファイルを添付する

  :
  :

という仕組みです。

今回のケースだとC列の値をそのままキーワードとして使うことも可能ですが、

キーワードを自由に設定できるように新たに1列用意しています。

汎用性ってやつですね。


ちなみに、前回紹介したフォーマットでF~Hの3列を余分に持たせてたのに気がつきましたか?!

f:id:excel-accounting:20180723203008p:plain


私がよくやる工夫の紹介です。

もし、初回設計時に余分なく列を詰めて件名や本文を設定していた場合、機能追加で列を追加すると何も手を加えていない部分もコード修正が必要になりますよね。

(なんかこのツール、行や列が増えそうだなー)

って予感がするときは、最初に少し空白行や空白列を用意しておくと良いですよ。

ソースコード

参照設定「Microsoft Outlook XX.0 Object Library」を使用しています。

Enum col '1以降の数値を省略した場合は+1される
    宛先 = 1
    複写
    氏名
    使用日
    金額
    添付キーワード
End Enum

Sub Outlookメール一括作成2()

    Dim OutlookObj As Outlook.Application 'Outlookオブジェクトの作成
    Set OutlookObj = CreateObject("Outlook.Application")
    
    Dim r As Long
    For r = 2 To Cells(1, 1).End(xlDown).Row '対象人数分の処理を繰り返す
    
        Dim mailItemObj As Outlook.MailItem 'メールアイテムオブジェクト作成
        Set mailItemObj = OutlookObj.CreateItem(olMailItem)

        Dim mailBody As String
        mailBody = CreateMailBody(r) 'メール本文作成
        
        With mailItemObj 'メールアイテム作成
            .To = Cells(r, col.宛先).Value 'Toを設定
            .CC = Cells(r, col.複写).Value 'CCを設定
            .Subject = Cells(1, "J").Value '件名を設定
            .Body = mailBody '本文を設定
        End With
        
        Dim attachObj As Outlook.Attachments '添付ファイルオブジェクト
        Set attachObj = mailItemObj.Attachments
        
        Dim KeyWord As String
        KeyWord = Cells(r, col.添付キーワード)
        
        Dim attachedPath As String, attachedFile As String
        attachedPath = Range("J16").Value
        attachedFile = SearchFile(attachedPath, KeyWord)
        
        Dim attached As String
        attached = attachedPath & "\" & attachedFile
        
        'ファイルが見つからない場合は付与しない
        If attachedFile <> "" Then attachObj.Add attached
        
        mailItemObj.Display '下書きを表示
        
        '次のメールアイテムを作成するためいったん破棄
        Set mailItemObj = Nothing
        Set attachObj = Nothing
    
    Next r

End Sub

Function CreateMailBody(r As Long) As String
' 機能:Excelシート上の指定行番号のメール本文を作成する

    Dim sName As String, DayOfUse As String, price As Long
    sName = Cells(r, col.氏名).Value
    DayOfUse = Cells(r, col.使用日).Value
    price = Cells(r, col.金額).Value
    
    Dim sign As String '署名
    sign = Cells(12, "J").Value
    
    Dim mBody As String 'メール本文
    mBody = Cells(2, "J").Value '初期値を設定
    mBody = Replace(mBody, "(氏名)", sName)
    mBody = Replace(mBody, "(使用日)", DayOfUse)
    mBody = Replace(mBody, "(金額)", price)
    mBody = mBody & vbCrLf & vbCrLf & sign '末尾に署名を付与
    
    CreateMailBody = mBody

End Function

Function SearchFile(attachedPath As String, KeyWord As String) As String
'指定パス配下に指定キーワードが含まれるファイルを検索し、ファイル名称を返す

    Dim fName As String
    fName = Dir(attachedPath & "\" & "*.xls*")
    
    Do While fName <> ""
        
        If InStr(fName, KeyWord) > 0 Then
            SearchFile = fName
            Exit Do
        End If
        
        fName = Dir()
    
    Loop

End Function

前回から追加した点

①Functionプロシージャの追加

SearchFileというFunctionを作成しました。

指定のパス配下のExcelファイルを順番に検索し、指定のキーワード("佐藤"や"鈴木"など)を含むファイルが見つかった場合、ファイルの正式名称を返します。

②添付ファイルを付与する処理の追加
メインのSubプロシージャの後半部分に、ファイルを添付する処理を追加しました。

各メールに添付するべきExcelファイル名をSearchFileで特定し、メールアイテムに付与します。

マクロの使い方

前回記事と同じです。

事前にOutlookを起動しておいてから、マクロを実行してください。

※「送信処理」は人間の作業です。
マクロで作成されたメールの下書きを確認し、問題なければ送信ボタンを押します。

送信処理もマクロで行いたい場合

これも前回記事と同じです。

あまりオススメはしませんが、一気に送信したい方はどうぞ!
※事前に必ずテストして自己責任でお願いします

mailItemObj.Display '下書きを表示
 メソッドを書き換えます。
mailItemObj.Send '送信

これでマクロを実行すると、メール送信まで行われます。
送信したメールは、Outlookの[送信済みアイテム]に入ります。


最近はセキュリティだのなんだのって厳しい時代ですよね。
ファイルを誤送信しないよう、くれぐれも慎重にお使いください。


以上です!

メールの下書き作成からファイルの添付まで、めんどうくさいことは全部自動化しちゃいましょう!

f:id:excel-accounting:20180723224358p:plain:w200


エクセルデータから1件ずつコピペでメールを作成している方、ぜひぜひ使ってみてくださいね。

スポンサーリンク