Send Each Row Every Day On A Specified Time

HI Experts,

I have a small requirement.

I want send the each row every day on a specified time. For instance, I will send row1, today at 2.00 AM and row2 tomorrow at 2.00 AM and row the subsequent day day at 2.00 AM etc.,

Option Explicit


Sub Send_Files()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range, FileCell As Range, rng As Range


    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    Set sh = Sheets("words")


    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon


    For Each cell In sh.Columns("G").Cells.SpecialCells(xlCellTypeConstants)


        'Enter the file names in the C:Z column in each row


        If cell.Value Like "?*@?*.?*" Then
            Set OutMail = OutApp.CreateItem(0)


            With OutMail
                .To = cell.Value
                .Subject = "Testing" & cell.Offset(0, -5).Value
                .Body = cell.Offset(0, -5).Value & "(" & cell.Offset(0, -4).Value & ") = " & cell.Offset(0, -3).Value & vbNewLine & vbNewLine & "test1 " & cell.Offset(0, -2).Value & vbNewLine & vbNewLine & "test2 " & cell.Offset(0, -1).Value
                .DeferredDeliveryTime = cell.Offset(0, 2).Value & " 02:00"
	    .send	
            End With


            Set OutMail = Nothing
        End If
    Next cell


    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

When I tried this code, it is giving Run time error message "Outlook does not recognize one or more names". I am not sure what am I missing here. Instead, if I use .Display in place of .Send it is composing the mail with correct values (including the delivery time).

Also is it possible that, every day mail is sent automatically, instead of I running the Vb code manually...?!

Thanks in advance.

Re: Send Each Row Every Day On A Specified Time

Thanks for your reply Dave.

Application.OnTime
  TimeValue ("02:00:00"), "My Macro"

I have added the above lines of code in my macro and the Compiler error shows 'Argument not optional'.

Below line of code in the Workbook open event

Application.OnTime TimeValue("02:00:00"), "My Macro"

Kindly suggest where I am going wrong.

Thanks in advance.

Re: Send Each Row Every Day On A Specified Time

Hi Dave,

You got it right.!
I just copied those lines of code from the link which you have mentioned and it's been some couple of years since I have looked at some vb code.

But, still the old problem remains, where if I replace 'Display' with 'Send', run time error is shown up.

Thanks for your time.

HI VBA Noob,
Thanks for your reply. I have used the correct macro name.

Re: Send Each Row Every Day On A Specified Time

Hi experts,

Could someone help me in this?

Thanks for your time.

Re: Send Each Row Every Day On A Specified Time

Hi Daniel,

Sorry for the delayed reply.

Thanks for taking time to reply to my post.

I am using Microsoft Excel 2003. The option is already checked and when I have added the lines of code you mentioned it gave the compile error: "User-defined type not defined".

Am I missing anything else..?