パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

VBAでメールを自動送信!エクセルマクロでoutlook操作する方法|事例&コード付

f:id:gene320:20170614232819p:plain

・VBAでプログラミングを覚えたからには、メール送信までやってみたい!
・アウトルックでメールで送信することは知っているが、具体的な方法はちゃんと分かっていない…
・Outlookのオフィスソフトやインターネットエクスプローラとエクセルを連携させてみたい!
・メールを自動送信して、仕事をラクに終わらせたい!

そんなことを考えているなら、この記事を読んでみてください。この記事では、

・なかなか得ることができないエクセルVBAでアウトルックを操作する方法
・ExcelVBAでアウトルック連携マクロを自力で書く
・Officeアプリケーションのオブジェクトモデル

こんなとっておきの情報を紹介します。コードもあわせて紹介しますので、ぜひこのまま読みすすめていってください。

そもそもエクセルマクロVBAとは?とギモンに感じているなら、こちらの記事がオススメです。

www.fastclassinfo.com

VBAでアウトルックを操作!メールを自動送信する方法(コード付)

エクセルマクロでメールを送信する事例

イメージをつかんでもらうために、事例を紹介します。まずは「メールを自動送信する」ということを理解していただければ、と思います。


動画デモ : テンプレートを指定して、差し込みで電子メールを送信する 動画再生時間: 01:13 (上の画像をクリックすると動画を再生します)
なお、この記事で「これから紹介するコード」と「動画の内容」は同じではありません。

エクセルマクロでメールを自動送信するメリット/デメリット

エクセルマクロVBAでメールを自動送信することで、さまざまなメリットがあります。同時にデメリットもありますので、いくつか紹介します。

アウトルック操作のメリット

まず、メリットから紹介します。

1.カンタンに始めることができる
2.作業の確認時間が減る
3.仕事のミスが減る

1.カンタンに始めることができる
Excelがインストールされていれば、自ずとOutlookもインストールされているため、新しく開発環境を作る必要がありません。もちろん無料です。

2.作業時間が減る
定型文を自動でメール送信/返信できるようになるので、メールにかける時間がガクンと減らせます。

3.仕事のミスが減る
ボタン一発で、複数人に異なるメールを送ることができるようになります。宛先確認やタイプミスによるメールの未送信、そういったみすを減らせます。

さらに、動画デモで紹介しているような差し込みでのメールが送信できるようになれば、仕事を仕組み化できるようになるので、人に仕事を渡すことができるようになります

アウトルック操作デメリット

何かを学ぶときは、メリットだけではなく、デメリットもあります。

1.コード編集に難しさを感じることがある
2.ウェブ上にコードがあまり出回っていない
3.本でも、アウトルックの有益な情報やコードが少ない

1.コード編集に難しさを感じることがある
VBAでアウトルック操作を学ぶとき、オブジェクトの知識が乏しい場合、コード編集に難しさを感じるでしょう。VBAでエクセルを操作をするときと違って、アウトルック独自のオブジェクトが多数出てきます。これが、混乱する原因になります。

その結果、途中で挫折してしまう…ということがあります。

2.ウェブ上にコードがあまり出回っていない
アウトルック操作は、エクセルVBAの外部連携というカテゴリーです。外部アプリケーションを操作することは、レベルの高い機能ですので、ウェブ上にコードが出回っていません。

もちろん、逆に言えば、これが使えるようになれば、VBAを使いこなす人の中でも、かなりレベルの高い層に仲間入りできます。

3.本でも、アウトルックの有益な情報やコードが少ない
私は、アウトルック操作について、書店に出向いて、本を探した時期があります。しかし、ほとんど本は出回っていません。もちろん、ないわけではないのですが、情報が古かったりして、苦戦を強いられました。

そういう経験もあり、この記事で、アウトルック操作をしたいあなたに情報を届けることにしました。

「アウトルックを操作してメール送信する」マクロの難易度は?

アウトルックでメール送信マクロを学ぶとき、どれくらいで習得できるの?

