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

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

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