Excel VBA シートにある値をテキストボックスから検索してヒットしたらOutlookを作成

Question

環境：Windows10　Excel2016　Outlook2016

いつもお世話になっております。
下記の【やりたいこと】を作成したいのですが
難航しており力添え頂きたいです。
以下に詳細を記載しています。

【シート内容】
Sheet1の内容
ActiveXのTextBox1,CommandButton1
C列に氏名、D列に第一メールアドレス、E列に第二メールアドレスが存在する(約100件)
Sheet2の内容
B1にメールの件名、B2にメールの本文が記入されている

【やりたいこと】
・Sheet１のTextBox1に氏名を入力してCommandButton1をクリックしSheet1に記載されている氏名(C列)の中から同じ氏名をヒットさせる
・氏名がヒットしたらTOに第一メールアドレス(D列)、CCに第二メールアドレス(E列)、件名にSheet2の件名(B1)、本文にSheet2の本文(B2)が入力された状態でOutlookのメール作成画面が表示されるようにしたい。
・検索がヒットしなければ"存在しません"と表示する

下記の作成したコード①と②を合わせたコードを作成しようと思いましたが上手くいきませんでした。
なので一旦、別々のコードを作ってみました。
作成したコード①と②を合わせたいです。初心者なので間違っているコードがあると思いますがご教授お願い致します。


【作成した内容①TextBox1に入力した氏名と同じ氏名をSheet1のC列からヒットさせる】
Private Sub CommandButton1_Click()

Dim F As Variant

Set myRange = ActiveSheet.Range("C10:E100")
Set rngSearch = myRange.Find(What:=TextBox1.Value, LookAt:=xlPart)

If TextBox1 = "" Then
    MsgBox "氏名を入力してください。"
Else
    If Not rngSearch Is Nothing Then
        rngSearch.select
　　Else
　　    MsgBox "見つかりませんでした。"
    End If
End If

End Sub

【作成した内容②メール作成】
Sub OUTLOOK()

Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem
Dim wsMail As Worksheet

Set objOutlook = New Outlook.Application
Set wsMail = ThisWorkbook.Sheets("送信内容")

Set objMail = objOutlook.CreateItem(olMailItem)

With wsMail

    objMail.To = wsList.Cells(i, 4).Value
    objMail.CC = wsList.Cells(i, 5).Value
    objMail.Subject = .Range("B1").Value
    objMail.BodyFormat = olFormatPlain
    objMail.Body = .Range("B2").Value

    objMail..Display
End With

Set objOutlook = Nothing
MsgBox "送信完了"

End Sub

end-u · Accepted Answer

＞シート上でTOのアドレス列を増やす場合
例えば？具体的には？

今は
＞TOに第一メールアドレス(D列)、CCに第二メールアドレス(E列)
これを
TOに第一メールアドレス(D列)、第二メールアドレス(E列)、CCに第三メールアドレス(F列)
ってことでしょうか

Selectionとの位置関係で
objMail.To = Selection.Offset(, 1).Value
objMail.CC = Selection.Offset(, 2).Value
ここを修正すれば良いです

end-u · Answer

とにかく動かすための最低限のアドバイスなら

Private Sub CommandButton1_Click()
　　Dim F As Variant
　　'Dim myRange As Range
　　'Dim rngSearch As Range

Set myRange = ActiveSheet.Range("C10:E100")
　　Set rngSearch = myRange.Find(What:=TextBox1.Value, LookAt:=xlPart)
　　If TextBox1 = "" Then
　　　　MsgBox "氏名を入力してください。"
　　Else
　　　　If Not rngSearch Is Nothing Then
　　　　　　rngSearch.Select
　　　　　　'【↓この行挿入】
　　　　　　Call Outlook
　　　　Else
　　　　　　MsgBox "見つかりませんでした。"
　　　　End If
　　End If
End Sub

Sub Outlook()
　　Dim objOutlook As Outlook.Application
　　Dim objMail As Outlook.MailItem
　　Dim wsMail As Worksheet

