【Excel】VBAと関数で報告書作成業務を改善 #Excel

はじめに

報告書などの作成はなるべく手早く済ませたいですよね。
「報告書の提出業務を効率化したい！」「改めてExcelの勉強もしたい！」ということで、12名のチームでExcelVBA・Excel関数を用いた業務改善を図りました。

01.活動目的・活動内容

活動目的

ExcelVBA・Excel関数を用いて、自社業務の改善に役立てる
「日報メール」「チーム会議事録メール」の作成を自動化・効率化させる

活動内容

日報担当：G.Kさん
議事録担当：Y.Yさん
検証結果をチーム内で共有

02.検証目的・検証結果

検証目的

定例業務である「日報メール」「チーム会議事録メール」の提出について、ExcelVBA及びExcel関数を用いて、効率化を図れるかを検証

検証結果

Excelのテンプレートに必要項目を入力しマクロを起動させることで、Outlookの立ち上げやメール送付が可能であることを確認
Outlookメールについて、プライベートのメールアドレスを差出人として送ることはできるが、自社で日報メールを送る際に使用する会社用メールアドレスからの送付は、サーバー情報が必要なため難しいことを確認

03.成果物 - 日報

テンプレート

こちらが入力用のExcelシートです。

手順

テンプレートの「入力内容」欄に必要な内容を記入します。
テンプレート右側にプレビューが表示されているため、内容を確認しながら入力することができます。
テンプレート上部にある「メール送信」ボタンをクリックします。
Outlookが起動します。
テンプレートに入力した内容が、そのまま送信できる形で反映されていることを確認します。

解説

「メール送信」ボタン部のコードはこちら

【日報】「メール送信」ボタン部のコード

'プログラム0｜変数設定の指定
Option Explicit
 
'プログラム1｜プログラム開始
Sub SendMail1()
 
    'プログラム2｜シート設定
    Dim ws As Worksheet
    Set ws = Worksheets("入力用テンプレート")
         
    'プログラム3｜Outlookアプリケーションを起動
    Dim OutlookObj As Outlook.Application
    Set OutlookObj = CreateObject("Outlook.Application")
    
    'プログラム4｜Outlookメールを作成
    Dim mymail As Outlook.MailItem
    Set mymail = OutlookObj.CreateItem(olMailItem)
    
    'プログラム5｜メール情報を設定
    mymail.BodyFormat = 3        'リッチテキストに変更
    mymail.To = ws.Range("B10").Value   'To宛先
    mymail.CC = ws.Range("B11").Value   'cc宛先
    mymail.BCC = ws.Range("B12").Value  'bcc宛先
    mymail.Subject = ws.Range("D9").Value     '件名
    
    'プログラム6｜メール本文を設定
    Dim mailBody As String, credit As String
    mailBody = ws.Range("D12").Value
    credit = ws.Range("D13").Value
    mymail.body = mailBody & vbCrLf & vbCrLf & credit
    
    'プログラム7｜メールにファイルを添付
    Dim attachedfile As String
    attachedfile = ThisWorkbook.Path & "\" & ws.Range("B9").Value
    If Not attachedfile = "" Then
    End If
            
    'プログラム8｜メール表示
    mymail.Display     'メール表示(ここでは誤送信を防ぐために表示だけにして、メール送信はしない)
    
    'プログラム9｜メール保存
    mymail.Save        '下書き保存
    
    'プログラム10｜メール送信
    'mymail.Send
 
    'プログラム11｜オブジェクト解放
    Set OutlookObj = Nothing
    Set mymail = Nothing
    
'プログラム12｜プログラム終了
End Sub

04.成果物 - 議事録

テンプレート

こちらが入力用のExcelシートです。

手順

テンプレートの「入力用」欄に必要な内容を記入します。
日報同様、テンプレート右側にプレビューが表示されているため、内容を確認しながら入力することができます。
テンプレート上部にある「メール作成」ボタンをクリックします。
Outlookが起動します。
テンプレートに入力した内容が、そのまま送信できる形で反映されていることを確認します。

