Sending an Outlook e-mail due to a condition in a cell

[neil]

I want to have excel automatically email a specific email
address each time a particular cell equals "Y" or "True".

Any ideas?

Thanks

Niel

 

[Ron de Bruin]

Example for cell A1

The change event will run the code when you change the cell
It must be placed in a sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
If Target.Value = "Y" Or Target.Value = "True" Then
Mail_with_outlook
End If
End If
End Sub

This sub in a normal module

Change Display to Send if you like it
Don't forget to set a reference to Outlook

How do you that:
1: Go to the VBA editor, Alt -F11
2: Tools>References in the Menu bar
3: Place a Checkmark before Microsoft Office Outlook ? Object Library
? is the Excel version number

Sub Mail_with_outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strto As String
Dim strcc As String
Dim strbcc As String
Dim strsub As String
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

strto = "(e-mail address removed)"
'Or this with the address in a cell
'strto = Sheets("Sheet1").Range("a1").Value
strcc = ""
strbcc = ""
strsub = "Cell A1 is changed"
strbody = "something you want"

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Display
End With
End Sub