Set objOutlook = New Outlook.Application
　　Set wsMail = ThisWorkbook.Sheets("送信内容")
　　Set objMail = objOutlook.CreateItem(olMailItem)
　　With wsMail
　　　　'【↓下２行修正】CommandButton1_ClickでSelectしてるのでそれを使う
　　　　objMail.To = Selection.Offset(, 1).Value　　'wsList.Cells(i, 4).Value
　　　　objMail.CC = Selection.Offset(, 2).Value　　'wsList.Cells(i, 5).Value
　　　　objMail.Subject = .Range("B1").Value
　　　　objMail.BodyFormat = olFormatPlain
　　　　objMail.Body = .Range("B2").Value
　　　　objMail.Display
　　End With
　　Set objOutlook = Nothing
　　MsgBox "送信完了"
End Sub

他アドバイス
1)Option Explicit
　https://e-vba.com/optionexplicit/
2)「Sub Outlook()」のようにオブジェクトの名前をマクロ名にするのは違和感
3)検索範囲『氏名(C列)の中から』なら.Range("C10:C100")でいいはず
4)Selectionという状況依存ではなく引数を別プロシージャに渡したほうが良いかも
　https://www.officepro.jp/excelvba/sub/index3.html
　またはモジュール単位の変数を使うという方法もありますけどね
　https://www.sejuku.net/blog/74359

Qchan1962 · Answer

こんにちは、コード自体を整理しましょう。省略されてい要る部分は良いとして
表示した変数の出所は、明確にした方が良いですよ。判断に困ります。

Option Explicit　を付けて変数宣言してみてはいかがでしょう。デバックなどにも良いかと

不明な変数　F　wsList　i 
Fは、想像できませんが、wsListは
Set wsList = ThisWorkbook.Sheets("Sheet1")
Sheets("送信内容")は、質問に出てきませんので
Set wsMail = ThisWorkbook.Sheets("Sheet2")
ですかね？

＃１end-u　さんがすでにアドバイスをされているので、

①番だけ
Private Sub CommandButton1_Click()
Dim F As Variant　’必要ありますでしょうか？
　　Dim myRange As Range　’宣言された方が良いと思います
　　Dim rngSearch As Range　’宣言された方が良いと思います
　　Dim Mail_TO As String, Mail_CC As String '例で使用

Set myRange = ActiveSheet.Range("C10:E100")　’名前の検索範囲はC列のみで良いのでは？例：ActiveSheet.Range("C10:C100")
Set rngSearch = myRange.Find(What:=TextBox1.Value, LookAt:=xlPart)　’オブジェクトが必要では？例：Set rngSearch = myRange.Find(What:=ActiveSheet.TextBox1.Value, LookAt:=xlPart)
If TextBox1 = "" Then　　’これも上と同じ。例：If ActiveSheet.TextBox1 = "" Then
MsgBox "氏名を入力してください。"
Else
If Not rngSearch Is Nothing Then
rngSearch.select　’ここでRangeオブジェクトを取得できるので　TOやCCを取得してはいかがでしょう。
Mail_TO = rngSearch.Offset(, 1).Value　’TOを変数に　ちなみに、TOを列挙するのなら、セミコロン+半角スペースでつないだと思います。
例：Mail_TO = rngSearch.Offset(, 1).Value & "; " & rngSearch.Offset(, 2).Value (OUTLOOK)
Mail_CC = rngSearch.Offset(, 2).Value　’CCを変数に
Call OUTLOOK(Mail_TO,Mail_CC)

Else
　　 MsgBox "見つかりませんでした。"
End If
End If
End Sub

Call OUTLOOK(Mail_TO As String,Mail_CC As String)
.
.
objMail.To = Mail_TO
objMail.CC = Mail_CC
.
.
.
Set objOutlook = Nothing
End Sub

Excel VBA シートにある値をテキストボックスから検索してヒットしたらOutlookを作成

とにかく動かすための最低限のアドバイスなら

＞シート上でTOのアドレス列を増やす場合

こんにちは、コード自体を整理しましょう。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング