outlook cell value formula

[kavanagh]

I'm trying to send a message via my own email email using formulas I bought through the site when a cell value reaches 186 or more
but I can't seem to get it to work it keeps telling me can't find project or library.
Ive place the first part of the code in the worksheet ant the second in the standard module

first part
'Place the code below into the worksheet module
Private Sub Worksheet_Calculate()
If Range("e10:e35") > 186 Then Call SendMessage
End Sub

second part
Sub SendMessage()
Dim objOutlook As Outlook.Application -can't find project or library
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("my email.ca")
.Subject = "CellValue > 186"
.Body = "CellValue: " & ActiveSheet.Range("e10:e35")
.Importance = olImportanceHigh
objOutlookRecip.Resolve
.Send
End With
Set objOutlook = Nothing
End Sub

even to print out a message to me at 186 or more would be great

any help would be appreaciated k

 

[Asator]

Have you taken a look at this?
http://www.rondebruin.nl/sendmail.htm

 

[kavanagh]

Thank you for responding, much appreaciated but I've tried that and nothing happens when that date is reached I'm not sure what I'm doing wrong

Here's the formula I'm using with excel 2003

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
On Error Resume Next
With OutMail
.To = "my email.ca"
.CC = ""
.BCC = ""
.Subject = "maintenance due"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub