Email from Excel using VBA

[Busymanjohn]

Hi everyone, Poser for you ... I have a spreadsheet I'm using to keep track of course material and updates. However as this sheet contains hundreds of entries, I wish to automate it a little bit more, and add a macro to automatically send a notification to the owner via e-mail if the last update was later than a year ago. It has to use Lotus Notes to send the e-mail.


The e-mail sent would need to be populated automatically with recipient (from the spreadsheet), Subject and body. The body will need to insert some data items from the spreadsheet into it's text.


Using the example below (Age is calculated with a formula: =TODAY()-E2&" days" ), I wish the body to say something like 'Your course Course 1 has not been updated since 01/03/2010 and is due for a review'


Italic words to be inserted from the spreadsheet, and e-mail to be pulled from the corresponding row, Column C.


In this example, it should send an e-mail to David (Row 2), but not Cathy (Row 3), provided the criteria is as stated above, older than a year, or 365 days.


A B C D E F

1 Course Owner Email Create Date Last Update Age

2 Course 1 David David@somewhere.com 01/03/2009 01/03/2010 458 days

3 Course 2 Cathy Cathy@somewhere.com 23/10/2009 23/09/2010 252 days

 

[Busymanjohn]

Sorry , I should have said, this needs to be Excel 2003 version. Thanks

 

[Luke M]

Take a look at Ron's tools on sending email. He has several different scenarios to help you out:

http://www.rondebruin.nl/sendmail.htm


Additionally, you may need to have some sort of If statement in your code, to check if the email needs to be sent, but that should be fairly easy to code, something like


For each c in Range("F2:F100")

If c.Value > 100 then 'How many days late

'Sendmail code goes here

End If

Next