Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
Outlook操作について
投稿日時: 21/12/19 02:40:30
投稿者: miraku

よろしくお願いします。
Outlookでエクセルから添付ファイルが2つまであるメールを複数送りたいと思います。
 
ブックにシートは1つで、「受信先情報」シートの1行目は空欄で、2〜8行目にデータが
あります。A2〜A8は項目でB列以降の2〜8列目にメールに反映するデータを入力します。
(1列で1件のメールデータ)
 
受信先情報シート説明
(B列以降)
2行目 To(メールアドレス入力)
3行目 cc(メールアドレス入力)
4行目 bcc(メールアドレス入力)
5行目 件名入力
6行目 本文入力
7〜8行目 添付ファイル名入力(パス含む)
 
現在添付ファイルはコードに合わせC:\tempに格納しています。添付ファイル名はセル
参照として、受信先情報シートのB列以降の7〜8行目に「C:\temp\添付ファイル1.zip」
のように入力していますが、現在のコードを実行すると送信直前のメールプレビュー画面
(添付もあり)がでてきてうまく動作しています。(試しにBとC列の2件分入力)
 
これを変更し、添付ファイルの保存先を本マクロ保存ブックと同じ場所にしても動作
するようにしたいです。添付ファイルを本マクロ保存ブックと同じ場所にし、受信先
情報シートのB列以降の7〜8行目に、「ThisWorkbook.Path & "\添付ファイル1.zip"」
と変更し、コード実行しましたが「実行時エラー ファイル名またはフォルダー名が
ただしくありません。」となりデバックすると「Mail.Attachments.Add Attach1」が
黄色表示されます。メールを添付する部分でエラーとなるようです。
 
現在のコードは以下です。
(ネットの「梅屋ラボ」さんの「Outlookでメールを一括送信する方法」のサンプル
コードを利用させていただき、解読して自分のやりたい必要最小限の内容に書き換え
たのものです)
 
Sub Sample()
  
    Dim Outlook As Outlook.Application
    Dim Mail As Outlook.MailItem
    Dim Ws1 As Worksheet
    Dim Attach1 As String '添付ファイル1
    Dim Attach2 As String '添付ファイル2
    Dim i As Long
     
    Set Outlook = New Outlook.Application
    Set Ws1 = ThisWorkbook.Sheets("受信先情報")
     
    '受信先情報入力列が空になるまで繰り返す
    i = 2
    Do Until Ws1.Cells(2, i) = ""
         
        'Outlookメールを作成
        Set Mail = Outlook.CreateItem(olMailItem)
         
        '送信情報を設定
        With Ws1
            Mail.To = .Cells(2, i) '送り先
            Mail.CC = .Cells(3, i) 'cc
            Mail.BCC = .Cells(4, i) 'bcc
            Mail.Subject = .Cells(5, i) '件名
            Mail.Body = .Cells(6, i) '本文
            Attach1 = .Cells(7, i).Value '添付ファイル1
            Attach2 = .Cells(8, i).Value '添付ファイル2
             
            '添付ファイル判定
            If Attach1 <> "" Then
                Mail.Attachments.Add Attach1
            End If
             
            If Attach2 <> "" Then
                Mail.Attachments.Add Attach2
            End If
             
            Mail.BodyFormat = olFormatPlain 'メールの形式
             
        End With
 
            'メールプレビュー
            Mail.Display
 
        i = i + 1
    Loop
     
    'オブジェクトを初期化
    Set Outlook = Nothing
  
End Sub

回答
投稿日時: 21/12/19 08:42:33
投稿者: simple

要するに、Attach1が実在のファイルになっているかを確認すればよいわけですね。

Debug.Print Attach1
Debug.Print Len(Attach1)
Debug.Print Dir(Attach1)
などと地道に確認するよりほかにないでしょう。
こればっかりは、他人には検証できません。

投稿日時: 21/12/19 08:47:51
投稿者: miraku

