Excelデータ→請求書→PDF→メール添付一括送信マクロ

2018年4月9日

URLをコピーしました！

Excelを活用すれば、ちょっとしたソフトのようなものもつくれます。

1 繰り返しの仕事は効率化の余地あり
2 Excelマクロで請求書作成
3 Excelマクロでメール送信

繰り返しの仕事は効率化の余地あり

仕事の効率化を考えるときに、そのヒントの１つは、「繰り返し」です。
繰り返しやっていることは効率化できますし、その効果も高くなります。

毎月、毎週、毎月、繰り返していることをまずは重点的に効率化しましょう。

そして、この繰り返せるかが重要です。
定型のパターンをつくりあてはめることで繰り返せます。
毎回、イレギュラーがあり、定型でない仕事は、効率化しにくくなり、プログラミングやロボットでも効率化できません。

定型の仕事？と思われるかもしれません。
そのとおり、定型ではない仕事が価値をうみます。
逆に考えれば、価値を生まない仕事は、定型にすべきなのです。

たとえば、今回事例に挙げる請求書の作成・送付。
これに時間をかけてもお客様は喜びませんし、請求書に記す金額が増えるわけでもありません。
定型にして、効率化すべきなのです。

■井ノ上陽一のプロフィール
■無料メルマガ「税理士進化論」　
■無料メルマガ『社長にこっそり伝えたい税理士のノウハウ』
■無料冊子『雇われない雇わない生き方』
■YouTube『効率化で独立を楽しく』
■Facebook『井ノ上陽一の独立日誌』
■無料Voicy ラジオ『ひとりしごとの営業術』
■Instagram　『1日1新』

Excelマクロで請求書作成

請求書をつくるには、請求書作成ソフトや会計ソフトがあります。
Excelマクロであえてつくらなくても、安くかんたんに請求書はつくれるわけです。

あえてExcelマクロでつくってみるのは、身近な事例を実際にプログラミングしてみるためでもあります。

他のプログラミング言語に比べると、Excelマクロ（VBA）は、圧倒的に手軽です。
Excelを開いて、Alt＋F11（同時に押す）で、すぐにプログラムが書けます。

プログラムを書き、自分でつくってみるのは、これまでの時代もこれからも大事なことです。
つくってみると、Excelのしくみを理解でき、仕事を定型にするくせもつきます。
使っているだけでは、効率化のセンスは磨かれません。

請求書作成

プログラム（コード）は、記事最下部に載せてあります。
Excelファイルの構成は、次のとおりです。
シート「data」
氏名、項目、金額、そしてメールアドレスのリストです。
実際には、複数項目になることもありますが、ここでは1つの項目のリストにしました。

シート「invoice」

請求書のフォーマットです。
ここにデータを流し込んでいきます。
消費税の計算はこのフォーマット内です。

プログラムをみると、まず、データをカウントしています。
今回の場合は、4です。
最初のデータは2行目なので、2行目からその4行目まで、繰り返します。
この「繰り返し」はマクロが得意とするもので、前述した効率化の秘訣でもあるわけです。

また、プログラムを見やすくするために、
ThisWorkbook.Worksheets(“data”)を　W_Data

に置き換えています。

請求書のフォーマット「invoice」をコピーしてそこにデータを流し込みます。
そのままフォーマットに流し込むと、2行目から4行目のデータで上書きしてしまうのでコピーが必要です。
Worksheets(“invoice”).Copy
で、新しいブックを開いてそこにコピーします。

まずは今日の日付を入力し、2行目の氏名、項目、金額を入力します。
あとは、3行目、4行目と繰り返しです。

請求書PDFファイル作成

できあがった請求書ExcelをPDFで保存します。
ファイル名は、「日付＋氏名＋様＋請求書」です。

Excelマクロでメール送信

最後に請求書PDFをメールに添付して送ります。

Excelマクロでメール送信するには、Outlookを使う方法もありますが、Outlookを使っている必要があります。
私も使っていないので、メールソフトに依存しない方法にしました。

また、Gmailで送る方法もありますが、Gmailのセキュリティ設定を弱くする必要もあり、2段階認証にしているとさらにややこしいので、Gmailアドレスではなく、サーバーのメールアドレス（独自メールアドレス）を使う方法です。

メールアドレスをGmailで送受信していれば、請求書PDFの送付をGmail上で確認できます。

まず、VBE（ExcelでAlt＋F11）で、［ツール］→［参照設定］で、

この［Microsoft CDO for Windows 2000 Library］にチェックを入れましょう。
これを使います。

プログラムでは、そのライブラリを動かし、その後メール設定をする流れです。

メールの送信形式、SSLで送るかどうかの設定のあと、メールサーバーの情報をExcelファイルのシート「set」に入れ、それを読み込みます。
プログラムに直接書き込んでもいいのですが、Excelから読み込むようにしたほうがメンテナンスがしやすいのでおすすめです。

SMTPサーバー名（ホスト名）、ユーザー名、パスワード、ポート番号といった情報が必要ですので、サーバーで確認しておきましょう。

さらに、メール本文の設定をしていきます。
Excelから、送信元、件名、本文を読み込むしくみです。
送信元は、井ノ上陽一<aaaa@aaaa.co.jp>と入れれば、表示名も変えられます。

本文は、「様・・・・」と入れておき、Excelのシート「data」の氏名から姓だけを取り出しくっつけます。
データ上、姓と名にわけていれば、もっと楽ですが。

添付ファイル名は、請求書PDFを作成したときの名前を使うようにしています。

メール送信後、設定をいったんリセットしています。
繰り返しの中にメール設定も入っているのは、このリセットのためです。
リセットしないと、1人目にPDFが1つ、2人目にPDFが2つ・・・となってしまいます。

マクロを実行すると、データの数だけそれぞれのメールアドレスへ添付ファイルとともに送ることができますので試してみていただければ。
（実際に使う場合はくれぐれも十分なテストをしてからにしましょう）

このマクロなら10か所でも100か所でも請求書PDFをメールで送れます。
そんなに請求書を出す先ありませんが。。。

Sub invoice_mail()


'■請求書Excelの作成
'データのカウント
    Dim Max_row As Long
    Max_row = Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row


'シート「データ」を変数へ
    Dim W_Data As Worksheet
    Set W_Data = ThisWorkbook.Worksheets("data")
    
        
    Dim i As Long
    For i = 2 To Max_row
    
        '請求書フォーマットを新しいブックへコピー
        ThisWorkbook.Activate
        Worksheets("invoice").Copy
        
        ActiveSheet.Range("d5").Value = Date  '日付
        
        ActiveSheet.Range("a6").Value = W_Data.Range("a" & i).Value & "様"  '氏名
        
        ActiveSheet.Range("b25").Value = W_Data.Range("b" & i).Value  '項目
        
        ActiveSheet.Range("d25").Value = W_Data.Range("c" & i).Value  '金額
        
        ActiveSheet.Name = W_Data.Range("a" & i).Value  'シート名
        
    
'■請求書PDFの作成
        'ファイル名
        Dim Pdf_name
        Pdf_name = ThisWorkbook.Path & "\" & Format(Date, "yyyymmdd") & ActiveSheet.Name & "様 請求書.pdf"
        
        'PDFで保存
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Pdf_name
        ActiveWorkbook.Close savechanges:=False
    
    
'■メール送信
        Dim Mail  As Object
        Set Mail = CreateObject("CDO.Message")
        
        Dim URL
        URL = "http://schemas.microsoft.com/cdo/configuration/"
        
        'メールの設定
        With Mail.Configuration.Fields
            .Item(URL & "send" & "using") = 2
            .Item(URL & "smtp" & "authenticate") = 1
            .Item(URL & "smtp" & "usessl") = 1
            
            .Item(URL & "smtp" & "server") = Worksheets("set").Range("b1").Value  'SMTPサーバー
            .Item(URL & "smtp" & "serverport") = Worksheets("set").Range("b2").Value  'ポート名
            .Item(URL & "send" & "username") = Worksheets("set").Range("b3").Value  'ユーザーID
            .Item(URL & "send" & "password") = Worksheets("set").Range("b4").Value  'パスワード
            .Update
        End With
        
        Mail.From = Worksheets("set").Range("b5").Value  '送信元
    
        Mail.To = W_Data.Range("d" & i).Value  '送信先（シート「data」より）
        Mail.Subject = Worksheets("set").Range("b6").Value  '件名
        
        Dim Body_Name '本文用に姓のみ抽出
        Body_Name = Left(W_Data.Range("a" & i).Value, InStr(W_Data.Range("a" & i).Value, "　") - 1)
        Mail.TextBody = Body_Name & Worksheets("set").Range("b7").Value '本文
        
        Mail.AddAttachment Pdf_name  '添付ファイル
        
        Mail.send 'メール送信
        
        Set Mail = Nothing '設定リセット
    
    Next

End Sub

EX-ITサンプル　請求書PDFメールマクロ.xlsm