More than 1 year has passed since last update.

エニプラAdvent Calendar 2022

@yuzuki6v6(ゆ)

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

Last updated at 2022-12-18Posted at 2022-12-18

はじめに

報告書などの作成はなるべく手早く済ませたいですよね。
「報告書の提出業務を効率化したい！」「改めて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やメモ帳に出力させることができました。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up