そんな質問をもらうことがあります。

その答えは、あなたがエクセルマクロVBAをどれくらい知っているかに依ります。

もしあなたが、Excel VBAのキホンを抑えていれば、試行錯誤しながら、なんとかコードを書きあげることができるでしょう。

というのも、アウトルック、powerpointやwordなどのオフィスアプリは、ExcelとVBAの仕様が共通です。大きく異なるのは「扱うオブジェクト」だけ。

ですので、Excelでオブジェクトモデルについて理解できていれば、扱いに苦労はしないでしょう。しかし、あまりにも理解が乏しいと、難しく感じるかもしれません。もし、オブジェクトと聞いて「ピン」と来ない方は、まずはこちらでExcel VBAの基本について学ぶことをオススメします。

アウトルックでメール送信する前に参照設定を変更する

エクセルVBAでoutlookのメールを操作するためのプログラミングに入る前に、準備しておくことがあります。

準備|VBEで外部ライブラリへの参照設定でOutlook型を追加

参照設定とは、機能拡張させること、です。

Microsoft Outlook 15.0 Object Libraryにチェックを入れることで、Outlook操作の設定ができるようになります。バージョンが古い場合、15.0ではないときがあります。もし15.0ではなく、数字が小さい場合は、それにチェックを入れましょう。

設定方法

1.VBEを開いて頂いて、「ツール」→「参照設定」
2.Microsoft Outlook 15.0 Object Libraryのライブラリにチェックを入れて、OKをクリック

詳細はこちらの画像の通りです。

1.VBEを開いて頂いて、「ツール」→「参照設定」

f:id:gene320:20170613213650p:plain

2.Microsoft Outlook 15.0 Object Libraryのライブラリにチェックを入れて、OKをクリック

f:id:gene320:20170614231515p:plain

outlookメールを自動で作成し送信する|サンプルコードを使って解説

それでは、ここからは、VBAでアウトルック操作してメール送信する方法をサンプルコードを合わせて紹介します。

まずは、「アウトルックを起動してメールを送信する」という事例を、コードと合わせて紹介します。具体的には、以下のことをやっていきます

コード1|outlookを起動する
コード2|差出人、本文、署名を取得する
コード3|メールを作成して、差出人、本文、署名を入れ込む
コード4|メール本文を改行して署名を入れる|vbCrLfをつかう
コード5|自動で添付ファイルを付ける
コード6|メールを送信する
コード7|outlookを閉じる(オブジェクトの解放)

エクセルファイルのセルには、以下の画像のセルに値を入れておきます。

f:id:gene320:20170614231849p:plain

サンプルコードのマクロで処理すると、メールが自動生成されます。

f:id:gene320:20170614232130p:plain

以下、全体のコードです。必要なら、このままコピペして使ってください。

Option Explicit
Sub sendmail_sample1() '

'---コード1|outlookを起動する
    Dim toaddress, ccaddress, bccaddress As String  '変数設定:To宛先、cc宛先、bcc宛先
    Dim subject, mailBody, credit As String '変数設定:件名、メール本文、クレジット、添付
    Dim outlookObj As Outlook.Application    'Outlookで使用するオブジェクト生成
    Dim mailItemObj As Outlook.mailItem      'Outlookで使用するオブジェクト生成
    
'---コード2|差出人、本文、署名を取得する---
    toaddress = Range("B2").Value   'To宛先
    ccaddress = Range("B3").Value   'cc宛先
    bccaddress = Range("B4").Value  'bcc宛先
    subject = Range("B5").Value     '件名
    mailBody = Range("B6").Value    'メール本文
    credit = Range("B7").Value      'クレジット

'---コード3|メールを作成して、差出人、本文、署名を入れ込む---
    Set outlookObj = CreateObject("Outlook.Application")
    Set mailItemObj = outlookObj.CreateItem(olMailItem)
    mailItemObj.BodyFormat = 3      'リッチテキストに変更
    mailItemObj.To = toaddress      'to宛先をセット
    mailItemObj.cc = ccaddress      'cc宛先をセット
    mailItemObj.BCC = bccaddress    'bcc宛先をセット
    mailItemObj.subject = subject   '件名をセット
    
