Show Cell Value in Email

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
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!


VBA Code:
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

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
Got it thats perfect and works ace! thanks again for all your help!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,050
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What is actually in Column "D" from 3 to 39 ??
It works fine for my dummy data ??
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,050
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 ???
1613257525525.png
1613257557249.png
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,050
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
To stop the empty last row returning a "blank" value in the email
Change your loop from

VBA Code:
For i = 3 to 39

TO

VBA Code:
 For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row
That will then only run the code on ALL populated rows !
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,765
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top