Thanks for our kind response. First time i came to know about saving the file in excel start up folder. It was very useful.
But, now i couln't use either options,
1) Setting the macros security setting to low
2) Saving the file to excel start up folder ( as i need to save it in specific network drive)
So, am trying to use your idea to create a external VB script to perform the same function. This is the first time am trying to write VB script :-( Kindly correct me if am wrong totally... :-)
Also am am trying to use blat tool for sending email, since the email password changes frequently... and blat tool doesn't need password for mail id.
Here is the first part where am trying to check the deadline date in column "G" and sending the data in column "B" based on the date ( this is the modified version of your script )
and the output should be written to a text file. This text file content will be emailed through blat tool.
I know that the VB script below is wrong, but i don't know how to correct it... :-(
Dim oFilesys, oFiletxt, sFilename, sPath
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\WINNT\Profiles\PKumar\RA Tracking.xls")
Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oFiletxt = oFilesys.CreateTextFile("C:\WINNT\Profiles\PKumar\RA_Tracking.txt", True)
sPath = oFilesys.GetAbsolutePathName("C:\WINNT\Profiles\PKumar\RA_Tracking.txt")
sFilename = oFilesys.GetFileName(sPath)
'only run if between midnight and 2AM
If Hour(Now) > 1 Then
'setup basic HTML message header and footer
strHtmlHead = "<html><body>"
strHtmlFoot = "</body></html>"
'setup start of body of message
strMsgBody = "The following task(s) are due in less than 7 days<br />"
'set start of date range
Set rngStart = .Range("G2")
'find end of date range
Set rngEnd = .Range("G" & CStr(Application.Rows.Count)).End(xlUp)
'loop through all used cells in column G
For Each rngCell In .Range(rngStart, rngEnd)
'test if date is less than 7 days from today
If rngCell.Value - Int(Now) < 7 Then
'add to message - use task name from column B (offset -5)
'change as required
strMsgBody = strMsgBody & "Task: " & rngCell.Offset(0, -5).Text _
& " is due on: " & rngCell.Text & "<br />"
'Note last test time/date
rngEnd.Offset(1, -5) = Now
rngEnd.Offset(1, -5).NumberFormat = "dd/mmm/yy \a\t hh:mm"
'put message together
strMsg = strHtmlHead & strMsgBody & strHtmlFoot
Me.Close savechanges = True
Kindly advice :-)