'---コード4|メール本文を改行する
    mailItemObj.Body = mailBody & vbCrLf & vbCrLf & credit   'メール本文 改行 改行 クレジット
    
'---コード5|自動で添付ファイルを付ける---
    Dim attached as string
    Dim myattachments As Outlook.Attachments 'Outlookで使用するオブジェクト生成
    Set myattachments = mailItemObj.Attachments
    attached = Range("B9").Value     '添付ファイル
    myattachments.Add attached

'---コード6|メールを送信する---
    'mailItemObj.Save   '下書き保存
    mailItemObj.Display  'メール表示(ここでは誤送信を防ぐために表示だけにして、メール送信はしない)

'---コード7|outlookを閉じる(オブジェクトの解放)---
    Set outlookObj = Nothing
    Set mailItemObj = Nothing

End Sub

それでは、一つずつコードを解説していきます。

コード1|outlookを起動する

    Dim toaddress, ccaddress, bccaddress As String  
    Dim subject, mailBody, credit As String 
    Dim outlookObj As Outlook.Application    
    Dim mailItemObj As Outlook.mailItem       

1行目:変数設定:To宛先、cc宛先、bcc宛先
2行目:変数設定:件名、メール本文、クレジット
3行目:Outlookで使用するオブジェクト生成
4行目:Outlookで使用するオブジェクト生成

コード2|差出人、本文、署名を取得する

    toaddress = Range("B2").Value   
    ccaddress = Range("B3").Value   
    bccaddress = Range("B4").Value  
    subject = Range("B5").Value     
    mailBody = Range("B6").Value    
    credit = Range("B7").Value      

1行目:To宛先 = エクセルのB2セルの値
2行目:cc宛先 = エクセルのB3セルの値
3行目:bcc宛先 = エクセルのB4セルの値
4行目:メールの件名 = エクセルのB5セルの値
5行目:メールの本文 = エクセルのB6セルの値
6行目:メールの署名 = エクセルのB7セルの値

コード3|メールを作成して、差出人、本文、署名を入れ込む

    Set outlookObj = CreateObject("Outlook.Application")
    Set mailItemObj = outlookObj.CreateItem(olMailItem)
    mailItemObj.BodyFormat = 3      'リッチテキストに変更
    mailItemObj.To = toaddress      'to宛先をセット
    mailItemObj.cc = ccaddress      'cc宛先をセット
    mailItemObj.BCC = bccaddress    'bcc宛先をセット
    mailItemObj.subject = subject   '件名をセット

1行目:outlookobjに新しいOutlook.Applicationをセット
2行目:mailItemObjに新しいolMailItemをセット
3行目:メールの本文形式を3に(3はリッチテキスト形式)
4行目:メールのto宛先にtoaddressの値をセット
5行目:メールのcc宛先にccaddressの値をセット
6行目:メールのbcc宛先にbccaddressの値をセット
7行目:メールの件名にsubjectの値をセット

コード4|メール本文を改行して署名を入れる|vbCrLfをつかう

    mailItemObj.Body = mailBody & vbCrLf & vbCrLf & credit   'メール本文 改行 改行 クレジット

1行目:メールの本文にmailBodyの値 + 改行 + 改行 + creditの値をセット

コード5|自動で添付ファイルを付ける

    Dim attached as string
    Dim myattachments As Outlook.Attachments 'Outlookで使用するオブジェクト生成
    Set myattachments = mailItemObj.Attachments
    attached = Range("B9").Value     '添付ファイル
    myattachments.Add attached

1行目:変数設定:attached(添付)
2行目:添付ファイルで使用するオブジェクト生成
3行目:新しいオブジェクト(mailItemObj.Attachments)をセット
4行目:attached = エクセルのB9セルの値のファイルアドレス
5行目:メールに4行目で設定したアドレスで指定されたファイルを添付

