Below I have some code (thanks GS) to send an email automatically with
a custom message. Is it possible to run when the workbook is first

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim dteOlderThan As String
Dim szMailTo As String, szCustomMsg As String

Set rng = Intersect(Target, Range("X:X"))
dteOlderThan = Cells(Target.Row, "B")
szMailTo = Cells(Target.Row, "L")
szCustomMsg = Cells(Target.Row, "C")

If Not rng Is Nothing Then
If Target.Value = "No" And _
dteOlderThan <= (Date - 14) Then _
Mail szMailTo, szCustomMsg
End If
End Sub

Sub Mail(szMailTo As String, szCustomMsg As String)
Dim OutApp As Object
Dim OutMail As Object
Dim strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strcc = ""
strbcc = ""
strsub = "Important message"
strbody = "A SPAD Advice Notice is required for signal:" &
vbNewLine & vbNewLine & _
With OutMail
.To = szMailTo
.cc = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub




Hi Al,

Yes, you can modify it to work that way, but there's a few issues you should
consider first.

This code is structured to be fired if you enter "No" into any cell in
column "X". To have it fire when the workbook opens, you'll need to loop
through all the cells in column "X" to process. This means you'll be sending
emails every time the file is opened. Somehow, I don't think that's what you
want to do!

That said, you'll need some way to tell which recipients have already been

Also, any changes you make while it's open won't get processed until you
re-open it. If the changes you make after opening affect what was emailed
when it was opened, it's already too late.

You need to be sure how you want it to work. Maybe some others here might
have additional input for your consideration.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send Email 4
Send Email VBA 4
Email on value change (Formula) 6
E-mail if Cell B33 = Yes 2
?????? 2
Mail Multiple Sheets via PDF Q 5
Converting an Excel file to PDF Q 1
Time Format Issue (Email message body) 2