VBA・マクロに関する私の考え

最近、

自分には一文の得にもならない、純粋に他人のためのマクロ

を作らされることが多くなって、改めて思うんですが、やっぱり、

マクロなんて自分のためにのみ作るもんだよな！

ってことですよ。

ウチの上司は、純粋に「単純作業をマクロ化することは良きことなり！」と信じている様子で、それが業務改善だと考えている様子。

でもねえ……。

そんなことしても業務改善にはならないと思うんですよ。

ウチの職場って旧弊なところで、結構マジで「給料は苦痛に耐えた対価。苦しんでいるからこそ給料をもらう資格がある」みたいな価値観が支配的なんですわ。そんな環境で一般人がヒイコラ言ってやってる作業をマクロで一瞬で終わらせてごらんなさいよ、逆にバカにされるよ。「アイツは苦労が足りん！」みたいな感じで。

だいたい、「Excelを使った面倒な作業の繰り返し」っつっても、関数やマクロを覚えるでもなく時間かけてやってるということは、当の本人にとっては「それで満足」なんですよ。だから放っといてあげたらいい。めんどくせーなーと思うんなら自分で工夫すりゃいいんだし。

そこに、「こんな便利なものがありますよー」ってしゃしゃり出てどうすんだろ。どれほど便利でも、仕組みも分からず使うだけの人は、不具合があったら「仕組みが分かってる人」に全力でもたれかかるしかないんだからさ。「仕組みが分かってる人」は一人負けじゃん。

それに、「『仕組みが分かってる人』が自分が使うために作るマクロ」っていうのは簡単なんですよ。不具合があっても自分で対応できるし。

だけど、「『仕組みも分からず使うだけの人』が使うために作るマクロ」ってのはめちゃくちゃ大変です。経験のある人なら分かると思うけど、自分にとってそんなマクロを作るメリットは皆無なのに手間だけはアホみたいにかかる。しかも、実際使われ出したら、

そんなしょうもないこといちいち聞いてくんなボケ！

というレベルのことや、

節子……、それはマクロ以前の問題や……

というレベルのことまで安易に聞いて来られるようになる。

早い話、マクロを作る側には何のメリットもないのにデメリットばかりが大きくなる。

さらに、そのマクロが奇跡的に

どんなシロートが使っても不具合が起きない堅牢性を持ち、なおかつ作業量を劇的に減らすチョー便利なモノ

だったとしよう。どういうことが起こるか。

単純な話である。

「手間も時間もかかる大変な仕事」が「アホでも一瞬でできる楽勝な仕事」に変わる

だけのことだ。

たぶん、「今まで３時間かかっていた仕事が５分で片付くようになったんだから、２時間55分早く仕事を上がっていいよ♪」という風になる職場ってのは存在しないんじゃないかね？

逆に、浮いた時間に新たな仕事が詰め込まれて、却ってしんどくなるんじゃねえの（仕事の密度が上がっとるんだからさ）？

本気で「業務改善」しようってんなら、やっぱり人事とか予算とかどうこうできるレベルの層が「給料は苦痛に耐えた代償」という価値観を脱して、せめて「給料は価値を生み出した対価」という価値観になってもらわないとダメなんであって、そこを変えずに下々が「業務改善」策を提案しているようじゃ話にならないんですよね。

結論

マクロやVBAのスキルというのは、「給料＝苦痛に耐えたことへの対価」という価値観による攻撃から身を守るための手段だと思うようになりました。

マクロやVBAのスキルを身につけたら、ひたすら自分のためにのみ使うようにしましょう。決して「単純作業にやたら時間をかけている同僚を助けてあげよう」などと思わないことです。マクロ使いから見ると、「単純作業にやたら時間をかけている同僚」は「メチャクチャ気の毒な人」に思えることでしょう。しかし、本人はさほど不幸だと思っていないはずです。むしろ、「長時間の単純作業に黙って耐えてるオレ、すごくね？」ぐらいに思っています。

「みんなのために」などという気を起こさないようにしましょう。あなたはあなたのためにのみマクロを作ればいいんです。それが一番効率がいいんですから。

別途給料がもらえる、というなら別ですが、そうでもないなら、決して私の二の舞にならないようにしましょうね！

私のVBAとの出会い

もともと、私がVBAを覚えたのは、「ExcelでWordの差込印刷みたいなことってできないんだろうか」とggったことがきっかけだ。

一応、番号を入力したらそれに対応して内容が変化するように数式が埋め込まれていたんだけど、それが全部手作業だった。全てプリントアウトする必要があったんだけど、それまでの担当者は、「セルに"1"を入力して印刷、"2"を入力して印刷、"3"を入力して印刷……」ということを200回ぐらい繰り返していたらしい。

「バカヤロー、そんなアホなこと、デキッコナイス！　だいいち、Wordでフツーにできることなら、誰かがExcelでもやる方法を考えているはず！　やったるで！」とggりまくって、

マクロというものを使えばできるらしい

と知ったのが、VBAとの出会いだった。そのとき既に40歳を超えていたんですけどｗ

久しぶりにggってみたんだが、当時の私がたどり着いたのはこのページ。懐かしいな。

Sub 個人票印刷()
  Range("個人番号") = Range("自")
  Do While Range("個人番号") <= Range("至")
    Sheets("個人票").PrintOut
    Range("個人番号") = Range("個人番号") + 1
  Loop
End Sub

これがサンプルコード。もちろん、このままでは使えないから、解読して自分の業務に合わせてアレンジする必要があった。

