Excel/VBA email notification

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

Hi again,

I was used to using the old code which didnt do what I wanted, thats why I left the email addresses out. But now I see your point on why there should be a column.

Attached is the modified spreadsheet containing example e-mail addresses with the designated columns.

The "send out" date column is the date I want the program to trigger. So, for this case, as soon as "January 10th, 2010" comes around, I would like the program to automatically email all three addresses giving a warning the product is going to expire in April 22, 2010.

Does this make more sense? sorry for leaving some of these things out..


Thanks again, looking forward to your responses!!

Hello, I'm new to all of this, and I just registered on this site. I know this is an old thread by I'm hoping I can still get a response on this. I've tested this Macro and it seems to work except that instead of sending the email it just opens my web browser. Any idea why it would do that? I'm using excel 2007 on windows 8.1.

Thanks in advance,

Jorge