EMAIL

[AL]

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
open?

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 & _
szCustomMsg
With OutMail
.To = szMailTo
.cc = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Thanks

AL

 

[Guest]

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
emailed.

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.

HTH
Regards,
GS