質問の内容に以下訂正等がありましたのでご報告いたします。
 
訂正前 A2〜A8は項目でB列以降の2〜8列目にメールに反映するデータを入力します。
訂正後 A2〜A8は項目でB列以降の2〜8行目にメールに反映するデータを入力します。
 
また、前回記載したコードを最初に実行すると、コンパイルエラーとなりますので
事前に次に設定をしています。(メールソフトを起動するためと思います)
VBA画面のツール→参照設定→「Microsoft Outlook 16.0 Object Library」にチェック
→OK
 
この設定は、本ブックのみで有効のようです。

回答
投稿日時: 21/12/19 09:23:40
投稿者: simple

通じていないようなので、もう一度。
 
エラーになったときに、
イミディエイトウインドウで
? Dir(Attach1)
としてみてください。
添付ファイル1.zip と表示されれば、実在していることになります。
 
""が返ってきて何も表示されない(""が表示される)のであれば、
それは指定が間違っているのです。
 
ありがちなのは、前後に余計なスペースが入っていることですね。
想定するパス名と文字数を比較したりしてみては?(それがLen(Attach1)の趣旨)
そちらでよく調べてみてください。こちらではわかりませんよ。

投稿日時: 21/12/19 09:25:33
投稿者: miraku

simple様 お返事ありがとうございました。
 
Debug.Print Attach1
Debug.Print Len(Attach1)
Debug.Print Dir(Attach1)
を掲載コードのAttach2 = .Cells(8, i).Valueの次の行に追加しました。
コードを実行したら、「実行時エラー52ファイル名または番号が不正です」となり、
デバッグするとDebug.Print Dir(Attach1)が黄色くなりました。
イミディエイトウィンドウには、
ThisWorkbook.Path & "\添付ファイル1.zip"
 34
と表示されました。
 
宜しくお願い致します。

回答
投稿日時: 21/12/19 09:34:23
投稿者: simple

引用:
イミディエイトウィンドウには、
ThisWorkbook.Path & "\添付ファイル1.zip"
 34
と表示されました。

ああ、よく読んでいませんでした。ちょっと唖然とする話でした。
 
それだと、まさに、
ThisWorkbook.Path & "\添付ファイル1.zip"
という名前のファイル名を探しに行きますよね。
そんなファイルありませんよ。
 
セルには、
\添付ファイル1.zip
とだけを書き、
Attach1 = ThisWorkbook.Path & .Cells(7, i).Value '添付ファイル1
としてはどうですか?
 
添付ファイル1.zip
とだけを書き、
Attach1 = ThisWorkbook.Path & "\" & .Cells(7, i).Value '添付ファイル1
のほうがよいかもしれません。

投稿日時: 21/12/19 10:17:38
投稿者: miraku

simple様
 
ありがとうございました!解決致しました。感謝感激です!
すっと悩んでいたので助かりました。
 
以下の方法でうまくできました。
 
>添付ファイル1.zipとだけを書き、
>Attach1 = ThisWorkbook.Path & "\" & .Cells(7, i).Value '添付ファイル1
>のほうがよいかもしれません。
 
VBAの外部ファイルを扱う部分は自分には難しいです(泣)
 
今後は、B列以降の4行目のBCCに、数式で複数のアドレスを「;」でつなげる予定です。
(実際はグループ毎にシートを分けるのでC列以降は入力しないと思います)
現在は、Outlookのアドレスのグループ化機能で複数アドレスをBCCに呼出して送信して
いますが今後は、エクセルのコードだけで同じことができ、この方がメール操作が早いと
思うのでコードの方法に変更したいと思います。(定期的な作業のため)
 
メールは一度に99件までアドレス送信可能ですが、変更後はエクセルのB列以降の4行目
のBCC箇所に数式で工夫してつなげたいと思います。(グループは10種類程度あり、ある
グループの最大は100を超え、99を超える場合はメールが送信エラーとなるのでグループ
を分けています)