Google Sheets Automation - Auto Email On Basis Of Cell Value

 Google Sheets Automation - Auto Email On Basis Of Cell Value

Copy below Code and paste under apps script (Change the email id to required)

function sendEmail() {

  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet

  var range = sheet.getRange("C:C"); // Replace "C:C" with the range of your column

  var values = range.getValues();

  var lastRow = sheet.getLastRow();

  var emailSent = sheet.getRange("E1:E").getValues(); // Assuming E2 is the starting cell of the emailSent column

  for (var i = 0; i < lastRow; i++) {

    if (values[i][0] === 0 && emailSent[i][0] !== "SENT") { // Change "0" to the value you want to monitor and adjust emailSent accordingly

      var emailAddress = "dummy@excelmind.com"; // Replace with the email address you want to send the notification to

      var subject = "Stock count went to zero";

      var message = "Stock went zero for: " + sheet.getRange("I2").getValue(); // Replace "D1" with the cell you want to include in the email body

      

      MailApp.sendEmail(emailAddress, subject, message);

      sheet.getRange("E" + (i + 1)).setValue("SENT"); // Assuming E2 is the starting cell of the emailSent column

      }

  }

}

 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