daddyfoxuk
Board Regular
- Joined
- Nov 18, 2016
- Messages
- 68
So i have the below codes... When i hit F5 the code will run perfectly although i would like it to include cell A,B,C and D in the email body. But i want this to run every time the value of any cells in I going above 1.... Can anyone help as to whys it isnt running, kinda doing my head in now haha!
On Sheet4 (Bad Parts)
Module 1
Any Help would be great!!!
On Sheet4 (Bad Parts)
Code:
Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
NotSentMsg = "Not Sent"
SentMsg = "Sent"
MyLimit = 1
Set FormulaRange = Me.Range("I2:I100")
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook2
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell
ExitMacro:
Exit Sub
EndMacro:
Application.EnableEvents = True
MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description
End Sub
Code:
Public FormulaCell As Range
Sub Mail_with_outlook1()
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strto = "****"
strcc = ""
strbcc = ""
strsub = "Suspect Notification"
strbody = ""
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Sub Mail_with_outlook2()
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strto = "****"
strcc = ""
strbcc = ""
strsub = "Suspect Notification"
strbody = ""
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Last edited by a moderator:
