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

  • Thread starter Thread starter neil
  • Start date Start date
N

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
 
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
 
Back
Top