Email alert VBA

Hi,

One way. I'm assuming there are several different dates in your column and you want the email sent if the latest date in the list is more than 21 days ago.

So introduce a cell named "CheckDate" and in this cell enter the formula:
=MAX(A:A)
assuming column A is the one containing the dates.

Then create a module level macro as follows

Sub CheckDate21()
    Dim OutApp As Object
    Dim OutMail As Object

    If Date - Range("CheckDate") > 21 Then

        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = "youremail@yourisp.blah"
            .Subject = "Three weeks have passed!"
            .Body = "See Subject"
            '.display
            .Send
        End With

        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
End Sub

Then decide what event you want to use to trigger the macro. If you want this to run the in the Workbook open event add a line of code

Call CheckDate21

If you want to run this at any time then create a button and attach the macro to the button.

Thanks, this is really helpful!

I never done anything in excel programing but I guess that I'll type the code in to the command window on that cell (If i press Alt+F8 when tanding on the cell)?


And I want the document to send an email when three weeks have passed, without me doing anything, no button or nothing I don't understand what you meant with the button macro attachment, sorry :/



EDIT: Just to be clear, I know jack about macros and stuff in Excel, I can make pretty tables, that's about it, so I don't know how to make macros work.

Date is in: 2012-11-27 format, but is it better to have it in 11-27-2012 or 27-11-2012 format? Don't know how the code line reads it.

Hi,

Alt-F8 will not help you since that just points to any macros which you already have. In order to create a macro you need to use Alt-F11.

I'm attaching a workbook with this macro included.

However you need to decide which event should trigger the macro. At the moment it will trigger every time you open the workbook. If you look in the Workbook Open event in the macro environment you'll see the line of code which calls the macro which exists in Module1.

You could instead have a clickable button on the face of the worksheet which would set the macro running. The choice is yours. There are other events that you could use. For instance any time you entered a new date in column A, or perhaps some other value in another column.

Okey I got the code to work, but I ain't getting mails :S


What is the trigger for the macro at the moment? I mean, I don't have a button or anything, so it must be when I open the document, right? But I ain't getting an email :S

Hi,

It works 'out of the box' for me.

As I said, the trigger is the workbook open event. Obviously you need to ensure there is a valid email address in B3. You can see that I made up an email address 'aladoran@something.com' so whilst no one (unless they happen to have such an email address) will receive it, you will find that you get a bounced Send email message in your inbox.

I am of course assuming you use Outlook as your email client and not the 'lite' Outloox Express version or any other software.