コード6|メールを送信する(下書き保存も可能)

    'mailItemObj.Save   '下書き保存
    mailItemObj.Display  'メール表示

1行目:メールの下書きを保存
2行目:作成したメールを表示

ここでは誤送信を防ぐために表示だけにして、メールの自動送信はしません。ですので、送信する場合は、メールを確認して「送信ボタン」を押す必要があります。

もし下書き保存したい場合は、'mailItemObj.Save の「'」を削除します。

コード7|outlookを閉じる(オブジェクトの解放)

    Set outlookObj = Nothing
    Set mailItemObj = Nothing

1行目:outlookObj オブジェクトの解放
2行目:Set mailItemObj オブジェクトの解放

詳しく説明しました。ぜひコピペしてぜひ活用してみてください。

メールをHTML形式にして、フォント変更やハイパーリンクを貼る|サンプルコードを使って解説

このサンプルコードは、アウトルックのメール本文をHTML形式に変更して、フォント変更したり、ハイパーリンクを貼るコードを紹介します。

具体的には、以下のことをやっていきます

コード1|outlookを起動する
コード2|差出人、本文、署名を取得する
コード3|メールを作成して、差出人、本文、署名を入れ込む(HTML形式に変更)
コード4|本文をHTML形式にして、フォント変更やハイパーリンクをセット
コード5|自動で添付ファイルを付ける
コード6|メールを送信する
コード7|outlookを閉じる(オブジェクトの解放)

エクセルファイルのセルには、以下の画像のセルに値を入れておきます。

f:id:gene320:20170614231849p:plain

サンプルコードのマクロを使うと、メールが自動生成されます。

f:id:gene320:20170614232146p:plain

以下、全体のコードです。必要なら、このままコピペして使ってください。

Sub sendmail_sample2() '

'---コード1|outlookを起動する
    Dim toaddress, ccaddress, bccaddress As String  '変数設定:To宛先、cc宛先、bcc宛先
    Dim subject, mailBody, credit As String '変数設定:件名、メール本文、クレジット、添付
    Dim outlookObj As Outlook.Application    'Outlookで使用するオブジェクト生成
    Dim mailItemObj As Outlook.mailItem      'Outlookで使用するオブジェクト生成
    
'---コード2|差出人、本文、署名を取得する---
    toaddress = Range("B2").Value   'To宛先
    ccaddress = Range("B3").Value   'cc宛先
    bccaddress = Range("B4").Value  'bcc宛先
    subject = Range("B5").Value     '件名
    mailBody = Range("B6").Value    'メール本文
    credit = Range("B7").Value      'クレジット

'---コード3|メールを作成して、差出人、本文、署名を入れ込む---
    Set outlookObj = CreateObject("Outlook.Application")
    Set mailItemObj = outlookObj.CreateItem(olMailItem)
    mailItemObj.BodyFormat = 2      'htmlテキストに変更
    mailItemObj.To = toaddress      'to宛先をセット
    mailItemObj.cc = ccaddress      'cc宛先をセット
    mailItemObj.BCC = bccaddress    'bcc宛先をセット
    mailItemObj.subject = subject   '件名をセット
    
