Hi All, I am fairly new to Excel, I need to make the below work , it does trigger email but only for cell M6 then nothing? Can anybody help?
HTML:
Option Explicit
Private Sub Worksheet_Calculate()
If Me.Range("M6").Value < 3 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Worksheet_Calculate7()
If Me.Range("M7").Value < 3 Then
Call Mail_small_Text_Outlook7
End If
End Sub
Private Sub Worksheet_Calculate_8()
If Me.Range("M8").Value < 3 Then
Call Mail_small_Text_Outlook_8
End If
End Sub
Private Sub Worksheet_Calculate_9()
If Me.Range("M9").Value < 3 Then
Call Mail_small_Text_Outlook_9
End If
End Sub
HTML:
Option Explicit
Sub Mail_small_Text_Outlook()
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 Accusence Cameras Part Number: DS-2CD2346G2-I is at 3 units or below. New stock should be ordered." & vbNewLine & _
"Kind Regards" & vbNewLine & vbNewLine & _
"Stock Keeper"
On Error Resume Next
With xOutMail
.To = "jon@test.co.uk"
.CC = ""
.BCC = ""
.Subject = "Low Stock Alert - Acusense Cameras"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Sub Mail_small_Text_Outlook_7()
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 POE Hubs is at 3 units or below. New stock should be ordered." & vbNewLine & _
"Kind Regards" & vbNewLine & vbNewLine & _
"Stock Keeper"
On Error Resume Next
With xOutMail
.To = "jon@test.co.uk"
.CC = ""
.BCC = ""
.Subject = "Low Stock Alert - POE HUB"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Sub Mail_small_Text_Outlook_8()
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 Stock 99 is at 3 units or below. New stock should be ordered." & vbNewLine & _
"Kind Regards" & vbNewLine & vbNewLine & _
"Stock Keeper"
On Error Resume Next
With xOutMail
.To = "jon@test.co.uk"
.CC = ""
.BCC = ""
.Subject = "Low Stock Alert - Stock99"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Sub Mail_small_Text_Outlook_9()
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 STOCK 100 is at 3 units or below. New stock should be ordered." & vbNewLine & _
"Kind Regards" & vbNewLine & vbNewLine & _
"Stock Keeper"
On Error Resume Next
With xOutMail
.To = "jon@test.co.uk"
.CC = ""
.BCC = ""
.Subject = "Low Stock Alert - STOCK 100"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
HTML: