Auto Email from Excel

  • Thread starter Thread starter kevin.fletcher
  • Start date Start date
K

kevin.fletcher

Has anyone developoed a vb script to automatically email
from excel to outlook when an alarm condition is raised in
the data of a spreadsheet?
 
Hi Kevin
when an alarm condition is raised ??
Click to expand...
I don't know what you mean by that

Try this

If the A1 value >100 the mail will be send

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 > 100 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