Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Email with Excel File Closed
Is it possible to trigger an Outlook email based on a date (example, today's
date) stored in a cell in an excel file that is closed? For instance, the code below triggers the email if the file is open. would it be possible to make it work every day and keep the file closed? Thanks and thanks to Ron for the code below. Sub TestFile() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) With OutMail ..To = cell.Value ..Subject = cell.Offset(0, 1).Value ..Body = cell.Offset(0, 2).Value ..Send 'Or use Display End With Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Email with Excel File Closed
Filo
NO -- DownThePaint Where points are scored and games are won! "Filo" wrote: Is it possible to trigger an Outlook email based on a date (example, today's date) stored in a cell in an excel file that is closed? For instance, the code below triggers the email if the file is open. would it be possible to make it work every day and keep the file closed? Thanks and thanks to Ron for the code below. Sub TestFile() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) With OutMail .To = cell.Value .Subject = cell.Offset(0, 1).Value .Body = cell.Offset(0, 2).Value .Send 'Or use Display End With Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Email with Excel File Closed
You could retrieve values from a closed workbook using a routine from John
Walkenbach: http://j-walk.com/ss/excel/eee/eee009.txt Look for either: GetDataFromClosedFile or GetValue. And I don't know a way to return whether the value is the result of a formula or a constant. But since you're looping through column B, I would think opening that workbook and processing all the cells would be much more efficient. Filo wrote: Is it possible to trigger an Outlook email based on a date (example, today's date) stored in a cell in an excel file that is closed? For instance, the code below triggers the email if the file is open. would it be possible to make it work every day and keep the file closed? Thanks and thanks to Ron for the code below. Sub TestFile() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) With OutMail .To = cell.Value .Subject = cell.Offset(0, 1).Value .Body = cell.Offset(0, 2).Value .Send 'Or use Display End With Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email trigger | Excel Discussion (Misc queries) | |||
Can you set a "trigger" in excel to send an email? | Excel Worksheet Functions | |||
Auto notify a list of people via email when excel file is closed | Excel Programming | |||
Can reciept of an email in outlook express be used to trigger an even in excell | Excel Programming | |||
Using a checkbox to trigger an email | Excel Programming |