refer this code, use Cell
A1 and
will run the macro if the cell value
<0
1) Right click on a sheet tab and choose view code
2) Paste the event below in the sheet module.
3) Alt-q to go back to Excel
Note: Change
YourMacroName to the name of
your macro in the code.
If you want the code to work for another cell or more cells you can change
the range in the event.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 0 Then
'Call YourMacroName
End If
End If
End Sub
For sending mail from outlook
macro to create/display a Outlook mail with a small text message.
You must copy this macro in a standard module and not in the worksheet module, use .Display in the code to display the mail, you can change that to .Send
Do not forget to change Call YourMacroName to Call Mail_small_Text_Outlook in the Change event
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 & _
"Cell A1 is changed" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
On Error Resume Next
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub