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(v arBody)), 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