今見ると、非常にイヤな書き方のコードだが、これが当時の私には良かった。とにかく解読はできたから。

んで、実験。ポチッとボタンを押して実行したらアンタ、プリンタから凄まじい勢いで出てくるｗ

ええ、ものの５分もたたずにできちゃいましたよ。

「これがマクロってものか」……すぐに本屋へ走りましたよ。そこで出会ったのが立山秀利さんの『実例で学ぶExcelVBA』。この本を読んで、データ転記マクロを自作してから一気にハマりました。

それからはまあ、自分の業務に関することでおよそ自動化できそうなものはほとんど自動化していきましたよ。おかげで、残業自慢しとるようなヤツばっかりの職場内で私だけが目に見えて残業や休日の自主的ボランティア出勤が減っていきましたよ（まあ、実際にはそんなに単純なものでもなくてあれこれとうっとうしこともあったんですけど、その辺は省略）。

まあ、VBAと出会えてよかったなあと、そういう話です。つまんない話ですみません。

追伸

いつの間にか「読者」というパラメータが「8」になっていてびっくりしています。

しかも、その中にt-homという人がいるではないか!!!!!!!!　「クラスモジュールって何じゃいな？？？」と思っていた頃に（っていうか今でもですけど）この方のブログには大変お世話になっておりますし、今読んでもまだ理解不能なところが結構あるので、私にとってはまさに雲の上の人なんですけど……。

私は、この「はてな」というものの仕組みがまるで分かっとらんので、どう対応して良いのかサッパリ分かりません。

もし、このブログの読者になってくださっている方で、「何だよコイツは！　人様が読者になってやってるのに何の挨拶もなしかよ！　失礼なヤツやのー」とお怒りの方がおられましたら、私がどのように振る舞えば良いのか教えてください。

小さなクラスを作る(3)～ファイルを選択させる

ファイルを選択させるやつも作ってみた。

例によってクラスモジュールを挿入。オブジェクト名はFilePickerにした。

クラスモジュールのコードは下記の通り。

Option Explicit

'フィールド
Private gotFileFullPath_ As String
Private isCancelled_ As Boolean

'アクセサ
Public Property Get gotFileFullPath() As String
  gotFileFullPath = gotFileFullPath_
