Scheduled emailing with VB + Outlook

Hi,

Is there a way to combine scheduled emailing with VB codes in Ms Office Outlook ?


Here is my working VB code created by admin, (thanks to him once again)

Code:

---

Sub SendEmailRowByRow()
   
    Dim OutApp      As Object
    Dim OutMail    As Object
    Dim strBody    As String
    Dim LastRow    As Long
    Dim eMailIDs, i As Long
    Dim varBody
   
    Const StartRow  As Long = 1    '<<< adjust to suit
   
   
    If Not Application.Intersect(Range("H:H"), ActiveSheet.UsedRange) Is Nothing Then
       
        LastRow = Range("I" & Rows.Count).End(xlUp).Row
       
        eMailIDs = Range("I" & StartRow).Resize(LastRow - StartRow + 1)
       
        For i = 1 To UBound(eMailIDs, 1)
           
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
           
            varBody = Range("a" & StartRow + i - 1).Resize(, 8).Value
            strBody = Join(Application.Transpose(Application.Transpose(varBody)), vbTab)
            On Error Resume Next
            With OutMail
                .To = eMailIDs(i, 1) 'email from corresponding row goes here
                .CC = ""
                .BCC = ""
                .Subject = Range("C" & i).Value & ", account expiration date notification"
                .Body = strBody
                'You can add a file like this
                '.Attachments.Add ("C:\Users\USER\Desktop\info.doc")
'                .Display
                .Send
                Application.Wait Now + TimeSerial(0, 0, 3)
                Set OutMail = Nothing
                Set OutApp = Nothing
            End With
            On Error GoTo 0
        Next
    End If
   
End Sub
     

---

And please find below the helpme.xls file running with above VB code.

In column E, you can see the exp date of each customer's account exp date.
What I would like to do is to force outlook to send email to each customer, It shall be sent one week before each exp date.

In outlook there is a scheduled emailing function but is not working for me. On the other hand, it's not handy as I have to edit every single email submission date one by one.

Do you have any idea about how to set it?


Thanks in advance
Boris