プログラム解説

「メール作成」ボタン部のコードはこちら

【議事録】「メール作成」ボタン部のコード

'Private Sub CommandButton1_Click()

    'プログラム2｜シート設定
    Dim ws As Worksheet
    Set ws = Worksheets("入力欄")
         
    'プログラム3｜Outlookアプリケーションを起動
    Dim outlookObj As Outlook.Application
    Set outlookObj = CreateObject("Outlook.Application")
    
    'プログラム4｜Outlookメールを作成
    Dim mymail As Outlook.MailItem
    Set mymail = outlookObj.CreateItem(olMailItem)
    
    'プログラム5｜メール情報を設定
    mymail.BodyFormat = 1        'テキスト形式
    mymail.To = ws.Range("B7").Value   'To宛先
    mymail.CC = ws.Range("B9").Value   'cc宛先
    mymail.BCC = ws.Range("B11").Value  'bcc宛先
    mymail.Subject = ws.Range("G6").Value     '件名
    
    'プログラム6｜メール本文を設定
    Dim mailbody As String
    mailbody = ws.Range("G8").Value
    mymail.Body = mailbody
            
    'プログラム8｜メール表示
    mymail.Display     'メール表示(ここでは誤送信を防ぐために表示だけにして、メール送信はしない)

    'プログラム11｜オブジェクト解放
    Set outlookObj = Nothing
    Set mymail = Nothing
    
'プログラム12｜プログラム終了
End Sub

関数解説

複数のセルを参照させたい場合は、一行で入力します。（今回はB14～B41までを参照）
記載したい本文・参照したいセルの組み合わせ方は、【"本文"&セル番号】【セル番号&"本文"】です。
改行したい場合はCHAR関数”CHAR(10)”を使用します。（テキスト扱いになるため””が必要です）

05.日報 - テキストファイル出力Ver.

Outlookからコピーする方法では誤送信の可能性があるため、テキストファイルを出力する形式のものも作成しました。
日報・議事録共に作成し、日報はテキストファイルを出力する方法でプログラムしています。

手順

テンプレートに必要な内容を記入します。
テンプレート上部にある「日報テキスト出力」ボタンをクリックします。
「日報.txtに書き出しました」というメッセージが表示されます。
日報.txtが出力され、テンプレートに入力した内容が記載されていることを確認します。

プログラム解説

「メール作成」ボタン部のコードはこちら

【日報テキストファイル出力Ver.】「日報テキスト出力」ボタン部のコード

'Option Explicit

Sub makeText()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(3)

Dim datFile As String
datFile = ActiveWorkbook.Path & "\日報.txt"

Open datFile For Output As #1

Dim i As Long
i = 1
Do While ws.Cells(i, 1).Value <> ""
    Print #1, ws.Cells(i, 1).Value
    i = i + 1
Loop

Close #1

MsgBox "日報.txtに書き出しました"

End Sub

06.議事録 - メモ帳起動Ver.

こちらはメモ帳アプリを起動する方法でプログラムしています。

手順

テンプレートに必要な内容を記入します。
テンプレート上部にある「メモ帳コピー」ボタンをクリックします。
メモ帳が起動します。
テンプレートに入力した内容が記載されていることを確認します。

プログラム解説

「メール作成」ボタン部のコードはこちら

【議事録メモ帳起動Ver.】「メモ帳コピー」ボタン部のコード

'Private Sub CommandButton2_Click()

Dim strText As Variant
Range("G8").Select
strText = Replace(ActiveCell, vbLf, vbCrLf)

With CreateObject("forms.textbox.1")
    .MultiLine = True
    .Text = strText
    .SelStart = 0
    .SelLength = .TextLength
    .Copy
End With

Shell "notepad", 1
Application.Wait Now() + TimeValue("00:00:03")
SendKeys "^V", True

End Sub

最後に

VBAの構文や関数の知識を得ることができました。
VBAを使用して、Excelに記載した内容をOutlookやメモ帳に出力させることができました。