Show Cell Value in Email

[JON_ROCKS]

Hi Guru's

I am creating a stock taking database and need the automatic email to send the value in a specific cell in this case lets say M6.

I need the value of cell M6 to replace the text below where it reads "3 units or below" so it shows the exact remaining stock level.

Thanks in advance for all your help!

Sub Mail_small_Text_Outlook(sName 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 = "Hi Laura" & vbNewLine & vbNewLine & _
       [U][B] "The current stock of '" & sName & "' is at 3 units or below. New stock should be ordered." & vbNewLine & _[/B][/U]
        "Kind Regards" & vbNewLine & vbNewLine & _
        "Stock Keeper"
    
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - " & sName
        .Body = xMailBody
        .Display   'or use .Send
    End With
    
    Set xOutMail = Nothing
    Set xOutApp = Nothing

End Sub

 

[JON_ROCKS]

Got it thats perfect and works ace! thanks again for all your help!

 

[Michael M]

Got it thats perfect and works ace! thanks again for all your help!

Glad to help...??

 

[JON_ROCKS]

ok run into a new problem i am hoping you can help with?

I now need it to put the stock value in the emails but it is not working , the values are from column D but it will not add the values and sees the column as empty?

I know its something simple i am screwing up but cannot see what?!

Can you help?

Public i As Long

Private Sub Worksheet_Calculate()

Dim sName(3 To 39) As String

sName(3) = "Stock 1"
sName(4) = "Stock 2"
sName(5) = "Stock 3"
sName(6) = "Stock 4"
sName(7) = "Stock 5"
sName(8) = "Stock 6"
sName(9) = "Stock 7"
sName(10) = "Stock 8"
sName(11) = "Stock 9"
sName(12) = "Stock 10"
sName(13) = "Stock 11"
sName(14) = "Stock 12"
sName(15) = "Stock 13"
sName(16) = "Stock 14"

'When ading new stock be sure on the spreadsheet cells to add a -
'to any blank spaces where numbers here arent usedfor example here it
'goes from 21 to 25 so m22 m23 m24 would need - adding

sName(17) = "Stock 15"
sName(18) = "Stock 16"
sName(19) = "Stock 17"
sName(20) = "Stock 18"
sName(21) = "Stock 19"
sName(22) = "Stock 20"
sName(23) = "Stock 21"
sName(24) = "Stock 22"
sName(25) = "Stock 23"
sName(26) = "Stock 24"

sName(27) = "Stock 25"

sName(28) = "Stock 26"
sName(29) = "Stock 27"
sName(30) = "Stock 28"
sName(31) = "Stock 29"
sName(32) = "Stock 30"
sName(33) = "Stock 31"
sName(34) = "Stock 32"
sName(35) = "Stock 33"

sName(36) = "Stock 34"
sName(37) = "Stock 35"
sName(38) = "Stock 36"
sName(39) = "Stock 37"
For i = 3 To 39
If Range("D" & i).Value < 3 Then Call Mail_small_Text_Outlook(sName(i))
Next i

End Sub



Sub Mail_small_Text_Outlook(sName As String)
Dim xOutApp As Object, xOutMail As Object, xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
With xOutMail
.To = "jon@test.co.uk"
.CC = ""
.BCC = ""
.Subject = "Low Stock Alert - " & sName
.HTMLBody = "Hi Laura" & "<br><br>" & _
"The current stock: '<u><b><font color=""green"">" & sName & "</font></b></u>' is at <b><u><font color=""red"">" & Cells(i, 4).Value & "</font></u></b> Unit(s) remaining." & "<br>" & _
"New stock should be ordered with immediate effect." & "<br><br>" & _
"I look forward to your re-stocking of this equipment." & "<br><br>" & _
"Kind Regards." & "<br><br>" & _
"<i><b>Mr Stock Keeper</b></i>" & "<br>" & _
"My Company"
.Display 'or use .Send
End With
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[Michael M]

What is actually in Column "D" from 3 to 39 ??
It works fine for my dummy data ??

 

[JON_ROCKS]

Excel Work Sheet

Ok see the link to the attached work sheet mate, the code works but in the email popup it doesnt display the referenced amounts in column D when its <3

 

[Michael M]

I changed a cell in column "D" to reflect a low stock and it returned the correct email
However, I note that row 39, which is in the loop 3 to 39, is empty and therefor is < 3, so will create an email, but because it's empty returns no value....see screenshot No 2
Does that help ???

 

[Michael M]

To stop the empty last row returning a "blank" value in the email
Change your loop from

For i = 3 to 39

TO

 For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row

That will then only run the code on ALL populated rows !