Stock Inventory Tracker In Excel - Auto Email

To Download Sample File Click Here

Note: Save File under excel format and makesure outlook installed and correctly configured

Copy Code and paste under Inventory/Sheet Sheet

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range

    Dim myCell As Range

    Dim mySubject As String

    Dim myMessage As String

    Dim myMail As Object

    

    Set myRange = Range("E:E")

    

    If Not Intersect(Target, myRange) Is Nothing Then

        For Each myCell In Intersect(Target, myRange)

            If myCell.Value = 0 Then

                Set myMail = CreateObject("Outlook.Application").CreateItem(0)

                mySubject = "Stock count has gone to zero"

                myMessage = "The stock count for " & Cells(1, 16).Value & " has gone to zero."

                With myMail

                    .To = Cells(2, 8).Value

                    .Subject = mySubject

                    .Body = myMessage

                    .Send

                End With

            End If

        Next myCell

    End If

End Sub