Hello all,

I am fairly good at using Excel, but my VBA skills are pretty bad. Here is what I am trying to accomplish (I hope excel can allow me to do this):

I need to know when something will expire ahead of time. I have certain dates located in cells that an item will expire, for example, a product life of "10-Jul-06 - 11-Aug-09"

What I am trying to do is have an automatic e-Mail sent out "3 months ahead" of the time something will expire. So in this case, for the above example in August of 2009, I need an email trigger sent out in "May" stating that the item is about to expire. Sort of like an "alert".

I found some code that will allow me to run a macro which triggers an email. It works, but I need to have it automatically do it when I hit a certain date..not do it "manually" like when you run a macro back on the excel sheet.

Ultimately, I need an email sent out as soon as a certain day/month comes that will trigger an email message through Microsoft Outlook.


My Code so far (in VBA module):



Sub Mail_small_Text_Outlook()
'Working in Office 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "my email"
        .CC = "someone elses email"
        .BCC = "someone elses email"
        .Subject = "AUTOMATIC EXPIRATION NOTIFICATION"
        .Body = "This is an automated message generated to notify of something about to expire"
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
        
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
This is not what I want. If someone could please help me find a way to my solution, I would greatly appreciate it. I know there needs to be "IFs" in this, but I could not figure it out.

Maybe something like "IF Date=March,20,2010, SEND EMAILs" ??

Thank you so much!

Warren