'---コード4|本文をHTML形式にして、フォント変更やハイパーリンクをセット
    
    Dim link1, strstyle, url As String
    link1 = Range("B8").Value      'リンク
    url = "<a href=""" & link1 & """>" & link1 & "</a>"
    strstyle = "<face=""MS P明朝"" color=""#FFFFFF""><b>" & mailBody & "</b></font>" & url
    mailItemObj.HTMLBody = strstyle & "<br>" & "<br>" & credit  'メール本文 改行 改行 クレジット

'---コード5|自動で添付ファイルを付ける---
    Dim attached as string
    Dim myattachments As Outlook.Attachments  'Outlookで使用するオブジェクト生成
    Set myattachments = mailItemObj.Attachments
    attached = Range("B9").Value     '添付ファイル
    myattachments.Add attached

'---コード6|メールを送信する---
    'mailItemObj.Save   '下書き保存
    mailItemObj.Display  'メール表示(ここでは誤送信を防ぐために表示だけにして、メール送信はしない)

'---コード7|outlookを閉じる(オブジェクトの解放)---
    Set outlookObj = Nothing
    Set mailItemObj = Nothing

End Sub

それでは、一つずつコードを解説していきます。

コード3とコード4のみ解説します。(コード3とコード4以外は、sample1と同じため)

コード3|メールを作成して、差出人、本文、署名を入れ込む(HTML形式に変更)

    Set outlookObj = CreateObject("Outlook.Application")
    Set mailItemObj = outlookObj.CreateItem(olMailItem)
    mailItemObj.BodyFormat = 2      'htmlテキストに変更
    mailItemObj.To = toaddress      'to宛先をセット
    mailItemObj.cc = ccaddress      'cc宛先をセット
    mailItemObj.BCC = bccaddress    'bcc宛先をセット
    mailItemObj.subject = subject   '件名をセット

3行目:メールの本文形式を2に(2はHTML形式)

3行目以外は、sample1と同じため省略

コード4|本文をHTML形式にして、フォント変更やハイパーリンクをセット

    Dim link1, strstyle, url As String
    link1 = Range("B8").Value      'リンク
    url = "<a href=""" & link1 & """>" & link1 & "</a>"
    strstyle = "<face=""MS P明朝"" color=""#FFFFFF""><b>" & mailBody & "</b></font>" & url
    mailItemObj.HTMLBody = strstyle & "<br>" & "<br>" & credit  'メール本文 改行 改行 クレジット

1行目:変数設定(文字列型):link1, strstyle, url
2行目:link1 = エクセルのB8セルの値のURL
3行目:url に htmlのaタグを入れ込み、リンク先を3行目でしていたlink1に設定
4行目:mailBody を フォント「MS P明朝」、文字の色を「黒」に設定し、3行目で設定したリンクをつなげる
5行目:HTML形式のメール本文に4行目で設定したHTML文章 + 改行 + 改行 + 署名

これを使えば、メール本文のフォントを変更したり、ハイパーリンクを設置できます。コピペしてぜひ活用してみてください。

テンプレートファイルをダウンロードしたい方はコチラから

紹介したoutlookを操作するコードを一から作るのは大変なので、記事の中で紹介した2つのマクロのコードが入ったファイルは無料でダウンロードできるようにします。以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。

ぜひご活用ください。

もし重要度、開封確認を取得したり、送信日時を取得したいなら

重要度の設定方法

以下の3つの内、1つを選択すれば、メールの重要度を設定できます。もし設定したいなら、上記で紹介している「コード4|メール本文を改行して署名を入れる」のコードに追加します。

'[1]メールの重要度を高くする
mailItemObj.Importance = olImportanceHigh  

'[2]メールの重要度を普通にする
mailItemObj.Importance = olImportanceNormal

'[3]メールの重要度を低くする
mailItemObj.Importance = olImportanceLow 

開封確認の設定方法

開封確認をしたい場合は、以下の方法で設定できます。もし設定したいなら、上記で紹介している「コード4|メール本文を改行して署名を入れる」のコードに追加します。

'[1]開封確認を設定する
mailItemObj.ReadReceiptRequested=True

'[2]開封確認を設定しない
mailItemObj.ReadReceiptRequested=False

送信日時の取得方法

エクセルマクロでoutlookメールを送信するとき、送信日時をエクセルに出力したい場合があります。そのときの方法を紹介します。ここでは、セルB10に送信日時を出力するように設定します。

Range("B10").Value = Now    '送信日時を取得

上のコードを、上記で紹介している「コード6|メール送信する」のコードに追加します。補足ですが、Nowを使えば、現在の日時を取得できます。メール送信の次の行に、これをコードすれば送信日時を取得可能です。

差し込みメールで複数の宛先に一斉送信したいなら

エクセルシートでアウトルックを操作してメールを送る場合、最大のメリットは、

宛先、人名、本文をあらかじめ準備しておき、パターンに応じて、VBAで宛先、人命、本文を選択し、自動でメールを送信する

というシステムを構築することです。ボタン一つで、異なるメールを異なる宛先に配信できます。

そのためには、姓を記入する列、名を記入する列を用意し、そこに入っているデータで name1 , name2 という差し込み用の文字列を自動的に置き換えてメールを、送る、という仕様が必要になります。

もっと多くの値を置き換えの項目にしてもかまいませんし、減らしてもかまいません。また配列やfunctionプロシージャのような知識を合わせることで、さらに効率よくメールを自動送信するシステムを構築できれば、すさまじい効率化をもたらすことができます。

しかし、あなたの状況に合わせて、コードを編集したり、コードを追加したりするには、エクセルマクロVBAの基本を確実に抑えておく必要があります。そうしないとここで紹介しているコードをそのままコピペして使うくらいしかできません。

もし、この記事で紹介した内容を読んで、「ちょっとムズカシイ・・・けど、もっといろいろできるようになりたい!」と感じているなら、こちらの無料動画で学ぶことをオススメします。エクセルマクロVBAの基本からしっかり学ぶことができますので、この記事で紹介したコードを編集できるようになりたい!と考えている方には、オススメです。

outlookの受信メールを読み込んで、エクセルに一覧で表示する方法

エクセルマクロでは、outlookの受信メールをエクセルに出力することも可能です。

受信トレイのメールをエクセルに一覧にする

受信トレイのメールをエクセルに一覧にしたい場合は、こちらの記事で詳しく紹介しています。

ただ一覧にするだけではなく、アウトルックのメールに添付されている資料を、自動でフォルダに保管することも可能です。

こちらの記事では、受信メールを解析して、エクセルに一覧にする方法を紹介していますので、ぜひ合わせて読んでみてください。

www.fastclassinfo.com

受信トレイのサブフォルダ内のメールをエクセルに一覧にする

受信トレイのサブフォルダ内のメールをエクセルに一覧にしたい場合は、こちらの記事で詳しく紹介しています。

www.fastclassinfo.com

ぜひ参考にしてみてください。

メール送信を含めた外部アプリケーション連携を深く学ぶなら

ここまで、初心者必見のVBAを使ったアウトルック操作について、事例をあわせて紹介しました。今回の内容をぜひVBAの勉強に活かしてみてください。

とはいえ「自分にはハードルが高そう、、、」と感じている方も多いのではないでしょうか。

VBAのアウトルック操作に関しては、なかなかいい教材はありませんが、もし勉強をするなら、こちらがオススメです。

アウトルック連携をもう少し詳しく学ぶなら|本がオススメ

オフィスの達人〈5〉VBA活用術―Excel/Word/Access/PowerPoint/Outlook Office 2003対応 (オフィスの達人 (5))

オフィスの達人〈5〉VBA活用術―Excel/Word/Access/PowerPoint/Outlook Office 2003対応 (オフィスの達人 (5))

本だと、手軽に始めることができます。しかし、本だと「分からない部分を質問できない」、「情報がアップデートされていない」ことがあります。この本もタイトルに書いてあるようにoffice 2003で、情報がしっくりこない部分があるかもしれません。ですので、がっつり学びたい方には、物足りないでしょう。

アウトルック連携を含めた外部アプリケーション連携をマスターしたいなら

アウトルックとの連携を学ぶなら、エクセルからアウトルックなどの外部アプリとの連携について、基礎から完全にマスターすることをオススメします。そうすることで、アウトルック連携が本当に意味で理解できるようになります。

もしきちんと学ぶなら、このオンライン動画教材がオススメです。講師に質問できますので、分からないところがあっても安心ですし、今すぐ始めることができます。

この記事がVBAでアウトルックを操作して、自動メールを送ることができるようになれば、幸いです。