Prev: Create a new Excel spreadsheet from Word VBA
Next: Copying autonumbered paragraphs and retaining original numbers
From: vbnewbie on 11 Feb 2010 04:08
I use Excel 2002 and I am trying to use an expression to calculate any
entries older than 14 days and then send an email as a reminder. Date is in
column1. I have the following code
For j = 1 To lastcell
If Cells(j, 13) <> "" And Cells(j, 14) = "" And Cells(j, 1).Value > (Now -
14) And Cells(j, 15) = "" Then
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
On Error GoTo cleanup

Set OutMail = OutApp.CreateItem(0)
etc...

The only bit thats not working is the And Cells(j, 1).Value > (Now - 14) -
it sends email to all regardless of the date.

Please help!

Thanks


From: Doug Robbins - Word MVP on 11 Feb 2010 04:57
For questions about programming with Excel, you should post to the
Microsoft.Public.Excel.Programming Newsgroup. However, what you need to use
is the DateDiff() function to test the date in Cell(j, 1)

Check in out in the Visual Basic Help file to get the correct syntax for its
use.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"vbnewbie" <vbnewbie(a)discussions.microsoft.com> wrote in message
news:4D78805E-831F-4215-824A-2580A6E993B2(a)microsoft.com...
> I use Excel 2002 and I am trying to use an expression to calculate any
> entries older than 14 days and then send an email as a reminder. Date is
> in
> column1. I have the following code
> For j = 1 To lastcell
> If Cells(j, 13) <> "" And Cells(j, 14) = "" And Cells(j, 1).Value > (Now -
> 14) And Cells(j, 15) = "" Then
> Dim OutApp As Object
> Dim OutMail As Object
> Dim cell As Range
> Application.ScreenUpdating = False
> Set OutApp = CreateObject("Outlook.Application")
> OutApp.Session.Logon
> On Error GoTo cleanup
>
> Set OutMail = OutApp.CreateItem(0)
> etc...
>
> The only bit thats not working is the And Cells(j, 1).Value > (Now - 14) -
> it sends email to all regardless of the date.
>
> Please help!
>
> Thanks
>
>
 | 
Pages: 1
Prev: Create a new Excel spreadsheet from Word VBA
Next: Copying autonumbered paragraphs and retaining original numbers