Automatically send an email from excel if the condition met

[balakrishna24]

Hi All,

I wanted to trigger an email from an excel tracker. it is working fine if i run the macro manually but i wanted to convert this into automated process. i have added the "Private Sub Workbook_Open()" into the workbook and "Sub Create_Mail_From_List()" on the module 1. unfortunately it is not working.... can somebody verify the code and let me know where it has gone wrong. help help help

Code geos in workbook....

Private Sub Workbook_Open()
Application.OnTime TimeValue("02:00:00"), "Create_Mail_From_List"
End Sub

Code geos in module 1....

Sub Create_Mail_From_List()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Application.OnTime TimeValue("02:00:00"), "Create_Mail_From_List"
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 Columns("i").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "j").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear Team" _
& vbNewLine & vbNewLine & _
"Line Item - " & Cells(cell.Row, "B").Value _
& " From " & Cells(cell.Row, "C").Value _
& ", will Expire on " & Cells(cell.Row, "E").Value _
& vbNewLine & vbNewLine & _
"Only " & Cells(cell.Row, "H").Value _
& " Days Left for expiry" _
& " Please intiate PO to keep AMC active" _
& vbNewLine & vbNewLine & _
"Thank you Admin Team " _
& vbNewLine & vbNewLine & _
"" & _
""
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.Display
Application.Wait (Now() + TimeValue("0:00:01"))
Application.SendKeys "%s"
.SentOnBehalfOfName = """Facility Team"" xxxx@com"
'Or use Display.
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
Application.Quit
ThisWorkbook.Close savechanges = False
Application.OnTime Now() + TimeValue("00:01:00"), "save_exit"
End Sub

 

[Derek Brown]

Hello and welcome to The Board.
There are a couple of errors that come to mind:
You are closing the workbook (Thisworkbook.close).
Before closing the workbook you are cosing Excel (Application.Quit).

You didn't give the "save_exit" so I don't know what that would do.

Therefore you appear to be closing the workbook and the 'on time' event would have no code to run two hours later.

I use Task Scheduler to run a VB.net (.exe) application to open an Excel workbook and run a macro. The following describes the use of Task Scheduler in XP:
http://support.microsoft.com/kb/308569