Scheduled emailing with VB + Outlook

[BARIS]

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)

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