Extracting Excel Rows to Outlook Based on Data

[MEDavis]

Hi All,

I'm kind of a novice here so please, be gentle. I was wondering if there is a way to send specific, non-consecutive rows from excel to Outlook based on the data in one of the cells. Basically, I'm working on inventory and I want to populate an email to send to my boss when certain parts are running low or are out of sock. Say, row 10 is low on inventory and row 22 is out of stock, can I have Excel automatically pull those 2 rows and populate them into the body of my email?

Then if I update say row 37 to a quantity of zero and it is now out of stock, I want that row to populate my email.

What I have so far is below and it works well, it just doesn't have all of the information I need in the body of the email.

Thanks in Advance

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim XRg As Range
On Error Resume Next
If Target.Cells.Count > 1 Then
Set XRg = Application.Range("E210-241,E275,E279-E458")
End If
If IsNumeric(Target.Value) And Target.Value < 1 Then
End If
If Target.Cells.Count > 2 Then
Set XRg = Application.Range("E3-E5,E22-E36,E120,E150-E152,E172-E208,E245-274,E277-E278")
End If
If IsNumeric(Target.Value) And Target.Value < 2 Then
End If
If Target.Cells.Count > 3 Then
Set XRg = Application.Range("E125,E209")
End If
If IsNumeric(Target.Value) And Target.Value < 3 Then
End If
If Target.Cells.Count > 4 Then
Set XRg = Application.Range("E160-E161")
End If
If IsNumeric(Target.Value) And Target.Value < 4 Then
End If
If Target.Cells.Count > 5 Then
Set XRg = Application.Range("E21,E169,E171")
End If
If IsNumeric(Target.Value) And Target.Value < 5 Then
End If
If Target.Cells.Count > 9 Then
Set XRg = Application.Range("E162")
End If
If IsNumeric(Target.Value) And Target.Value < 9 Then
End If
If Target.Cells.Count > 10 Then
Set XRg = Application.Range("E242-E244")
End If
If IsNumeric(Target.Value) And Target.Value < 10 Then
End If
If Target.Cells.Count > 15 Then
Set XRg = Application.Range("E164-E168")
End If
If IsNumeric(Target.Value) And Target.Value < 15 Then
End If
If Target.Cells.Count > 20 Then
Set XRg = Application.Range("E170")
End If
If IsNumeric(Target.Value) And Target.Value < 20 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "***TEST***" & vbNewLine & vbNewLine & _
"" & vbNewLine & vbNewLine & _
"" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & vbNewLine & _
"Thanks," & vbNewLine & vbNewLine & _
""
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "***TEST***Panel Shop Inventory"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub