VBA code to send email when multiple items fall below minimum values

[raspinwall]

Hello,

I would like to send a single email that contains all items in a spreadsheet that have fallen below minimum quantity values.
In the body of the email, I need to include each low items Manufacturer, Part # and Description which are each individual cells.

E-mail body:

Hello,

The following items have fallen below minimum quantities:

Row 2, Widget Co, ABC123, Widget Standard
Row 3 Widget Co, DEF456, Widget Pro

Note, in the code, there are different minimum values for different ranges.

Spreadsheet looks like this:

Quantity	Mfgr	Part#	Description
0	Widget Co	ABC123	Widget Standard
0	Widget Co	DEF456	Widget Pro

Code I have so far looks like this:

'Need to be in the sheet code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call notify
End Sub

Sub notify()
    Dim rng As Range
    For Each rng In Range("B6:B11,B13:B17")
       If (rng.Value < 4) Then
           Call mymacro(rng.Address)
       End If
    Next rng
    For Each rng In Range("B99:B116")
       If (rng.Value < 1) Then
           Call mymacro(rng.Address)
       End If
    Next rng
End Sub



Private Sub mymacro(theValue As String)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello," & vbNewLine & vbNewLine & _
              "The following inventory items have fallen below minimum stock quantities: " & theValue
    On Error Resume Next
    With xOutMail
        .To = "user@test.com"
        .CC = ""
        .BCC = ""
        .Subject = "Low warehouse stock alert"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Thanks for all of the help with this!!!!

Russ

 

[pbornemeier]

This site reveals all about controlling Outlook from Excel: Mail from Excel with Outlook (VBA)