Outlookのメールを日々利用されている方は、Excelで宛先や本文などまとめた情報からメールを作成・送信したいときはないでしょうか。
例えば、日報、週報、各書届などがワンクリックで何件もメールが作成され、送信ができるとなるととても楽になりますよね。
けど、そんな中で悩むことは、
・毎回メールを新規作成するのは面倒なので自動化したいけどやり方がわからない。
・日報、週報のメールをテンプレート化して、報告内容を入力するだけにしたいがやり方がわからない。
・宛先を手入力すると間違えてしまう可能性があるので固定値として定義しておきたいが、どうすればよいかわからない。
ですよね。
今回はそんなお悩みを解決する
・メールの差出人を変更、下書き保存、MSG形式で指定フォルダへ保存する方法
についてまとめます!
もくじ
ExcelからOutlookのメールを作成・送信する機能ついて
今回のVBAはExcelに記載した宛先、本文情報とメールテンプレートをもとに、Outlook側のメールを作成し、送信するといった内容となります。
サンプルデータは企業でよく使う勤怠の諸届(半休、全休、休日出勤など)としています。
それを以下のようにExcelのデータシートへ記載していきます。
続いて、メールテンプレートとなるシートを追加し、以下のようにタグ付きで入力します。
入力が完了したら、メールを作成したい行のA列に「表示」、メールテンプレート名を入力した上、VBAを実行すると、
メールテンプレートに沿った形でメールが作成され、タグの部分は先ほどのデータシートの値に置換されます。
また、A列に「送信」とすることで、表示をせずに送信することができます。
複数送信したい場合は、その分行を増やせばよいですし、
諸届以外にも日報など別のメール形式で出力したいときは、メールテンプレートを追加し、データシートに追加すればいろいろな種類のメールを同時に作成、送信することが可能です。
ExcelからOutlookのメールを作成・送信する機能の処理の流れについて
今回の機能の処理は以下の通りExcelからOutlookを起動し、Outlookのメール作成、送信が行われる流れとなります。
②Excelブック ← メールを作成したよ ← Outlook
③Excelブック(VBA) → メールを送信してね → Outlook
④Excelブック ← メールを送信したよ ← Outlook
では、早速実装をして動かしてみましょう!
VBAを実装する
続いてVBAを実装します。
VBAは以下の通りとなります。
Sub メール作成および送信()
Dim objOutlookApp As Object
Dim objMail As Object
Dim Rng As Range
Dim intintNum As Integer
Dim strBody As String
Dim strTag1 As String
Dim strTag2 As String
Dim strTag3 As String
Dim strTag4 As String
Dim strTag5 As String
Dim strBr As String
Dim strSubject As String
Dim strSTname_Template As String
strBr = vbLf '改行コード
'「メール宛先・本文」シートのA列最終行まで処理を繰り返します。
For intNum = 2 To Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
'「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。
If Worksheets(1).Cells(intNum, 1) <> "" Then
'メールテンプレートを設定します。
strSTname_Template = Worksheets(1).Range("B" & intNum).Value
'メールテンプレートの指定が無い場合は警告表示および処理を中断します。
If strSTname_Template = "" Then
MsgBox "B" & intNum & "にタイトル・本文テンプレートシートを指定してください。"
Exit Sub
End If
'Outlook.Applicationを呼び出し、セットします。
Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(olMailItem)
'メール宛先・本文シート記載の値を変数に入れます。
strTag1 = Worksheets(1).Range("G" & intNum).Text
strTag2 = Worksheets(1).Range("H" & intNum).Text
strTag3 = Worksheets(1).Range("I" & intNum).Text
strTag4 = Worksheets(1).Range("J" & intNum).Text
strTag5 = Worksheets(1).Range("K" & intNum).Text
'テンプレートのメール本文の箇所を読み込み、変数に入れます。
For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range("A5").Value)
strBody = strBody & Rng.Value & vbLf
Next Rng
'メール本文中のタグを指定データへ置換します。
strBody = Replace(strBody, "<Tag1>", strTag1)
strBody = Replace(strBody, "<Tag2>", strTag2)
strBody = Replace(strBody, "<Tag3>", strTag3)
strBody = Replace(strBody, "<Tag4>", strTag4)
strBody = Replace(strBody, "<Tag5>", strTag5)
With objMail
.BodyFormat = 3 ' 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.To = Worksheets(1).Range("E" & intNum).Value
.CC = Worksheets(1).Range("F" & intNum).Value
'本文をBodyプロパティにセットします。
.Body = Worksheets(1).Range("C" & intNum).Value & strBr & Worksheets(1).Range("D" & intNum).Value & strBr & strBr & strBody
'テンプレートからタイトルを読み込みます。
strSubject = Worksheets(strSTname_Template).Range("A2").Value
'タグ部分を置換します。
strSubject = Replace(strSubject, "<Tag1>", strTag1)
strSubject = Replace(strSubject, "<Tag2>", strTag2)
strSubject = Replace(strSubject, "<Tag3>", strTag3)
strSubject = Replace(strSubject, "<Tag4>", strTag4)
strSubject = Replace(strSubject, "<Tag5>", strTag5)
.subject = strSubject
Select Case Worksheets(1).Cells(intNum, 1)
Case "表示"
'メールを表示します。
.Display
Case "送信"
.Display
.Send
Case Else
MsgBox "値が異なります。「表示」か「送信」を入力してください。"
End Select
End With
'メール本文をクリアします。
strBody = ""
End If
Next intNum
MsgBox "メール作成・送信が完了しました。"
Set objOutlookApp = Nothing
Set objMail = Nothing
End Sub
設定が必要な箇所は特にないですが、メールフォーマットは標準はリッチテキスト形式ですので、
利用しているフォーマットと異なれば適宜変更するようお願いします。
「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.BodyFormat = 3
サンプルデータを用意する
上記の通りサンプルデータは企業でよく使う勤怠の諸届(半休、全休、休日出勤など)としていますので、
まずは情報を入力するデータシートおよび諸届メールのテンプレートシートを用意しましょう。
①情報を入力するシートを作成し、シート名を「メール宛先・本文」へ変更します。
※このシートはブックの1番左の位置へ配置するようにお願いします。
②1行目に項目名を入力します。
Mail操作 | タイトル・本文テンプレート | 部署名 | 氏名 | To | Cc | Tag1 | Tag2 | Tag3 | Tag4 | Tag5 |
③2行目のC2~J2まで以下のように入力します。
各項目の値は業務内容に応じて変更していただいて構いません。
④メールテンプレートとなるシートを追加し、シート名を「諸届テンプレ」へ変更します。
⑤以下をコピーし、A1セルからペーストしてください。
■タイトル
【諸届】タカヒロ:<Tag1>:<Tag2>■本文記載範囲
A8:A16■本文
以下の通り申請致します。
氏名:タカヒロ
年月日:<Tag1>
区分:<Tag2>
時間:<Tag3>
理由:<Tag4>
——–ここまで——-
申し訳ありません。
訂正をいたしましたので、再度参照頂ければと思います。
⑥「メール宛先・本文」シートへ戻り、A2に「表示」、B2に「諸届テンプレ」と入力します。
A列に「表示」といれるとその行の値を参照し、メールが作成・表示されることを意味します。
B列はメール本文のテンプレートを指定する列となります。
シートを追加することで複数の本文テンプレートを設定することが可能です。
これでデータの準備は完了です!
VBAを実行する
メールを作成・表示する
①「開発」タブの「マクロ」をクリックし「メール作成および送信」を選択し、「実行」をクリックします。
②Outlookからメールが作成されたら完成です!
メールを送信する
①「メール宛先・本文」シートのA列を「送信」に変更します。
サンプルでは行を増やし2つの書届メールを作成、送信します。
②「開発」タブの「マクロ」をクリックし「メール作成および送信」を選択し、「実行」をクリックします。
④Outlookの送信トレイの蓄積、送信済みトレイに送信履歴があれば完成です!
今回のVBAについて説明
「メール宛先・本文」シートのA列最終行を取得し、件数分処理を繰り返します。
For intNum = 2 To Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。
If Worksheets(1).Cells(intNum, 1) <> "" Then
メールテンプレートを設定します。
strSTname_Template = Worksheets(1).Range("B" & intNum).Value
メールテンプレートの指定が無い、もしくは指定テンプレートが存在しない場合は警告表示および処理を中断します。
If strSTname_Template = "" Then
MsgBox "B" & intNum & "にタイトル・本文テンプレートシートを指定してください。"
Exit Sub
End If
Outlook.Applicationを呼び出し、セットします。
Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(olMailItem)
メール宛先・本文シート記載の値を変数に入れます。
strTag1 = Worksheets(1).Range("G" & intNum).Text
strTag2 = Worksheets(1).Range("H" & intNum).Text
strTag3 = Worksheets(1).Range("I" & intNum).Text
strTag4 = Worksheets(1).Range("J" & intNum).Text
strTag5 = Worksheets(1).Range("K" & intNum).Text
テンプレートのメール本文の箇所を読み込み、変数に入れます。
For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range("A5").Value)
strBody = strBody & Rng.Value & vbLf
Next Rng
メール本文中のタグを指定データへ置換します。
strBody = Replace(strBody, “”, strTag2)
strBody = Replace(strBody, “”, strTag3)
strBody = Replace(strBody, “”, strTag4)
strBody = Replace(strBody, “”, strTag5).BodyFormat = 3 ‘ 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.To = Worksheets(1).Range(“E” & intNum).Value
.CC = Worksheets(1).Range(“F” & intNum).Value
本文をBodyプロパティにセットします。
.Body = Worksheets(1).Range("C" & intNum).Value & strBr & Worksheets(1).Range("D" & intNum).Value & strBr & strBr & strBody
テンプレートからタイトルを読み込みます。
strSubject = Worksheets(strSTname_Template).Range("A2").Value
タグ部分を Replaceメソッドで指定した値へ置換します。
<code>strSubject = Replace(strSubject, "", strTag1)
strSubject = Replace(strSubject, "", strTag2)
strSubject = Replace(strSubject, "", strTag3)
strSubject = Replace(strSubject, "", strTag4)
strSubject = Replace(strSubject, "", strTag5)
.subject = strSubjectSelect Case で実行パターン別に処理をおこないます。
Select Case Worksheets(1).Cells(intNum, 1)
Case "表示"
'メールを表示します。
.Display
Case "送信"
.Display
.Send
Case Else
MsgBox "値が異なります。「表示」か「送信」を入力してください。"
End Select
最後にメール本文をクリアします。
strBody = ""
<追加>作成したメールの差出人を変更する方法
作成したメールの差出人を変更する方法について説明をします。
サンプルは、デフォルトの差出人が「takahiro1」である場合、2つ目のアドレス「takahiro9」に変更する内容となります。
ソースコードの変更箇所はCase “表示”の中となります。以下のように処理を追加していきます。
■変更前
Case "表示"
.Display
■変更後
Case "表示"
‘差出人を指定します。
Set .SendUsingAccount = objOutlookApp.Session.Accounts.Item(“takahiro9@****.**”)
.Display
実行してみましょう。
はい、差出人が2つ目のアドレス「takahiro9」に変更されていますね。
SendUsingAccountプロパティを使用して、MailItem「objMail」の差出人アカウントを指定しています。
差出人アカウントはOutlookアプリケーション「objOutlookApp」のSession.Accounts.Itemにありますので、SMTPアドレスで指定しています。またSMTPアドレス以外にも順番をあらわす数値でも指定が可能です。
objOutlookApp.Session.Accounts.Item(2)
<追加>作成したメールを下書きへ保存する方法
作成したメールを下書きへ保存する方法について説明をします。
同じくCase “表示”のコードを以下のように変更をします。
■変更前
Case "表示"
.Display
■変更後
Case "表示"
‘下書きへ保存します。
.Save
.Display
VBAを実行してみます。
はい、下書きフォルダへ保存されていますね。
<追加>作成したメールをMSG形式で指定フォルダへ保存する方法
作成したメールをMSG形式で指定フォルダへ保存する方法について説明をします。
保存先のフォルダは「F:\MSG」、MSGファイルのファイル名はメールの件名となるようにしています。
同じくCase “表示”のコードを以下のように変更をします。
■変更前
Case "表示"
.Display
■変更後
Case "表示"
.Display
‘MSGファイルとして保存します。
.SaveAs “F:\test\” & .subject & “.msg”, olMSG
VBAを実行してみます。
はい、指定フォルダへ保存されていますね。
SaveAsメソッドはOutlookアイテムを、指定したパスに、指定したファイルの種類の形式で保存することができます。
書式は以下の通りです。
SaveAs (Path, Type)
TypeはMSGタイプの他にolHTML、olRTF、olTemplate、olDoc、olTXT、olVCal、olVCard、olICal、olMSGUnicode などが指定できます。
<追加>作成したメールにファイルを添付する方法
作成したメールにファイルを添付する方法について説明をします。
添付ファイルは「F:\test」にある2つのExcelファイルです。
表のQ列目に添付ファイルのパスを記述するスペースを設け、添付したいメールに添付ファイルのパスを指定していきます。
コードを以下のように変更をします。
■変更前
.subject = strSubject
■変更後
.subject = strSubject
‘ファイルを添付します。
If IsEmpty(Worksheets(1).Range(“Q” & intNum).Value) = False Then
.Attachments.Add Worksheets(1).Range(“Q” & intNum).Value
End If
VBAを実行してみます。
はい、指定フォルダへ保存されていますね。
<追加>メール本文にハイパーリンクをつける方法
メール本文にハイパーリンクをつける方法について説明をします。
基本的にhttpから始まる文字列や@マーク入りの文字列はリッチテキスト形式であれば貼り付けた段階でハイパーリンクに変換されます。
ただし、「ホームページはこちら」などアドレスと表示内容を別にしたい場合はできません。
そこで、少し技術難易度は上がりますが、HTMLタグを使うことにより対応ができますので、追加説明をしたいと思います。
まず、エクセル側のテンプレートの改行コードをHTMLタグに変更します。
サンプルテンプレートの「諸届テンプレ」を以下のように変更します。
■タイトル
【諸届】タカヒロ:<Tag1>:<Tag2>
■本文記載範囲A8:A16
<code>
■本文以下の通り申請致します。<br><br>
氏名:タカヒロ<br>
年月日:<Tag1><br>
区分:<Tag2><br>
時間:<Tag3><br>
理由:<Tag4><br>
Tag4にHTMLタグ付きの文字列を入れます。
夏季休暇をいただきたくお願いします。<br>
休暇中の<b>連絡先は以下まで</b>お願いします。<br>
<a href="mailto:○○○@○○○.co.jp">メールはこちら</a>
こちらは太字を示します。
<b>連絡先は以下まで</b>
hrefでメールリンク先を指定します。
<a href="mailto:○○○@○○○.co.jp">メールはこちら</a>
コードを以下に変更をします。
変更した箇所は以下の3点です。
改行コードを<br>に変更。
strBr = "<br>"
本文のフォーマットをHTML型に変更。
.BodyFormat = 2
本文をhtmlBodyプロパティに変更。
.htmlBody =
全コード
Sub HTMLメール作成および送信()
Dim objOutlookApp As Object
Dim objMail As Object
Dim Rng As Range
Dim intintNum As Integer
Dim strBody As String
Dim strTag1 As String
Dim strTag2 As String
Dim strTag3 As String
Dim strTag4 As String
Dim strTag5 As String
Dim strBr As String
Dim strSubject As String
Dim strSTname_Template As String
strBr = "<br>" '改行コード
'「メール宛先・本文」シートのA列最終行まで処理を繰り返します。
For intNum = 2 To Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
'「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。
If Worksheets(1).Cells(intNum, 1) <> "" Then
'メールテンプレートを設定します。
strSTname_Template = Worksheets(1).Range("B" & intNum).Value
'メールテンプレートの指定が無い場合は警告表示および処理を中断します。
If strSTname_Template = "" Then
MsgBox "B" & intNum & "にタイトル・本文テンプレートシートを指定してください。"
Exit Sub
End If
'Outlook.Applicationを呼び出し、セットします。
Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(olMailItem)
'メール宛先・本文シート記載の値を変数に入れます。
strTag1 = Worksheets(1).Range("G" & intNum).Text
strTag2 = Worksheets(1).Range("H" & intNum).Text
strTag3 = Worksheets(1).Range("I" & intNum).Text
strTag4 = Worksheets(1).Range("J" & intNum).Text
strTag5 = Worksheets(1).Range("K" & intNum).Text
'テンプレートのメール本文の箇所を読み込み、変数に入れます。
For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range("A5").Value)
strBody = strBody & Rng.Value & vbLf
Next Rng
'メール本文中のタグを指定データへ置換します。
strBody = Replace(strBody, "<Tag1>", strTag1)
strBody = Replace(strBody, "<Tag2>", strTag2)
strBody = Replace(strBody, "<Tag3>", strTag3)
strBody = Replace(strBody, "<Tag4>", strTag4)
strBody = Replace(strBody, "<Tag5>", strTag5)
With objMail
.BodyFormat = 2 ' 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.To = Worksheets(1).Range("E" & intNum).Value
.CC = Worksheets(1).Range("F" & intNum).Value
'本文をhtmlBodyプロパティにセットします。
.htmlBody = Worksheets(1).Range("C" & intNum).Value & strBr & Worksheets(1).Range("D" & intNum).Value & strBr & strBr & strBody
'テンプレートからタイトルを読み込みます。
strSubject = Worksheets(strSTname_Template).Range("A2").Value
'タグ部分を置換します。
strSubject = Replace(strSubject, "<Tag1>", strTag1)
strSubject = Replace(strSubject, "<Tag2>", strTag2)
strSubject = Replace(strSubject, "<Tag3>", strTag3)
strSubject = Replace(strSubject, "<Tag4>", strTag4)
strSubject = Replace(strSubject, "<Tag5>", strTag5)
.Subject = strSubject
Select Case Worksheets(1).Cells(intNum, 1)
Case "表示"
'メールを表示します。
.Display
Case "送信"
.Display
.Send
Case Else
MsgBox "値が異なります。「表示」か「送信」を入力してください。"
End Select
End With
'メール本文をクリアします。
strBody = ""
End If
Next intNum
MsgBox "メール作成・送信が完了しました。"
Set objOutlookApp = Nothing
Set objMail = Nothing
End Sub
設定が完了したらVBAを実行してみましょう。
はい、リンクが有効になり、太字にもなっていますね。
リンクをクリックするとメールが開きます。
hrefの横にある「mailto:」を外し、httpから始まるアドレスを入力してください。
<追加>メール本文に特定の書式やフォントを設定する方法
メール本文に特定の書式やフォントを設定する方法について説明をします。
まず、使用するコードは上記ハイパーリンクをつける仕様のコードになりますので、
「HTMLメール作成および送信」のコードを実装するようお願いします。
理由は書式設定はHTMLコードで記述するためです。
次に書式を設定したデータを用意します。
今回設定する書式は以下の通りです。
・文字色
・太字
・アンダーライン
・ハイパーリンク
フォントは
游ゴシックLight
を指定します。
まずはタグに設定する範囲で書式及びフォントを設定してみましょう。
<span style="font-family: '游ゴシック Light';">游ゴシック Lightフォントの指定
<span style="color: red;">文字赤</span>
<b>文字太字</b>
<u>文字斜体</u>
<a href="https://extan.jp/">ハイパーリンクhttps://extan.jp/</a>
</span>
全体の設定は以下の通りです。
テンプレートシートの内容もHTMLタグに対応させるため、改行コード<br>を追加します。
以下の通り申請致します。<br>
氏名:タカヒロ<br>
<br>
商品名:<Tag2><br>
発注日:<Tag3><br>
納品日:<Tag4><br>
運送会社:<Tag5><br>
<br>
以上/タカヒロ
設定が終わったら、ハイパーリンクで紹介したコード
「HTMLメール作成および送信」でVBAを実行します。
はい、メールが作成され、
指定タグ内の書式およびフォントが設定されたことがか確認できました。
次は本文全体にフォント指定をしてみたいと思います。
先ほどのテンプレート本文を以下のように指定したフォントタグで囲みます。
<span style='font-family:"游ゴシック Light"'>
以下の通り申請致します。<br>
氏名:タカヒロ<br>
<br>
商品名:<Tag2><br>
発注日:<Tag3><br>
納品日:<Tag4><br>
運送会社:<Tag5><br>
<br>
以上/タカヒロ
</span>
VBAを実行してみましょう。
はい、本文全体のフォントが指定されましたね!
これは本文外で指定しているためで、お手数ですが、
本文データ入力欄ごとにHTMLタグでフォントを指定するようお願いします。
さいごに
いかがでしょうか。
今回は、
・メールの差出人を変更、下書き保存、MSG形式で指定フォルダへ保存する方法
についてまとめました。
また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。
tagの欄に〈a herf= “mailto:○○○@○○○、co.jp〉を記入しましたが
メールにそのまま表示されてしまします
メール形式はHTMLにしているのになぜでしょうか
HTMLタグがそのまま表示されてしまう件ですが、
本文はBodyではなくhtmlBodyプロパティに変更する必要がありますので、
.htmlBodyになっているか確認いただけますでしょうか。
またHTMLの構文が正しいか確認いただけますでしょうか。
<a href=”mailto:○○○@○○○.co.jp”>メール</a>
※「<>」は半角に置き換えてください。
メール形式をHTMLにしてExcelの表からtagで、ハイパーリンクはできたのですがメールがうまくいきません
〈a herf= “mailto:○○○@○○○、co.jp〉のところでメールアドレスの所を〈tag○〉を代入することはできないのでしようか
Excelのセルにmailto:を入れればメールアドレスのリンクはいきますが
mailtoをみせたくないのです
方法はありますか?
tag○の欄に入れたHTMLタグでメールを起動させるハイパーリンクをつける方法につきまして、
いくつかコードを変える必要があり、記事内に追記いたしましたので、こちらをご参照いただけますでしょうか。
https://extan.jp/?p=3408#%EF%BC%9C%E8%BF%BD%E5%8A%A0%EF%BC%9E%E3%83%A1%E3%83%BC%E3%83%AB%E6%9C%AC%E6%96%87%E3%81%AB%E3%83%8F%E3%82%A4%E3%83%91%E3%83%BC%E3%83%AA%E3%83%B3%E3%82%AF%E3%82%92%E3%81%A4%E3%81%91%E3%82%8B%E6%96%B9%E6%B3%95
tagの欄にメールやURLのリンクを紐付けたままメールに貼り付けたい場合は
どのようにすればいいですか?
また 違うシートのExcelの表をメールに貼り付けたい場合はどう設定すればいいでしょうか?
いつもご利用ありがとうございます。
tagの値はリンクを付与したとしても貼り付け時にテキストへ変換される仕様となっており、
申し訳ありませんがExcelに設定したハイパーリンクをそのままメールへ貼り付けることはできない状況です。
対応としては、ハイパーリンクやURLを識別しリンクを挿入するか、
https://extan.jp/?p=1171
BodyプロパティをhtmlBodyプロパティへ変更し、リンク部分をHTMLタグで記述する方法が考えられます。
この場合、HTMLの知識が必要になりますので、リファレンスなどご参考いただければと存じます。
次のExcelシートにある表をメール本文に張り付ける方法については別記事にまとめておりますので、
ご参考いただければと存じます。
https://extan.jp/?p=6692
ご返信ありがとうございます。
Outlook VBAではなく、Excel VBAに組み込めないかと思いお聞きしました。
他のサイトから移植できないか探してみます。
ありがとうございました。
以前、こちらのマクロを利用させていただきましたものです。
メール本文の範囲を広げて、表をメール本文に入れ込みたいのですが、どのようにすれば表にすることができるかご教示いただけると大変うれしいです。
分かるところを下記のように書き換えております。
A5の範囲指定を
■本文記載範囲
A8:D25
として表を入れ込みたかったのですが、文字で表示されてしまうので、無駄な改行と表も文字で表示されてしまいます。
VBAの内容は下記のように少し書き換えています。
Sub メール作成および送信()
Dim objOutlookApp As Object
Dim objMail As Object
Dim Rng As Range
Dim intintNum As Integer
Dim strBody As String
Dim strTag1 As String
Dim strTag2 As String
Dim strTag3 As String
Dim strBr As String
Dim strSubject As String
Dim strSTname_Template As String
strBr = vbLf ‘改行コード
‘「メール宛先・本文」シートのA列最終行まで処理を繰り返します。
For intNum = 2 To Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
‘「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。
If Worksheets(1).Cells(intNum, 1) “” Then
‘メールテンプレートを設定します。
strSTname_Template = Worksheets(1).Range(“B” & intNum).Value
‘メールテンプレートの指定が無い場合は警告表示および処理を中断します。
If strSTname_Template = “” Then
MsgBox “B” & intNum & “にタイトル・本文テンプレートシートを指定してください。”
Exit Sub
End If
‘Outlook.Applicationを呼び出し、セットします。
Set objOutlookApp = CreateObject(“Outlook.Application”)
Set objMail = objOutlookApp.CreateItem(olMailItem)
‘メール宛先・本文シート記載の値を変数に入れます。
strTag1 = Worksheets(1).Range(“H” & intNum).Text
strTag2 = Worksheets(1).Range(“I” & intNum).Text
‘テンプレートのメール本文の箇所を読み込み、変数に入れます。
For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range(“A5”).Value)
strBody = strBody & Rng.Value & vbLf
Next Rng
‘メール本文中のタグを指定データへ置換します。
strBody = Replace(strBody, “”, strTag1)
strBody = Replace(strBody, “”, strTag2)
With objMail
.BodyFormat = 3 ‘ 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.To = Worksheets(1).Range(“F” & intNum).Value
.CC = Worksheets(1).Range(“G” & intNum).Value
‘本文をBodyプロパティにセットします。
.Body = Worksheets(1).Range(“C” & intNum).Value & strBr & Worksheets(1).Range(“D” & intNum).Value & strBr & Worksheets(1).Range(“E” & intNum).Value & strBody
‘テンプレートからタイトルを読み込みます。
strSubject = Worksheets(strSTname_Template).Range(“A2”).Value
‘タグ部分を置換します。
strSubject = Replace(strSubject, “”, strTag1)
strSubject = Replace(strSubject, “”, strTag2)
strSubject = Replace(strSubject, “”, strTag3)
.Subject = strSubject
Select Case Worksheets(1).Cells(intNum, 1)
Case “表示”
‘メールを表示します。
.Display
Case “送信”
.Display
.Send
Case Else
MsgBox “値が異なります。「表示」か「送信」を入力してください。”
End Select
End With
‘メール本文をクリアします。
strBody = “”
End If
Next intNum
MsgBox “メール作成が完了しました。”
Set objOutlookApp = Nothing
Set objMail = Nothing
End Sub
いつもご利用ありがとうございます。
表をメール本文に入れ込む方法について、別記事にてまとめておりますので、
こちらをご参考の上、実装および検証いただけますでしょうか。
https://extan.jp/?p=6692
よろしくお願いいたします。
このような使い方をしたかったので利用させていただきます。
1つ、送信に対する有無をつけたいと思っており、この人(Aさん)には送信するが、特定の人(Bさん)には送信しない、と条件を追加したいと思っています
このケースの場合、マクロ部分にどのような条件を付与して、どの部分を修正していくのが良いのでしょうか
いつもご利用ありがとうございます。
送信先を条件によって振り分ける方法ですが、
まず、ToやCCの送信パターンごとに行を増やして登録していく方法がコード修正不要でよいかと思われます。
次に、コード中に除外処理を加えるとすると、
以下のようにIF文で条件を追加し、条件に合致したらReplaceで不要なアドレスを除外、
その結果をToやCCに代入すればよいかと思われます。
If <何かしらの条件> Then
strTo = Replace(Worksheets(1).Range(“E” & intNum).Value, “<除外アドレス>”, “”)
End If
.To = strTo
よろしくお願いいたします。
タカヒロ@extanさま
このメール一括ツールのVBA作成方法など色々探していましたが、この記述を見つけ希望が出てきました。早速試そうと思っていますが、今回の例ではメールに添付ファイルを付けたい場合の方法がなく、添付ファイル付きの一括メール送信ツールを作成したいと思っています。
この例でどのようにすれば良いでしょうか。
突然、お忙しいところ申し訳ないのですがご教授願います。
いつもご利用ありがとうございます。
作成したメールにファイルを添付する方法につきまして、
記事へ追記しましたのでよろしければご参考ください。
https://extan.jp/?p=3408#%EF%BC%9C%E8%BF%BD%E5%8A%A0%EF%BC%9E%E4%BD%9C%E6%88%90%E3%81%97%E3%81%9F%E3%83%A1%E3%83%BC%E3%83%AB%E3%81%AB%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E6%B7%BB%E4%BB%98%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95
メールを自動作成したくていろんなサイトで探しまくっていたのですが、こちらの内容がとても使い勝手がよく助かっています。
2つ質問なのですが、差出人のアドレスを指定するとしたらどのようにすればよいですか?
1つの固定アドレスを想定しています。
また、メールの下書きを自動的にデスクトップ等に保存することは可能でしょうか?
いつもご利用ありがとうございます。
差出人のアドレスを指定する方法、メールを指定フォルダへ保存する方法について
記事へ追記しましたのでよろしければご参考ください。
https://extan.jp/?p=3408#%EF%BC%9C%E8%BF%BD%E5%8A%A0%EF%BC%9E%E4%BD%9C%E6%88%90%E3%81%97%E3%81%9F%E3%83%A1%E3%83%BC%E3%83%AB%E3%81%AE%E5%B7%AE%E5%87%BA%E4%BA%BA%E3%82%92%E5%A4%89%E6%9B%B4%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95
もし可能であれば、教えていただきたいのですが,
メール本文の部分に色付けしたり、フォントサイズを変更たり、URLを張ったりして、その内容のままOutlookメールの本文に反映したいので、調べて実施してみたのですが、うまくいきませんでした。
どのようなコードを入れれば対応できるでしょうか。
公開の範疇を超えているので大変申し訳ないのですが、教えていただけると大変助かります。
宜しくお願いします。
いつもご利用ありがとうございます。
ご質問の本文への書式設定の方法につきまして、
Excel側の書式設定をそのままリッチテキスト形式でOutlookへ引き継げないか検証いたしましたが、
うまくいきませんでした。
そこで代案として、フォームはHTML形式を指定しHTMLコードで書式を指定頂きたくお願いいたします。
設定方法は以下の通りです。
■VBA変更箇所
strBr = vbLf ‘改行コード
↓変更
strBr = “
” ‘改行コード
.BodyFormat = 3
↓変更
.BodyFormat = 2
.Body = Worksheets(1).Range(“C” & intNum).Value…
↓変更
.HTMLBody = Worksheets(1).Range(“C” & intNum).Value…
■Excelシート値
Tag1-5のいづれかに以下のようにHTMLコードを入力願います。
<font color=”red”>文字赤</font><br>
<b>文字太字</b><br>
<u>文字下線</u><br>
<a href=”https://extan.jp/”>ハイパーリンクhttps://extan.jp/</a>
よろしくお願いいたします。
本当に初心者で申し訳ないです。
”ag1-5のいづれかに以下のようにHTMLコードを入力”
はどのように入力すればよいでしょうか。
HTMLの指定方法ですが、
「メール宛先・本文」シートの各Tagへ入力されている値に対して、
文字サイズや色など書式を変えたい部分を、HTMLのタグで囲う形となります。
例えば一部の文字を太くしたい場合は
通常文字<B>太文字</B>通常文字 ※<>は半角へ変換してください。
となります。
HTMLタグの種類や詳細については「HTMLタグリファレンス」等で検索いただき、
文字装飾の方法をご参照頂ければと存じます。
似たような作業を自動化したく検索していたところ、ここにたたどり着きました、
VBA初心者なので、すべてコピペで作成してみたのですが、下記のデバックが出てしまいました。
何がいけなかったのでしょうか・・・
お教えいただけると助かります。
「実行時エラー’9′
インデクスが有効範囲にありません。」
該当箇所↓
For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range(“A5”).Value)
strBody = strBody & Rng.Value & vbLf
いつもご利用ありがとうございます。
インデックスのエラーの件ですが、
「メール宛先・本文」シートのB列にテンプレートとなるシート名(記事では「諸届テンプレ」としています。)が記載されているか、
テンプレートシート名とB列の値が一致しているか(半角スペースや改行がないかなども)確認頂けますでしょうか。
諸届テンプレにしたら成功しました!
ありがとうございます。
ちょうどこの様な事を行いたくて探していたら見つけました。
これが使いこなせたら仕事がかなり楽になります。
そこで質問があります。
1番左のシート:メール宛先・本文
2番のシート:諸届テンプレ
3番左のシート:Sheet1
「Sheet1」 から 関数で「メール宛先・本文」に表示する様にしてます。
エクセルのファイル名については特に明記が無かったので適当です。
他は全て説明通りに行ったつもりなんですが…
コンパイルエラー:ユーザー定期型は定義されていません。
上から3段目の
「Rng As Range」の所でエラーが起きます。
サンプルエクセルのデータなんかを配布していたら嬉しいのですが…
VBA初心者です、いろんなサイトで学んでいる最中です。
いつもご丁寧にありがとうございます
おしゃられるとうりでした OFFICE2010の環境で参照設定を
Microsoft Outlook Object Library が10でしたのでチェックいれて 2019で試したところ うまくできたようです ただいろいろな環境で試してみたので
参照設定がおかしくなっていたものかと思われます KINNGソフトのLibraryにチェックがあったりと いづれも参照不可の表示がありましたのではずしたら
うまくいきました
ただ、メモ帳書き出しの部分で 2010では 改行が行われず
一行表示となりました まあある程度妥協は必要なのでしょうか
うまくできたようで良かったです。
2010ではメモ帳書き出し後で改行がされない件ですが、
当方2010を保有していないので検証はできませんが、
strBr = vbLf
を
strBr = vbCrLf
に変更して変化があるかご確認いただけますでしょうか。
strBr = vbLf
を
strBr = vbCrLf
に書き換えでうまくいきました かなりカスタマイズしたものが出来上がった気がします まだまだ課題はあるようですが ひとまずはいいかんじです~
ほかの課題にも取り組みたいと思います
よろしくお願いいいたします
無事改行できたようですね。
また何かあれば気楽に連絡いただければと思います。
やはりそうなりますね シートには多数のマクロが組まれてますので あまり触ってほしくなくロックかけてます ひとつづつ環境に合わせて 行くしかないのでしょうか?
Set objOutlook = CreateObject(“Outlook.Application”)
少しほかで見かけたのですが 参照せってしなくても上記コードの追加でできるととのことでしたが あまりお勧めしないと書いてありました どうなのでしょう
なんか 色々スミマセン
ロックをかけて配布となると、各環境に合わせて参照設定をすることが基本になるかと思います。
ただ、調べていくと参照設定しているバージョンより高いモジュールがあれば自動的に高い方へ置き換わるそうです。
ですので、最下位のOffice2010で参照設定したファイルであれば以降の上位バージョンでも動く可能性はあります。
次に、参照設定をしない方法ですが、CreateObject(“Outlook.Application”)(公開しているVBAにも含まれています)はOutlookアプリケーションを参照してオブジェクトを作成するメソッドですので、参照設定を不要にするものではないと思われます。
ただ、こちらの環境(Win10、64bit、Office365 )で、Microsoft Outlook Object Libraryの参照設定を外して検証したところ、問題なく実行ができましたので、
方法としてはありなのかと思われます。
いずれにせよ確証がない状況ですので、お手数ですが検証頂ければと存じます。
ご親切にありがとうございます 下位のバージョンで OFFICE2010 で参照設定を入れたらばOFFICE2019ではそのままいけました ということは2013.2016でもいけそうな気がしてきました 2010での問題点は メモ帳に出力したときに改行ができず1列表示になってしまうところですか 2019 環境では改行もうまくいっています
なかなか 全バージョン対応は難しいものですね
ある程度妥協が必要なのかもしれませんね
ほんとに度々ひつ濃くてすみません また躓きました
今作成中のものが一つのブックで複数シートがあります それぞれのシートは
月ごとにわかれております 1行に数字が並んでいるわけなのですがA~GPまでです
それぞれ<Tag1~最終まで埋め込み メールを立ち上げることができます 成功です
感謝します
これで成功なのですが ファイルが膨大なため極力軽くすましたいので
コメントなど一つのセルで済ませようとしていますが そのセルの値を変換して テンプレートに反映させたいのですがうまくいきません
‘メール宛先・本文シート記載の値を変数に入れます。
strTag1 = Worksheets(1).Range(“B” & intNum).Text ‘
strTag2 = Worksheets(1).Range(“D” & intNum).Text
strTag3 = Worksheets(1).Range(“E” & intNum).Text ‘
strTag4 = Worksheets(1).Range(“H” & intNum).Text ‘
strTag5 = Worksheets(1).Range(“AD” & intNum).Text ‘
strTag6 = Worksheets(1).Range(“AG” & intNum).Text ‘
ここまで問題なしです ここから取得できません
セル指定であ書いてみたのですが できません
strTag8 = Worksheets(1).Range(“D44”).Value & intNum.).Text
‘ strTag8 = Worksheets(1).Range(“D45” & intNum).Text
‘ strTag9 = Worksheets(1).Range(“D46” & intNum).Text ‘
‘ strTag10 = Worksheets(1).Range(“D48” & intNum).Text
‘ strTag11 = Worksheets(1).Range(“D49” & intNum).Text
ご教授ください
ご連絡ありがとうございます。
ブックの構成につき以下にて理解しました。
①月単位でシートが分かれている
②各シートの1行目(A~GP)に数字が入力されている
③メール本文に入れるコメントを一つのセルに集約
まずは問題がある箇所ですが、文法の誤りがあります。
strTag8 = Worksheets(1).Range(“D44”).Value & intNum.).Text
③のコメントを「D44」のセルを指定し、値を取得する処理としますと、
strTag8 = Worksheets(1).Range(“D44”).Value
となります。
なお、以下のようにした場合は、
strTag8 = Worksheets(1).Range(“D44” & intNum).Text
「D442」のセルから下方向に順次参照される形となりますので、正しい動きか確認頂けますでしょうか。
よろしくお願いいたします。
ありがとうございます! 最高です これで解決すっきりです
一度そのようにい書いたような気がしたのですが 間違っていたんでしょうね
これで 完成したようです あとは欲を出していじりたいと思います
また、何かありました相談させてください
完璧に仕上がりました 満足です
ところが 各営業所に配布したところ 各所でエラーが…出てしまいました
これです
Set objMail = objOutlookApp.CreateItem(olMailItem)
で olMailItem
—————————
コンパイル エラー:
プロジェクトまたはライブラリが見つかりません。
多分OFFICEのバージョンの違いだと思います
参照設定と思うのですが 私の環境がWin10 64 OFFICE365
標準モジュールで、参照設定
Visual Basic Applications
Microsoft Excel 16.0 Library
OLE Automation
Upgrade WPS Office3.0 Object Library(Beta)
Microsoft Outlook 16.0 Object Library
にチェック入れてます
その他の環境としては 2010 2013 2016のバージョンですが
KINGSOFTもたまに使いますが outlook 扱えないので除外です
Upgrade WPS Office3.0 Object Library(Beta)
が悪さしているのかとも思うのですが
これまでこちらで参照設定をしていれば 確かその他のバージョンでもある程度できたと思うのですが
いかがでしょうか?
コンパイルエラーですね。
ご認識のとおり配布先Officeのバージョンが異なるためかと思われます。
Officeのバージョンによって各モジュールのバージョンも異なりますので、配布先環境に合わせて
参照設定をしていただく必要があります。
確認するポイントは参照設定をしたモジュールのバージョンが配布先環境に存在するかどうかです。
あれば動きますし、なければコンパイルに失敗してエラーとなります。
拠点によってOfficeのバージョンが異なるようですので、
参照設定を外した状態で配布し、マニュアルを添付するなどして
各拠点で参照設定をしていただいた方が良いかもしれません。
なお、KINGSOFTはごめんなさい。対象外となります。
いつもは拝見させてもらって参考にさせていいただいてます
私の設定が悪いのでしょうか?上記コードを実行すると
うまくTag>の部分が変換されず年月日:
区分:<Tag>
時間:<Tag>
理由:
と表示されるのですが どのようにしたらよいのでしょうか?
いつもご利用ありがとうございます。
タグにつきまして、こちらページの仕様によりうまく表示がされていないようで大変失礼いたしました。
カギカッコは半角で、その中身をTag1~Tag4まで割り当てて頂ければと存じます。
以下サンプルです。
年月日:<Tag1>
区分:<Tag2>
時間:<Tag3>
理由:<Tag4>
本文のほうもカギカッコが表示されるよう修正をいたしました。
VBAとテンプレートサンプルともに再度ご参照頂きたくお願いいたします。
よろしくお願いいたします。
ありがとうございます そのように変更して 検証いたします
お手数おかけいたします。
VBAとテンプレートサンプルともに再度ご参照頂ければと存じます。
ありがとうございます ほぼ100%完成しました
あとは 自分なりに少しカスタマイズして
完成します
また、よろしくお願いいたします
素晴らしいです 自分で想像したようになっていくことに感動します
ある程度できる気でいましたが、全然ということに気づかされます
ありがとうございます
さて、テキストで保存された内容を続けて確認したく思い
Open “C:\Users\*****\Documents\” & strSubject & “.txt” For Output As #1
Print #1, “件名:” & strSubject
Print #1, “本文:” & strBody
Close #1 ‘開いたファイルを閉じる
に
Shell “notepad”, 1
を追加してメモ帳で開こうかと思いましたが新規に立ち上がるばかりですね
わかっているのですが
Close #1 の前にいったん表示してと思いましたがどうもうまくいきません
Close #1 をコメントアウトしてみたのですが。。。
VB YES NOで YESで Close#1へ NOでそのままアクティブ
できませんか?
度々すみません
形になってきたようですね。
こちらも嬉しく思います。
テキストで保存されたファイルをShellでメモ帳を使用し開きたいということでしたら、
以下のように開きたいファイルのパスを引数に指定する必要があります。
Shell “notepad” & ” ” & “C:\Users\*****\Documents\” & strSubject & “.txt”, 1
YesNo形式でメモ帳で開くか確認メッセージを出したい場合は、以下のように条件を追加します。
Dim intYesNo As Integer
intYesNo = MsgBox(“メモ帳で確認しますか?”, vbYesNo, “確認”)
If intYesNo = vbYes Then
Shell “notepad” & ” ” & “C:\Users\*****\Documents\” & strSubject & “.txt”, 1
End If
ご確認ください。
もう一つ質問させてください このテンプレートで作成したメールの内容をそのまま メモ帳に出力したいのですが 追加でどのように書けばよいでしょう
同時進行させたいのですが メールの内容を同時にメモ帳として残したいため
ご質問の作成したメールの内容をメモ帳として残す処理を加える件につきまして、
テキストファイルの出力ということでしたら、以下のVBAにて可能です。
テキストファイル(メモ帳)書き込み処理:
※ファイルのパスはご自身の環境にあわせて変更願います。
※ファイル名はメールの件名となっています。
Open “C:\Users\*****\Documents\” & strSubject & “.txt” For Output As #1
Print #1, “件名:” & strSubject
Print #1, “本文:” & strBody
Close #1 ‘開いたファイルを閉じる
使用例:
“表示”処理のところにテキスト出力の処理を加えました。
送信時にテキスト出力したい場合は、”送信”の方へ追加してください。
Select Case Worksheets(1).Cells(intNum, 1)
Case “表示”
‘メールを表示します。
.Display
‘#テキストファイル(メモ帳)書き込み処理 追加
Open “C:\Users\*****\Documents\” & strSubject & “.txt” For Output As #1
Print #1, “件名:” & strSubject
Print #1, “本文:” & strBody
Close #1 ‘開いたファイルを閉じる
Case “送信”
.Display
.Send
Case Else
MsgBox “値が異なります。「表示」か「送信」を入力してください。”
End Select
よろしくお願いいたします。
ありがとうございます うまくいきそうです
今後も参考にさせていただきます