Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2017
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto email notifications based on excel cell values with different cell values

    Hello,
    I have been doing a lot of reading on this and found myself successful but not! What I am trying to do is on sheet1 cell D5 value less than value 65 to email me telling me that Patty Press stock is low. With this in mind I can get this to work fine. My issue is trying to get other cells to do the same thing with different values. For example: I would like sheet1 cell D5 to email me when it gets below 65 telling me that patty presses are low, and sheet1 cell D6 to email me when it gets below 25 telling me that patty smashers are low in stock. I have about 50 or more items to monitor all with different values. I have posted below what I have gotten to work for just one item but cannot figure out how to do multiple items with different values. I am using Win10 with office2010. I think since there are more than one chance of a value change reminder per session I might want it to send when closing the spreadsheet. Any help would be much appreciated as I am very new to this level of excel.

    Private Sub Worksheet_Change(ByVal Target As Range)


    If ActiveSheet.Range("D5").Value < 25 Then


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    strbody = "Patty Press stock is below 65 "



    On Error Resume Next
    With OutMail
    .To = "timmytoo@whatever.com"
    .CC = ""
    .BCC = ""
    .Subject = "Patty Press stock needs attention"
    .Body = strbody

    .Send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing

    End If
    End Sub






    Thank you in advance for taking time to look into this.

    Bocephusva

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,929
    Thanks
    4
    Thanked 295 Times in 267 Posts
    This works better if you provide a worksheet which contains columns for both data points (actual number & threshold value). And you need to determine whether you want a separate email for every trigger or a combined email containing all current triggers.

    You can produce a live display by using formulas in your columns along the lines of the following
    ItemName, StockLevel, TriggerPoint, =if(StockLevel<TriggerPoint,"order " & ItemName,"")
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    May 2017
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew, Thank you for replying.
    I think it would be best if it sends an email reminder when closing giving info on all items that are below Qty on hand. I added the code to sheet1 and it triggers the reminder at the time of entry. keeping in mind that all cell values on sheet1 column D will be different and not sure how to enter it for more than one cell value on column D.

    I hope this explains what im looking to do.
    Thank you
    Bocephusva


    Master Inventory test01.xlsm

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,929
    Thanks
    4
    Thanked 295 Times in 267 Posts
    Try this variation on your macro in your ThisWorkbook module.

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Dim aRng As Range, sAttn As String, bSendEmail As Boolean
      Dim OutApp As Object, OutMail As Object
    
      For Each aRng In ActiveWorkbook.Sheets("Sheet1").Range("E5:E22").Cells
        If aRng.Value < 0 Then
          sAttn = sAttn & aRng.Offset(0, -3).Value & vbCr
        End If
      Next
      Debug.Print sAttn
      If sAttn <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
          With OutMail
            .To = "timmytoo@whatever.com"
            .Subject = "Low stock levels needing attention"
            .Body = sAttn
            .Send
          End With
        On Error GoTo 0
        OutApp.Quit
        Set OutMail = Nothing
        Set OutApp = Nothing
      End If
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    New Lounger
    Join Date
    May 2017
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew,
    Thank you for getting back to me on this so fast. I have added your code and get no results, I must be doing something wrong. I have attached the new version for you to look at.

    Thank you sir for your time.

    Bocephusva


    Inventory test012.xlsm

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,929
    Thanks
    4
    Thanked 295 Times in 267 Posts
    If you want the code to run automatically before you close the workbook then it matters where the code is placed. You put it into a regular module. It won't automatically fire there. It has to be saved into your ThisWorkbook module.ThisWorkbook.png

    Note also that this code is just to get you started. There is a couple of bits of clumsiness in there that will need to be ironed out once you get it working.
    1. The code opens a new instance of Outlook whether or not Outlook is already running. Then at the end it closes it. If you already had Outlook running, you probably wanted to keep it open.
    2. I did add a quit line which probably acts before the email has been sent - this needs to be slowed down so that the email has time to be sent before Outlook is told to quit.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    New Lounger
    Join Date
    May 2017
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Andrew for helping out on this. I see how it works now and think I can tweak it just a bit. You all are awesome and thank you again for helping the needed!

    Bocephusva

  8. #8
    New Lounger
    Join Date
    May 2017
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew, one more thing if I may, is it possible to reduce the countdown time on the outlook send?

    Thank you again

    Bocephusva

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •