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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
MAybe this way
VBA Code:
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)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
       "The current stock of " & sName & " is at " & Cells(6, 13).Value & " . New stock should be ordered." & vbNewLine & _
        "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
 
Upvote 0
Thanks Michael

I am still learning but this is a great step, works perfect for m6 but i didnt forsee a small issue...

I need the cell of which ever the first vba references too based on the below example if possible so if M6 , M7 , M8 , M9 ETC... is referenced as below it shows that specific value and i can just keep adding cells indefinitely to the list

Now just to complicate things

VBA Code:
Private Sub Worksheet_Calculate()

    Dim sName(11 To 13) As String
    Dim i As Long
  
    sName(11) = "60 watt amp"
    sName(12) = "60 watt speakers"
    sName(13) = "Ampmeter screen"

    For i = 11 To 13
        If Range("M" & i).Value < 3 Then Call Mail_small_Text_Outlook(sName(i))
     Next i

End Sub

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 & _
        "The current stock of '" & sName & "' is at " & Cells(6, 13).Value & " units or below. New stock should be ordered." & vbNewLine & _
        "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
 
Upvote 0
So make the variable i as a global variable(outside the sub routine) and then change the code to
VBA Code:
Public i As Long
Private Sub Worksheet_Calculate()
    Dim sName(11 To 13) As String
    sName(11) = "60 watt amp"
    sName(12) = "60 watt speakers"
    sName(13) = "Ampmeter screen"
    For i = 11 To 13
        If Range("M" & 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)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
        "The current stock of '" & sName & "' is at " & Cells(i, 13).Value & " units or below. New stock should be ordered." & vbNewLine & _
        "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
 
Upvote 0
Thanks for the reply, this was my first thought too but i get a runtime error:

"Runtime error '1004': application defined or object defined error"
 
Upvote 0
One last thing in this line could i somehow make the " & sName & " and " & Cells(i, 13).Value & " values appear as bold and highlighted in say yellow somehow?

VBA Code:
"The current stock of '" & sName & "' is at " & Cells(i, 13).Value & " units or below. New stock should be ordered." & vbNewLine & _

I really do appreciate all your help, i have learned so much in a short time from you.
 
Upvote 0
You will need to change your .Bosy to .HTMLBody to do that
I have bolded and made it RED, you can change to yellow if you like, but yellow can be notoriously hard to read on a white background !!
VBA Code:
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" & vbNewLine & vbNewLine & _
        "The current stock of <b><font color=""red"">" & sName & " is at " & Cells(i, 13).Value & "</font></b> units or below. New stock should be ordered." & vbNewLine & _
        "Kind Regards" & vbNewLine & vbNewLine & _
        "Stock Keeper"
        .Display   'or use .Send
    End With
       Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
 
Upvote 0
thats brill but since doing that the colour changed but all text appears on one line? & vbNewLine & vbNewLine & _ stopped working?
 
Upvote 0
Ok, use this....NewLines are not vbNewLine when using HTML, they are <br>
Sorry, my bad there !
VBA Code:
Option Explicit

Public i As Long
Private Sub Worksheet_Calculate()
    Dim sName(11 To 13) As String
    sName(11) = "60 watt amp"
    sName(12) = "60 watt speakers"
    sName(13) = "Ampmeter screen"
    For i = 11 To 13
        If Range("M" & 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 of <b><font color=""red"">" & sName & " is at " & Cells(i, 13).Value & "</font></b> units or below. New stock should be ordered." & "<br>" & _
        "Kind Regards" & "<br>" & "<br>" & _
        "Stock Keeper"
        .Display   'or use .Send
    End With
       Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top