Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMAIL
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMAIL
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Transfer Email addresses from spreadsheet to email address book | Excel Discussion (Misc queries) | |||
Email editor closes when forwarding Excel-embedded email | Setting up and Configuration of Excel |