Excelの値をテキスト化してメール本文に貼りつけたい

VBA Ruby 日常業務自動化

やりたいことはタイトルの通りで、その方法を探していますが、
私の知識が乏しく、どういう過程を踏んだらいいのかわかりません。。

やりたいことの詳細

上記のような表があって、日ごとにシートが分かれています。
この内容をガラケーで見られるテキストメールにして送信したいのです。

メール本文は下記のような感じ

おはようございます。本日の予定です。
●Aさん：東京　Aさんの行先1、Aさんの行先2、3、4、、、
●Bさん：東京　Bさんの行先1、Bさんの行先2、3、4、、、
●Cさん：埼玉　Cさんの行先1、Cさんの行先2、3、4、、、
・
・
・
以上です。よろしくお願いいたします。

つまり

"●" + ①　+ "：" + ② + ③

ということです。
●とか：は別のものに置き換えてもいいし、
③は7つのセルをカンマなどで結びたくて、
場合によっては7つすべてあるわけではない。


という状況。


文字列をクリップボードに貼りつけて、メールに貼る

ちょっと考えてみたんだけど、
Excelの表に入っている値を、ひとつずつ取り出してカンマで結んで、
ひとつの文字列を作ってクリップボードにコピーして、
それをメールに貼りつける。。のがいいかな。

"●" & A4 & "：" & "行先1" & "行先2"・・・

まずは一人分でそれが出来るか実験。

クリップボードへのコピー

クリップボードに文字列をコピーするには、こちらを参照しました。
[Excel VBA] クリップボードにコピーする、で体験した意外な落とし穴 - dzone. blog

クリップボードの操作をするには、
Microsoft Form 2.0 Object Library を参照するよう設定が必要なのですが、
ツール＞参照設定　を見てみても、最初は該当項目が見つかりませんでした。

http://www.relief.jp/itnote/archives/017881.php

こちらを参考にして、いちどユーザーフォームを挿入→（今回はユーザーフォームが必要ないので）解放、
とすると、自動的に参照設定されました！

もちろん、ダウンロードファイルを探して設定してもいいと思いますが。


テスト

Option Explicit

Sub schedule_copy()
    Dim CB As New DataObject
    Dim a As String
    Dim com As String
    
    com = "、"
    
    a = "●" & Range("A4") & Range("A5") & "：" & Range("B4") & com & Range("C4") & com & Range("D4")
    
    With CB
        .SetText a
        .PutInClipboard
    End With
    
End Sub

これを実行して、メーラーを立ち上げ、本文に貼りつけしてみました。

できた！

複数人分のループ化

が、フクザツでぜんぜんわからない

考え方を変える

複数人にするとすごく複雑になるので、
まず表のとなりのセルに文字列をつくって、それをくっつけて改行をいれて、コピーしました。

I4セルに下記の通り式をいれて、コピーしてAさんからFさんまでの文字列を作成。
I2セルに「、」をいれて、行先ごとに区切るようにしました。

="●" & A4 & A5 & "：" & $B4 & $I$2 & $C4 & $I$2 & $D4 & $I$2 & $E4 & $I$2 & $F4 & $I$2 & $G4 & $I$2 & H4

VBAのコードを修正

Sub schedule_copy()
    Dim CB As New DataObject
    Dim all As String
   
    all = Range("I4") & vbNewLine & Range("I6") & vbNewLine & Range("I8") & _
    vbNewLine & Range("I10") & vbNewLine & Range("I12") & vbNewLine & Range("I14")
    
    With CB
        .SetText all
        .PutInClipboard
    End With
    
End Sub

うーん、出来るけど、この式をすべてのシートにいれて隣に文字列が表示されていることを考えると煩わしいので、
やっぱりすべてVBAで解決できたほうがいいな…

修正。。やっぱりループの書き方がわからない

Option Explicit

Sub schedule_copy()
    Dim CB As New DataObject
    Dim all As String
    
    Dim A As String         'Aさん
    Dim B As String         'Bさん

    Dim com As String
        com = "、"
        
    Dim cor As String
        cor = "："
        
    
    A = "●" & Range("A4") & Range("A5") & cor & Range("B4") & com & Range("C4") & com _
    & Range("D4") & com & Range("E4") & com & Range("F4") & com & Range("G4") & com & Range("H4")
    
    B = "●" & Range("A6") & Range("A7") & cor & Range("B6") & com & Range("C6") & com _
    & Range("D6") & com & Range("E6") & com & Range("F6") & com & Range("G6") & com & Range("H6")
    
    
    all = A & vbNewLine & B 
    
    With CB
        .SetText all
        .PutInClipboard
    End With
    
End Sub

とりあえずAさんとBさんだけでかいてみました。
すごく見づらい…わかりづらい…

課題

文字列を取得するのを、AさんからFさんまでループ化したい。
配列…とか？？

というわけで今日はここまで。