Excel VBAを使った在庫管理/自動メール送信システムの作成手順【初心者向け】　Excelからメールで自動発注

在庫管理はビジネスにおいて重要な要素です。しかし、手動で在庫を管理するとミスが発生しやすく、効率的な管理が難しくなります。そこで、Excel VBAを活用して自動化された在庫管理システムを作成しましょう。この記事では、初心者でも理解しやすいように、具体的な手順で在庫管理システムを作成する方法を解説します。
今回は、VBAを使って、在庫数量がしきい値より下がった場合に、メールで業者に自動発注するシステムを紹介します。

・工場の在庫管理ってうまくできないんだよな。
・現場のみんなが使ってくれるシステムを作りたいな。
・在庫数量があわないし、在庫が不足したときにわざわざ頼むのが面倒なんだよな。

「在庫管理って面倒くさいよね」「手動で管理するとミスが多いんだよな」――こんな風に在庫管理に悩んでいる人、たくさんいますよね。

商品が売れたり、製品が届いたりするたびに在庫数を更新するのは、時間も手間もかかるものです。さらに、在庫不足に気づかなかったり、オーダーのタイミングを逃してしまったりすると、ビジネスに大きな損失をもたらすこともあります。

でも大丈夫！Excel VBAを使えば、在庫管理を自動化することができますよ。手動での更新作業やミスから解放され、効率的な在庫管理が可能になります。

このブログでは、初心者でもわかりやすい手順でExcel VBAを活用した在庫管理/自動メール送信システムの作成方法を紹介しています。具体的なコードの解説や関数の構文などもわかりやすく解説していますので、プログラミング初心者の方も安心して取り組めます。

ブログの対象者
　・在庫管理に手間を感じているビジネスオーナー
　・在庫数量の更新や管理作業に時間を費やしている作業者
　・在庫不足やオーダーのタイミングを逃してしまうことに悩んでいる事業者
　・Excel VBAを使って在庫管理を効率化したいと考えている方
　・プログラミング初心者でも手軽に取り組める在庫管理システムを探している人

リンク

Contents

在庫管理システムの基本概念
在庫管理・自動メール送付システムのコード
まとめと次のステップ

🚀 0円で現役エンジニアから学べる【Techスクールオンライン】のお申込みをお勧めします。このオンラインスクールでは、現役のエンジニアから直接学ぶことができ、プログラミングの基礎から高度なスキルまでを習得できます。しかも、今なら 0円で受講できるチャンスです。エンジニア転職を考えている方やプログラミングに興味がある方、新しいスキルを習得したい方に特におすすめです。

在庫管理システムの基本概念

在庫管理システムでは、在庫数量としきい値の関係を把握することが重要です。在庫数量がしきい値を下回った場合に、自動的にメールで通知する機能を実装します。これにより、在庫不足に早く気づき、迅速な対応が可能となります。

エクセルファイルの作成手順：

①Excelを開き、新しいブランクのワークブックを作成します。

②ワークシートを作成し、セルに必要な情報（アイテム名称、在庫数量、しきい値、メール送付先）を配置します。

とりあえず、こんな表を作成します。

VBAエディタの開き方と基礎知識：

Excelのリボンメニューから「開発」タブを表示し、「Visual Basic」をクリックします。
VBAエディタが開かれます。VBAエディタでは、VBAコードを記述して在庫管理システムを作成します。
VBAの基礎知識（変数、条件分岐、ループなど）を学びながら、以下の手順でVBAコードを作成していきましょう。

VBAエディタが開かれたら、Sheetをダブルクリックして、下記プログラムを貼り付けてください。内容の紹介は最後に紹介します。

今回使っているシートのコードは、Excelの表の数量が変化し、しきい値以下となった場合に、メールを送付するポップアップが出るプログラムになっております。

在庫管理システムのVBAコード作成：

在庫数量としきい値の比較を行うためのVBAコードを作成します。
在庫数量がしきい値を下回った場合にメール送信するためのVBAコードを追加します。

イベントの設定と動作確認：

在庫数量が変更されたときにVBAコードが実行されるようにイベントを設定します。
在庫数量を変更し、しきい値を下回るかどうかを確認します。
在庫数量がしきい値を下回った場合、自動的にメールが送信されることを確認しましょう。在庫数量を人为的に減らしてしきい値を下回るように変更し、VBAコードが正常に動作するかをテストします。

関数の構文

事前の基礎知識として、関数の構文をいれます。こちらが、今回の肝となる、Sheetに変更があった場合イベントが発生する構文です。

Worksheet_Changeイベントの構文

Private Sub Worksheet_Change(ByVal Target As Range)
    ' イベントの処理内容
End Sub

MsgBox関数の構文：

result = MsgBox(prompt, buttons, title)

prompt：表示するメッセージのテキスト
buttons：表示するボタンの種類を指定（vbQuestion + vbYesNoの組み合わせでYes/Noボタンが表示されます）
title：メッセージボックスのタイトル

在庫管理・自動メール送付システムのコード

プログラムコードが下記になります。実際にコードをコピーして使ってみてください。
D列に在庫数量、E列にしきい値が記載されて、D列の値がE列より小さくなったら、メールを送付するプログラムです。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim stockRange As Range
    Dim thresholdRange As Range
    Dim recipientRange As Range
    Dim stockQty As Long
    Dim threshold As Long
    Dim recipientAddress As String
    Dim i As Long
    
    ' 監視するセルの範囲を指定します（D列とE列の範囲）
    Set stockRange = Range("D3", Range("D" & Rows.Count).End(xlUp))
    Set thresholdRange = Range("E3", Range("E" & Rows.Count).End(xlUp))
    Set recipientRange = Range("F3", Range("F" & Rows.Count).End(xlUp))
    
    ' 在庫数量としきい値をアイテムごとに比較します
    For i = 1 To stockRange.Rows.Count
        stockQty = stockRange.Cells(i).Value
        threshold = thresholdRange.Cells(i).Value
        
        ' 在庫数量がしきい値を下回った場合に処理を実行します
        If stockQty < threshold Then
            ' ポップアップメッセージを表示し、ユーザーの選択結果を取得します
            Dim response As VbMsgBoxResult
            response = MsgBox("メールを送付してよいですか？", vbQuestion + vbYesNo)
            
            ' OKが選択された場合にメールを送信します
            If response = vbYes Then
                recipientAddress = recipientRange.Cells(i).Value
                Call SendEmail(recipientAddress, "在庫不足のお知らせ", "アイテム名称: " & Cells(i + 2, 2).Value & vbCrLf & "在庫数量: " & stockQty)
            End If
        End If
    Next i
End Sub

Private Sub SendEmail(ByVal recipient As String, ByVal subject As String, ByVal body As String)
 ' メールを送信するマクロ
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    ' Outlookアプリケーションを作成します
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' 新しいメールを作成します
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    With OutlookMail
        ' 宛先、件名、本文を設定します
        .To = recipient
        .subject = subject
        .body = body
        
        ' メールを表示せずに送信します
        .Display
        '.Send ' メールを直接送信する場合は、この行のコメントを解除します
    End With
    
    ' オブジェクトを解放します
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

実際に動かしてみます。上記プログラムをVBAのSheetに貼り付けます。
下記表のホースの在庫数量を100個から、しきい値以下の80個に数値を減らします。

すると。。。