Generate an automatic email from a cell condition in Excel or Access

[Seanwho]

I need to alert users by email when a date cell in Excel is two weeks away from expiry. Does anyone know of a way this can be done automatically as there several hundred potential emails to be sent out quarterly and at present this is done manually... Thanks

Sean

"He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever."
-Chinese Proverb

 

[petersdaniel]

Sean,

You can accomplish this by doing a VBA routine that is triggered when the date range meets the minimum test.

Here is an example of a VBA routine that launches Outlook and sends an e-Mail

Sub sendMessage(Optional AttachmentPath)

Dim olookApp As Outlook.Application
Dim olookMsg As Outlook.MailItem
Dim olookRecipient As Outlook.Recipient
Dim olookAttach As Outlook.Attachment

' create the Outlook session.
Set olookApp = CreateObject("Outlook.Application&quot

' create the message.
Set olookMsg = olookApp.CreateItem(olMailItem)

With olookMsg
' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Christopher Wyke&quot
olookRecipient.Type = olTo

' add the CC recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Robert Dil&quot
olookRecipient.Type = olCC

' set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set olookAttach = .Attachments.Add(AttachmentPath)
End If

' resolve each Recipient's name
For Each olookRecipient In .Recipients
olookRecipient.Resolve
If Not olookRecipient.Resolve Then
olookMsg.Display ' display any names that can't be resolved
End If
Next
.Send

End With
Set olookMsg = Nothing
Set olookApp = Nothing

End Sub petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein