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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Michael M

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

JON_ROCKS

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

Michael M

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

JON_ROCKS

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

ADVERTISEMENT

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"
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
never mind i did a stupid!! 🤣🤣

Works perfectly, you Sir are a genius!!!
 

JON_ROCKS

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

ADVERTISEMENT

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.
 

Michael M

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

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
thats brill but since doing that the colour changed but all text appears on one line? & vbNewLine & vbNewLine & _ stopped working?
 

Michael M

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

Watch MrExcel Video

Forum statistics

Threads
1,127,875
Messages
5,627,405
Members
416,245
Latest member
Xterminat

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