End Property
Public Property Get gotFileName() As String         '……(1)
  gotFileName = Right(gotFileFullPath_, _
                     (Len(gotFileFullPath) - InStrRev(gotFileFullPath, "\")))
End Property
Public Property Get isCancelled() As Boolean
  isCancelled = isCancelled_
End Property

'コンストラクタ
Private Sub Class_Initialize()
  isCancelled_ = False
End Sub

'メソッド
Public Sub showFilePicker(ByVal titleStr As String)
  Dim fileFullPath As Variant                       '……(2)
  fileFullPath = Application.GetOpenFilename(Title:=titleStr)
  If fileFullPath = False Then                      '……(3)
    isCancelled_ = True
    gotFileFullPath_ = ""
  Else
    isCancelled_ = False
    gotFileFullPath_ = fileFullPath
  End If
End Sub

もはやほとんど解説の必要もないと思うので、要点だけ。

• (1)は、gotFileNameプロパティの取得だが、Privateの仮変数を使っていない。フルパスが分かったら自ずと決まるものなので、必要なときのみ演算するようにした方がいいと思いました。「アホか」と思った上級者の方、ぜひご指摘ください。
• (2)でGetOpenFileNameの結果を受け取る変数をVariant型にしている。これは、ファイル選択ダイアログで[キャンセル]が選ばれると、Booleanの値を返すため。Variantとか、雑な仕事のような気がしてあんまり使いたくないんだけど。
• (3)はだから、[キャンセル]が選ばれたとき、ということ。isCancelled_をTrueにして、gotFileFullPath_には""を入れている。

標準モジュールに下記のコードを書いて実行。

Public flp As FilePicker
Sub test03()
  Set flp = New FilePicker
  With flp
    flp.showFilePicker "ファイルを選べ。"
    If .isCancelled = False Then
      MsgBox "ファイルのフルパス：" & .gotFileFullPath & vbCrLf & _
             "ファイル名：" & .gotFileName
    Else
      MsgBox "キャンセルされとるよ。"
    End If
  End With
End Sub

ファイルを選ぶと、

gotFileFullPathプロパティには選んだファイルのフルパスが、gotFileNameプロパティには選んだファイルのファイル名が、それぞれセットされていることが分かる。

引数でFileFilterとかMultiSelectに対応できるようにするなど、改良次第でかなり便利になりそうだ。

コチラのページ（mougモーグ）を参考に、追々改良していこう。

@akashi_keirin on Twitter

小さなクラスを作る(2)～他のアプリケーションの起動チェック

LotusNotesのメールをExcelVBAで自動作成するときは、そもそも自分のアカウントでNotesに接続していないといけない。

「でも、他のアプリケーションが起動しているかどうかなんて、どうやって判定できるんだろ？」とggっていてわりかしあっさりとたどり着いたのが、Office TANAKA「実行中のタスク一覧（非API）」という記事。

なるほどね～、と思ったので、即導入し、今に至る。

今回は、小さなクラスシリーズの一環として、ノーツが起動しているかどうか判定するクラスを作ってみた。需要があるのかどうかは分かりませんがｗ

クラスモジュールを挿入して、オブジェクト名を「NotesStartedChecker」とした。英語がおかしい気もするけど。

クラスモジュールに書いたコードは次の通り。

Option Explicit

'フィールド
Private isStarted_ As Boolean

'アクセサ
Public Property Get isStarted() As Boolean                '……(1)
  isStarted = isStarted_
End Property

'コンストラクタ
Private Sub Class_Initialize()                            '……(2)
  isStarted_ = False
End Sub

'メソッド
Public Sub checkNotesIsStarted(ByVal alertMessage As String)
  Dim objWord As Object                                   '……(3)
  Set objWord = CreateObject("Word.Application")          '……(4)
  If objWord.Tasks.Exists("Lotus Notes") = True Then      '……(5)
    isStarted_ = True                                     '……(6)
    '実行中タスクの中にLotus Notesがあれば、何もしない。
    objWord.Quit                                          '……(7)
    Set objWord = Nothing                                 '……(8)
  Else
    '実行中タスクの中にLotus Notesがなければ、メッセージを表示して終了
    isStarted_ = False
    MsgBox alertMessage, vbExclamation
    objWord.Quit
    Set objWord = Nothing
    Exit Sub
  End If
End Sub

例によってコードの解説。

• (1)はisStartedプロパティのgetter。Notesが起動しているかどうかを格納する。これぐらいしか持たせるプロパティが思いつかない。
• デフォルト値がFalseなので、(2)は不要だと思いますが、まあ、ないと寂しいのでｗ
• (3)でWordオブジェクト用の変数を用意する。CreateObjectを使うので、参照設定は不要。よって、不本意ながらObject型。
• (4)でWordのインスタンスを変数にセット。
• (5)がミソ。WordオブジェクトのTasksコレクションの中に、Lotus Notesが入っているかどうかをExistsメソッドで調べる。入っていたらTrueが返る。
• Tasksコレクションの中にLotus Notesが入っていたら、すなわち、Lotus Notesが起動中なら、(6)でisStarted_をTrueにする。
• Wordオブジェクトはもはや用済みなので、(7)で終了させて、
• (8)でオブジェクト変数を解放する。
• Lotus Notesが起動していない場合は、メッセージを表示する。

とまあ、こんな感じ。

標準モジュールに下記のコードを書いて実行してみよう。

Public nsc As NotesStartedChecker
Sub test02()
  Set nsc = New NotesStartedChecker
  Dim alertMessage As String
  alertMessage = "Lotus Notesは起動していません。" & vbCrLf & _
                 "    　＿＿＿＿＿＿＿＿_" & vbCrLf & _
                 "　／　　　　　　　　  ＼ " & vbCrLf & _
                 "／　／・＼　　／・＼　  　＼" & vbCrLf & _
                 "|　　￣￣　　　￣　　 　　|　ち～んｗ" & vbCrLf & _
                 "|　　　　（＿人＿）　　 　|" & vbCrLf & _
                 "|　　　　　＼ 　   |   　    　 |" & vbCrLf & _
                 "＼　　　　 　＼＿|　　   ／"
  nsc.checkNotesIsStarted alertMessage
End Sub

意味もなく、引数にやたら長い文字列を持たせたことは、許してください。

Lotus Notesは職場のPCにしか入っていないので、自宅のPCで実行すると当然こうなる。

CreateObjectの引数をメソッドの引数として渡すようにしたら、汎用性が高まるのかな。

@akashi_keirin on Twitter

小さなクラスを作る(1)～フォルダ選択機能

twitterのフォロワーさんからのアドヴァイス。

曰く、よく使う機能はクラスにしといた方がいいぜと。なるほど。今まで、よく使う処理をSubやFunctionにまとめて一つのモジュールに集めておいて、ライブラリ的に使っていたけど、結局、

どこに何を書いていたのか忘れる

というマヌケなことになっていた。

その点、クラスだとかなり擬人的なので、忘れにくいかも知れん。

で、さっそくやってみた。私の場合、ユーザーにフォルダを選ばせるという処理をよく使うので（しかも、やり方をよく忘れるｗ）、そんな役割のクラスを作ってみる。

クラスモジュールに「FoldePicker」という名前を付けて、以下のコードを書く。

Option Explicit

'フィールド
Private gotFolder_ As String
Private isCancelled_ As Boolean

'アクセサ
Public Property Get gotFolder() As String
  gotFolder = gotFolder_
End Property
Public Property Get isCancelled() As Boolean
  isCancelled = isCancelled_
End Property

'コンストラクタ

'メソッド
Public Sub showFolderPicker()
'機能：フォルダ選択ダイアログボックスを表示し、選択されたフォルダパスを
'      gotFolderプロパティにセットする。引数なし。
'　　　キャンセルされると、isCancelledプロパティをTrueにする。
  With Application.FileDialog(msoFileDialogFolderPicker)  '……(1)
    If .Show = True Then                                  '……(2)
      gotFolder_ = .SelectedItems(1)                      '……(3)
    End If
  End With
  If gotFolder_ = "" Then                                 '……(4)
    MsgBox "キャンセルされました。"
    isCancelled_ = True                                   '……(5)
  Else
    isCancelled_ = False                                  '……(6)
  End If
End Sub

こんな感じ。

Application.FileDialogオブジェクト（？）で、引数に「msoFileDialogFolderPicker」を指定しているので、フォルダ選択ダイアログオブジェクトを指すってことでしょうか。

• (1)でWithを使っているので、以下はEnd Withまでフォルダ選択ダイアログオブジェクトに対する操作。
• (2)のIf文の条件の中でShowメソッドを実行。ユーザーがダイアログで[ＯＫ]ボタンを押したら、条件成立。
  コチラによると、ファイル ダイアログ ボックスを表示して、ユーザーが [アクション] ボタン (-1) または [キャンセル] ボタン (0) を押したかどうかを示す Long を返します。との由。
  つまり、[ＯＫ]ボタンが押されたら「-1(True)」が返るということ。
• ユーザーがダイアログで[ＯＫ]を押したら、(3)でSelectedItemsプロパティ（＝選択したフォルダのフルパス）が変数gotFolder_に代入される。
  フォルダ選択ダイアログでは、複数選択ができないので、SelectedItemsのインデックスは「1」を指定する。
• (4)。キャンセルされると、gotFolder_には何も代入されていないことになる。
• (5)でisCancelled_をTrueにする。
• (6)は、gotFolder_にフォルダパスがセットされている場合なので、isCancelled_をFalseにしておく。
  一旦キャンセルした後、再度showFolderPickerメソッドを実行したら、isCancelled_がTrueのままになってしまうので。

動作確認のため、標準モジュールに以下のコードを書いて実行。

Public fdp As FolderPicker
Public Sub test()
  Set fdp = New FolderPicker                   '……(1)
  With fdp                                     '……(2)
    .showFolderPicker                         '……(3)
    If .isCancelled = True Then               '……(4)
      MsgBox "ズバリ、キャンセルしたでしょう！"
    Else
      Debug.Print "gotFolder = " & .gotFolder
      Debug.Print "Len(.gotFolder) = " & Len(.gotFolder)
      Debug.Print "InStrRev(.gotFolder, ""\"") = " & InStrRev(.gotFolder, "\")
      Debug.Print "Right(.gotFolder, Len(.gotFolder) - InStrRev(.gotFolder, ""\"")) = " & _
                  Right(.gotFolder, Len(.gotFolder) - InStrRev(.gotFolder, "\"))
      MsgBox "ズバリ、あなたが選んだフォルダは、「" & _
             Right(.gotFolder, Len(.gotFolder) - InStrRev(.gotFolder, "\")) & _
             "」フォルダでしょう！"           '……(5)
    End If
  End With
End Sub

一応説明。

• (1)でFolderPickerクラスのインスタンスを生成。
• ここからは、全てインスタンスfdpへの操作なので(2)でWithでまとめる。
• (3)で、showFolderPickerメソッドを実行。
• (4)。isCancelledプロパティがTrueだったら、メッセージを表示。
• (5)。isCancelledプロパティがTrueでなかったら、フォルダ名を表示。
  フォルダ名だけを切り出す処理はちょっとややこしいので、後で。

んで、実行。

フォルダ選択ダイアログが表示され……、

フォルダを選んで[ＯＫ]をクリックすると……、

ほれ、この通り、選んだフォルダ名が表示された。

キャンセルすると、……

キャンセルしたことがバレる仕様ｗ

ちなみに、フォルダ名だけを切り出すのはこんなカラクリ。

「フォルダ名は、フルパスのうち一番右にある「\」から右の文字列である」という考え方で切り出している。

今回の例だと、フルパスが

E:\個人用\ち～んw

の11文字。

一番右の「\」が前から7文字目。これはInStrRev関数で求めることができる。

11から7を引くと、4。よって、Right関数で右から4文字を抜き出してやると、めでたくフォルダ名「ち～んw」が得られる。

……とまあ、こんな感じでユーザーにフォルダを選択させてフォルダのフルパスを得るためのクラスを作ってみたけど、これで良いのだろうか……？

玄人の意見求む！

自作クラスのプロパティに配列をセットする

VBAを使って、ExcelからLotusNotesのメールを送るマクロ。ずいぶん前に作った素人丸出しのマクロだったから、いっそクラス・モジュールの練習も兼ねて作り直してみようと思い立った。

基本は、上の画像のようなワークシートに必要な値を入れ、Ｂ列の番号のところを選択した状態で実行するものとする。

送り手、つまりユーザの情報は、「ユーザ情報」というシートを別に作って、

こんな感じで管理しているものとする。

列数が多いので、まずは、標準モジュールの宣言セクションで、

Public Enum colNum
  isSent = 1
  numOf
  sendTo
  mailTo
  CC
  BCC
  mailSubj
  belongsTo
  jobTitle
  personName
  p01
  p02
  p03
  p04
  p05
  p06
  p07
  p08
  p09
  p10
  att01
  att02
  att03
  att04
  att05
  att06
  att07
  att08
  att09
  att10
  returnReceipt
End Enum

列挙体で列名を定義しておく。これで、Cellsプロパティでセルを指定するのがかなり楽になる。異様に縦が長くなるのはまあ仕方がないと割り切ろう。

で、次にクラス・モジュールで、メールそのものを表すクラスを作りたい。送信先アドレスとか、メールの件名なんかは単なる文字列だから、コンストラクタで単純に代入するだけで済むが、メール本文とか、添付ファイルのフルパスなんかは、LotusNotesメール作成時の扱い（１要素づつappendTextとかaddNewLineで書き込んでいく）からして配列として持たせておきたい。また、そうしておくことで他のメーラー（Thunderbirdとか）への拡張も可能だから。

今回は、配列として持たせたい３つのプロパティについて、自身の覚書も兼ねて残しておく。

ちなみに、コチラを参考にさせていただきました。ありがとうございました。

まず、クラス・モジュールに、フィールド部分を並べる。

'クラス名は"CreatedMail"としています。
Private baseCell_ As Range
Private mailTo_ As String
Private CC_ As String
Private BCC_ As String
Private mailSubject_ As String
Private belongsTo_ As String
Private jobTitle_ As String
Private personName_ As String
Private mailBody_() As String         '……(1)
Private numOfBody_ As Integer
Private attFiles_() As String         '……(2)
Private numOfAttFiles_ As Integer
Private returnReceipt_ As String
Private senderData_(1 To 9) As String '……(3)

メールが持つ属性を列挙している。１個目の「baseCell」は、マクロ実行時にユーザが選んでいるセルを格納する。Ｂ列の番号のところを選んでマクロを実行するようにし、その列のデータに基づいてメールを作成することにする。

(1)～(3)が配列にするプロパティ。(1)と(2)は、その時々で要素数が変わるので、カッコ内は空白。(3)は要素数が決まっているのでカッコ内に「1 to 9」と記述している。

• (1)は、メール本文を格納する配列。10段落まで設定可能。
• (2)は、添付ファイルのフルパスを格納する配列。10個まで設定可能。
• (3)は、送信者、すなわちユーザのデータを格納する配列。これは、上の４つめの画像のとおり、項目が９つあるので、要素数を９に固定している。添え字部分を「(1 to 9)」と書く、というのが特徴的ですな。

重要なのはここから。

まずは、上記の３つのプロパティのみ、アクセサ部分のコードを挙げる。

'mailBodyプロパティ
Public Property Get mailBody(ByVal i As Integer) As String
  mailBody = mailBody_(i)
End Property
'attFilesプロパティ
Public Property Get attFiles(ByVal i As Integer) As String
  attFiles = attFiles_(i)
End Property
'senderDataプロパティ
Public Property Get senderData(ByVal i As Integer) As String
  senderData = senderData_(i)
End Property

どうやら、プロパティを配列にした場合、値を取得するためのProperty Getプロシージャに配列の添え字を渡して、その添え字に対応する要素がプロパティの値としてセットされる、という処理の流れになっているらしい。右辺にのみ添え字があるのも、そういうカラクリなんだろうな。プロパティの値が参照されたときだけ値をセットすりゃいいんだから、プロパティそのものが複数の値を配列として保持しておく必要はない、ということなんだろう。

今回は値の取得のみが可能なプロパティにしているので、Letの場合の書き方は割愛する。それはまた機会があれば……。

基本的に、この点にさえ気をつけていれば、クラスのプロパティを配列として扱うことはできそう。割と簡単なんだなー。

後は、クラス・モジュールのコードを全部載っけとこう。

Option Explicit
'クラスフィールド
Private baseCell_ As Range
Private mailTo_ As String
Private CC_ As String
Private BCC_ As String
Private mailSubject_ As String
Private belongsTo_ As String
Private jobTitle_ As String
Private personName_ As String
Private mailBody_() As String
Private numOfBody_ As Integer
Private attFiles_() As String
Private numOfAttFiles_ As Integer
Private returnReceipt_ As String
Private senderData_(1 To 9) As String

'アクセサ
Public Property Get baseCell() As Range
  Set baseCell = baseCell_
End Property
Public Property Get mailTo() As String
  mailTo = mailTo_
End Property
Public Property Get CC() As String
  CC = CC_
End Property
Public Property Get BCC() As String
  BCC = BCC_
End Property
Public Property Get mailSubj() As String
  mailSubj = mailSubj_
End Property
Public Property Get belongsTo() As String
  belongsTo = belongsTo_
End Property
Public Property Get jobTitle() As String
  jobTitle = jobTitle_
End Property
Public Property Get mailSubject() As String
  mailSubject = mailSubject_
End Property
Public Property Get personName() As String
  personName = personName_
End Property
Public Property Get mailBody(ByVal i As Integer) As String
  mailBody = mailBody_(i)
End Property
Public Property Get numOfBody() As Integer
  numOfBody = numOfBody_
End Property
Public Property Get attFiles(ByVal i As Integer) As String
  attFiles = attFiles_(i)
End Property
Public Property Get numOfAttFiles() As Integer
  numOfAttFiles = numOfAttFiles_
End Property
Public Property Get returnReceipt() As String             '……(※)
  returnReceipt = returnReceipt_
End Property
Public Property Get senderData(ByVal i As Integer) As String
  senderData = senderData_(i)
End Property

'コンストラクタ
Private Sub Class_Initialize()
  Set baseCell_ = ActiveCell                              '……(1)
  Dim n As Integer  'カウント用変数
  Dim baseRow As Long
  baseRow = baseCell_.Row
  With baseCell_.Parent
    '送信相手の基本情報を各プロパティにセット             '……(2)
    mailTo_ = .Cells(baseRow, colNum.mailTo).Value
    CC_ = .Cells(baseRow, colNum.CC).Value
    BCC_ = .Cells(baseRow, colNum.BCC).Value
    mailSubject_ = .Cells(baseRow, colNum.mailSubj).Value
    belongsTo_ = .Cells(baseRow, colNum.belongsTo).Value
    jobTitle_ = .Cells(baseRow, colNum.jobTitle).Value
    personName_ = .Cells(baseRow, colNum.personName).Value
    returnReceipt_ = .Cells(baseRow, colNum.returnReceipt).Value
    Dim i As Integer
    n = 0
    '文字列の入っている段落を数えてnumOfBodyプロパティにセット  '……(3)
    For i = colNum.p01 To colNum.p10
      If .Cells(baseRow, i).Value = "" Then                     '……(4)
        Exit For                                                '……(5)
      Else
        n = n + 1                                               '……(6)
      End If
    Next
    numOfBody_ = n                                              '……(7)
    'mailBodyプロパティに本文をセット
    ReDim mailBody_(numOfBody_)                                 '……(8)
    For i = 1 To numOfBody_                                     '……(9)
      mailBody_(i) = .Cells(baseRow, colNum.p01 + i - 1).Value
    Next
    n = 0
    '添付ファイル名の入っているセルを数えてnumOfAttFilesプロパティにセット  '……(10)
    For i = colNum.att01 To colNum.att10
      If .Cells(baseRow, i).Value = "" Then
        Exit For
      Else
        n = n + 1
      End If
    Next
    numOfAttFiles_ = n
    ReDim attFiles_(numOfAttFiles_)
    For i = 1 To numOfAttFiles_
      attFiles_(i) = .Cells(baseRow, colNum.att01 + i - 1).Value
    Next
  End With
  'ユーザ情報をsenderDataプロパティにセット                                 '……(11)
  For i = 1 To 9
    senderData_(i) = ThisWorkbook.Worksheets("ユーザ情報").Cells(i, 2).Value
  Next
End Sub

一応、コードの解説。

• (1)で、基準となるセルをプロパティにセット。以後、「インスタンス.baseCell」で取得できる。
• (2)は、単純に代入するだけのプロパティ群。これは説明不要だと思う。
  ※returnReceiptプロパティがString型なのに注意。後で説明する。
• (3)では、「本文（１行目）」～「本文（10行目）」のセル（Ｋ～Ｔ列）のうちいくつのセルに文字列が入っているのかをカウントしている。
• Forループで本文の入っているセルを左から調べていく。(4)の条件は、「セルが空白ならば」。
• (4)の条件を満たしていれば、すなわち、空白セルに当たったら、(5)でループを抜ける。
• (4)の条件を満たしていなければ、変数nをインクリメントしてループ。
• (7)まで来たら、変数nには文字列の入ったセルの数が格納されているはずなので、numOfBodyプロパティに値をセットする。
• これでmailBodyプロパティの要素数が確定しているので、(8)でReDimする。
• (9)で、Forループを用いて配列に要素を格納していく。
• (10)では、mailBodyプロパティと同様にattFilesプロパティをセットしていく。
• (11)で、同じようにsenderDataプロパティもセットする。

とりあえずこれで、コンストラクタまではできあがったことになる。

ひとまず挙動を確かめるために次のコードを標準モジュールに書く。

Public cm As CreatedMail
Sub test()
  Set cm = New CreatedMail        '……(1)
  Dim i As Integer
  For i = 1 To cm.numOfBody
    Debug.Print cm.mailBody(i)    '……(2)
  Next
End Sub

コードの説明。

• (1)でCreateMailクラスのインスタンスを生成。
• (2)では、Debug.Printを使って、Forループで

実行結果は、

ほれ、この通り。mailBodyプロパティに格納した各要素が全部順番にイミディエイト・ウィンドウに表示されている。

とりあえず、これでメールを作るための材料はひととおりクラスに持たせることができた。

後は、LotusNotesなり、Thunderbirdなり、メーラーに合わせてメールを作成するメソッドやクラスを書いていったらいいと思う。

あ、そうそう。リスト中の(※)のところ、returnReceiptプロパティをString型にしているのにはわけがあるのです。

コチラに「受信者が文書を開いたときに開封確認を送る場合は 1 を使用します。」だなんて書いてあるもんだから、てっきり

wkNDoc.ReturnReceipt = 1

と書いたら「受信確認あり」になると思うじゃないですか！

ところが、このように書くと、「送信オプション」の「受信確認」欄に不自然に「1」が埋まっているだけで、「受信確認あり」になってくれなかったんです。あまりにも謎現象だったので、しばらく放置していたのですが、

まさか、「1」とか「0」とかって、文字列じゃないよね？

と実験してみたらアンタ、

アッサリできちまった

じゃねーの!!!!!!!!

もし、同じ悩みを抱えている人がいたら、参考にしてください。

VBAで、ExcelからLotusNotesのメールを自動作成する

LotusNotesで送るメールの自動作成

メール作成・送信を自動化するマクロ

職場では、IBMのLotusNotesというグループウェアを使っています。とはいえ、ほとんど活用されていなくて、日常的にはせいぜいメールの送受信ぐらいにしか使われていない。私も、なんとなく非常に高機能なものなんだろうなあとは思いつつ、ほとんど活用していないｗ

ほとんど単なるメーラーと化しているNotes。全く同じメールを一斉送信するだけならいいんだけど、あっちこっちにちょっとづつ文面や添付ファイルが異なるメールを送らなきゃならん、ということになると非常にメンドクサイ。

「新規文書としてコピー」だかなんだかの機能（すまん、職場にしかNotesがないから確認できん）を使って一部を変えては送信、というやり方も２通や３通ならポチポチできようが、20通とか50通とか100通とかになってくると、もはや拷問のような業務になるし、添付ファイルの取り違えや宛先（メールアドレス）と本文内の宛名が一致しない、なんていうミスも起こりやすくなる。

ただでさえ砂を噛むような苦痛でしかない業務なのに、その上ミスが出て謝りまくるというのはあまりにも精神衛生上よろしくない。

なんとかならないものなのか、とggりまくって見つけたのがコチラのページ。

コードを引用する。割とあちこちで引用（っていうか転載？）されているので、「VBA　ノーツ　メール」とかでggったら、一番よくヒットするコードかも知れない。

'「Notesでメールを送信する」
'Excel97でNotes4.6用に作ったものだが、多分 どのバージョンでも動くと思います。

Const EMBED_ATTACHMENT As Integer = 1454                             '……(1)

Public Sub SendNotesMail()
    Dim wkNSes As Object    ' lotus.NOTESSESSION                     '……(2)
    Dim wkNDB As Object     ' lotus.NOTESDATABASE                    '……(3)
    Dim wkNDoc As Object    ' lotus.NOTESDOCUMENT                    '……(4)
    Dim wkNRtItem As Object ' lotus.NOTESRICHTEXTITEM                '……(5)
    Dim wkNAtt As Object    ' lotus.NOTESEMBEDDEDOBJECT              '……(6)
    Dim AttFName As String  ' 添付ファイル名(フルパス)

    ' Notesのセッションを起動する                                    '……(7)
    Set wkNSes = CreateObject("Notes.NotesSession") 
    ' NotesDatabaseオブジェクトを作成し、そのデータベースを開く      '……(8)
    Set wkNDB = wkNSes.GETDATABASE("", "")  
    ' NotesDBをユーザーのメールDBに割り当てた後に開く                '……(9)
    wkNDB.OpenMail

    ' NotesDBに文書を作成し、新規文書をオブジェクト変数にセットする  '……(10)
    Set wkNDoc = wkNDB.CREATEDOCUMENT()
    ' 件名をセットする                                               '……(11)
    wkNDoc.Subject = "テスト(タイトル)"
    ' 宛先をセットする
    wkNDoc.SendTo = Array("belie.kondo@mbh.nifty.com")
    'wkNDoc.CopyTo = Array("xxx@xxx")
    'wkNDoc.blindCopyTo = Array("xxx@xxx")

    ' 文書にリッチテキストアイテムを作成する
    Set wkNRtItem = wkNDoc.CreateRichTextItem("BODY")                '……(12)
    ' 本文をセットする
    With wkNRtItem
        .APPENDTEXT "本文（１行目）"                                 '……(13)
        .ADDNEWLINE 1                                                '……(14)
        .APPENDTEXT "本文（２行目）"                                 
        .ADDNEWLINE 2
        ' 添付ファイル名をセットする
        AttFName = "D:\TEST\Book1.xls"
        ' ファイルを添付する
        Set wkNAtt = .EmbedObject(EMBED_ATTACHMENT ,"" ,AttFName)    '……(15)
        .ADDTAB 1
        .ADDNEWLINE 1
    End With

    ' メールを送信する
    wkNDoc.Send False                                                '……(16)

    ' オブジェクト変数を解放する
    Set wkNAtt = Nothing
    Set wkNRtItem = Nothing
    Set wkNDoc = Nothing
    Set wkNDB = Nothing
    Set wkNSes = Nothing

    MsgBox "メール発信", vbOKOnly + vbInformation
End Sub

'Notesが起動していることが前提。

これを使い始めたきっかけは、

150件以上の宛先に、それぞれ全て異なる添付ファイルを添付してメールを送る

という、アホとしか思えない業務があったからだった。

聞けば、これまでの担当者は、それを休日に職場に出て行って、半日がかりでやっていたらしい。

私は、「バカヤロウ、そんなことができるか！　第一、休日の間に送ったメールでミスがあったら、週明けは苦情電話祭りになるじゃんか……」と思ったので、必死でそんなアホなことに陥らないようにggりまくりましたよ。良かった、インターネッツのある時代で。

まあ、そんなわけで、上掲のコードには非常にお世話になりましたよ。150件超のそれぞれ添付ファイルの異なるメールを、ちゃーんと本文内の宛名もその人に合わせて、10分ほどで送信完了できたわけですからね。

当時は、コードの意味なんてほとんど分からないまま使っていましたが、少しは分かるようになってきているので、確認も兼ねて説明してみよう。所詮素人のやることなので、間違えていたら優しく教えてください。

• (1)は、後の(15)、EmbedObject関数（？）の引数にするための定数。
• (2)～(6)では、VBAでNotesの各オブジェクトを操作するために、Notesの各オブジェクトを格納する変数を宣言している。
• (2)は、Notesのセッションそのもの。……と書いている自分でもいまいち意味がよく分かっていないｗ
• (3)は、Notesのデータベース。
• (4)は、Notesのドキュメント。たぶん、メール全体を指すのだと思う。
• (5)は、Notesのリッチテキストアイテム。たぶん、メールの中の、本文をはじめとする「目に見えている部分」を表しているのだと思う。
• (6)は、Notesの埋め込みオブジェクト。たぶん、添付ファイルなどの、メールに埋め込まれるものを表しているのだと思う。

たかがメール１本に、これだけたくさんのオブジェクトが関わっとるんですなあ。

• (7)では、VBAのCreateObject関数を用いて"Notes.NotesSession"クラスのインスタンスを生成して変数にセットしている。以後、この変数を使ってNotesのセッションを操作できるということ。以下同じ。
• (8)では、(7)でインスタンス化したNotesSessionクラスのgetDatabaseメソッドを使ってNotesDatabaseクラスのインスタンスを生成し、変数にセットしている。
• (9)では、(8)でインスタンス化したNotesDatabaseクラスのopenMailメソッドを用いて……何してるんだろ？？？
• (10)では、(8)でインスタンス化したNotesDatabaseクラスのcreateDocumentメソッドを用いてNotesDocumentクラスのインスタンスを生成し、変数にせっとしている。
• (11)以下のところでは、(10)でインスタンス化したNotesDocumentクラスのそれぞれのフィールドに値をセットしている。これはまあ、見たらだいたい初心者でも何やってるかは分かると思う。
• (12)では、NotesDocumentクラスのcreateRichTextItemメソッドを用いて、NotesRichTextItemクラスのインスタンスを生成し、変数にセットしている。

さあ、いよいよここからがメール本体の部分だ。

• (13)は、NotesRichTextItemクラスのappendTextメソッド。引数に指定したテキストを本文に追加する。
• (14)は、NotesRichTextItemクラスのaddNewLineメソッド。引数で指定した行数の新しい行を追加する。
• (15)は、NotesRichTextItemクラスのembedObjectメソッドを用いて添付ファイルをNotesEmbededObjectクラスのインスタンスとして生成しているのだと思う。そうした上で変数にセットしているのだろう。
• (16)でメールを送信。

あとは、オブジェクト変数を解放し、メッセージを表示しておしまい、ということ。

このコードには本当に非常にお世話になった。このコードのおかげで何度救われたことか……。

ただ、二つかなり困ることがあった。

• メールがいきなり送られてしまうこと。
• 送信履歴が残らないこと。

特に、２点目は困る。仕方がないので、常に自分自身にBCCで送るようにして擬似的に送信履歴が残るようにしていた。ただ、このやり方だと、受信リスト上で選ぶたびに「このメールには非表示の写しが」でんでんうんぬんといちいち出てきてうっとうしいことこの上ない。

それでも辛抱して使い続けていたある日、コチラのページに出会ったのでした。

メールを自動作成し、送信直前の状態で表示するマクロ

ソースコードも引用しておきます。

'*********************************************************************************
'いきなり送りつけずに、編集状態にする様に改造
'一旦確認してから送ると、送信ボックスに残るメリットあり。
'*********************************************************************************

Public Sub makeNotesMail()
    Dim wkNSes As Object    ' lotus.NOTESSESSION
    Dim wkNDB As Object     ' lotus.NOTESDATABASE
    Dim wkNDoc As Object    ' lotus.NOTESDOCUMENT
    Dim wkNRtItem As Object ' lotus.NOTESRICHTEXTITEM
    Dim wkNAtt As Object    ' lotus.NOTESEMBEDDEDOBJECT
    Dim AttFName As String  ' 添付ファイル名(フルパス)
    '追加
    Dim ws As Object 'NotesUIWorkspace
    Dim uidoc As Object
    
    ' Notesのセッションを起動する
    Set wkNSes = CreateObject("Notes.NotesSession")
    '追加
    Set ws = CreateObject("Notes.NotesUIWorkspace")
    
    ' NotesDatabaseオブジェクトを作成し、そのデータベースを開く
    Set wkNDB = wkNSes.GETDATABASE("", "")
    ' NotesDBをユーザーのメールDBに割り当てた後に開く
    wkNDB.OpenMail

    ' NotesDBに文書を作成し、新規文書をオブジェクト変数にセットする
    Set wkNDoc = wkNDB.CREATEDOCUMENT()
    ' 件名をセットする
    wkNDoc.Subject = "テスト(タイトル)"
    ' 宛先をセットする
    wkNDoc.SendTo = Array("abc@def.ghi.com")
    'wkNDoc.CopyTo = Array("xxx@xxx")
    'wkNDoc.blindCopyTo = Array("xxx@xxx")

    ' 文書にリッチテキストアイテムを作成する
    Set wkNRtItem = wkNDoc.CreateRichTextItem("BODY")
    ' 本文をセットする
    ' VBAでやる場合は、普通に文字列bufとかに、vbCrLfを介して文字を入れてやって
    ' wkNRtItem.APPENDTEXT buf で一丁上がり
    
    With wkNRtItem
        .APPENDTEXT "本文（１行目）"
        .ADDNEWLINE 1
        .APPENDTEXT "本文（２行目）"
        .ADDNEWLINE 2
        ' 添付ファイル名をセットする
        AttFName = getDesktopPath & "\Book1.xlsx"
        ' ファイルを添付する
        Set wkNAtt = .EmbedObject(EMBED_ATTACHMENT, "", AttFName)
        .ADDTAB 1
        .ADDNEWLINE 1
    End With
    ' メールを保存する。これをやらないとRichItemの編集が表示されない  '……(*)
    wkNDoc.Save False, False
    ' メールを編集状態にする
    Set uidoc = ws.EDITDOCUMENT(True, wkNDoc, False)

    ' オブジェクト変数を解放する
    Set wkNAtt = Nothing
    Set wkNRtItem = Nothing
    Set wkNDoc = Nothing
    Set uidoc = Nothing
    Set wkNDB = Nothing
    Set wkNSes = Nothing
    Set ws = Nothing

End Sub

ポイントは(*)のところ。何と、たったこれだけで、送信直前の状態で画面表示されます。

しかも、Notesの側で送信アイコンをクリックして送信するので、ちゃんと送信履歴に残るし、(*)の時点でドラフトに保存されているから、送信せずに閉じてもちゃんと残っている。

これは素晴らしい!!!!!!!!!!!

……というわけで、私は、上記２つのコードを参考に、

Excelでご覧のようなシートを作成し、メールを自動作成するマクロを作って大いに活用しています。

そのマクロは、また折を見てご紹介します。今日はもうここまででかなり長くなってしまったので……。

コチラもどうぞ！

akashi-keirin.hatenablog.com

akashi-keirin.hatenablog.com

akashi-keirin.hatenablog.com

akashi-keirin.hatenablog.com