I'm having trouble setting my macro up properly so that when a cell reaches a certain value it sends an email to certain employees. These cells are refreshing every 30 minutes or so from a website the spreadsheet is connected to, for whatever reason excel kind of ignores the refreshed values.
Any thoughts?
Here is the coding I am using.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C11"), rng) Is Nothing Then
If Range("C11").Value < 0 Then Sheet19.Mail_with_outlook
End If
End If
EndMacro:
End Sub
Sub ABT_with_outlook()
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")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
strto = "
[email protected]"
strcc = "
[email protected]"
strbcc = ""
strsub = "Important message"
strbody = "ABT has reached its stop loss." & vbNewLine & vbNewLine & _
"Please review this position immediately."
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Emailing from Excel using webquery
