Sending email if criteria is met in excel, with all cells that have met criteria included in body

[rmk911]

Hi all

Apologies if this has already been posted (I checked, and found similar but not identical posts), but essentially, I have a VBA script that looks like this, to send an email based on the criteria of one cell:

Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("B1"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 75 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
On Error Resume Next
With xOutMail
.To = "test@test.com"
.CC = ""
.BCC = ""
.Subject = "Please order more stocks for " & Range("A1")
.Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

This is great, but I want it to instead send me an email if any cell in column A meets the criteria. I want the email to list for me the corresponding values contained in column B for the values in column A that meet this criteria. For example, Column A contains the stores that are selling stock, and Column B has the percentage sold. So once it hits 75%, I want an email generated that shows which stores require more stock.

This being said, I don't want it to run automatically once the criteria is met - this is because the values are taken from another system, and I essentially want to be able to run the script once I've finished entering in all of the values (which is done on a daily basis).

Any help at all would be appreciated.

Thanks heaps
rmk911

 

[rmk911]

EDIT to 3rd paragraph: I want the email to list for me the corresponding values contained in column A for the values in column B that meet this criteria.