Excel or Access to auto send email

  Picklefactory 08:04 18 Apr 07
Locked

Hello folks. Just a general opinion required at the moment, please. I might need to create something to monitor due delivery dates against actual delivery dates. It's pretty easy to use an Excel wbook and conditional formatting to highlight late deliveries, but what I'd like is an automated email sent to a couple of relevant people as soon as an item becomes late. That also might not sound too hard, but what I think might be a problem, is this. Is there a way for this to happen even if the program is not currently open and running? And would this sort of thing be easier to achive in Access or Excel? (Assuming it is possible at all)
Thanks

  xania 08:56 18 Apr 07

If the program is not running, then that's it. The only thing I can suggest is that you run the program automatically using Schduled Tasks.

  Picklefactory 09:12 18 Apr 07

OK, so that seems to answer the 'send while not running' question, any opinions on which, if either, would be the easier package to set up emails from? Access or Excel?

  Picklefactory 09:36 18 Apr 07

Been having a dabble.I can put a hyperlink in an Excel cell to open an email eg mailto:[email protected]?Subject=Web Site Question&body=body text
This will create an email but not send it.
2 questions: -
Is there anything I can add that will actually complete sending of the email automatically.
Can I have a simple IF statement that could check dates and then action the hyperlink depending on results.
Or am I talking rubbish and barking up the wrong tree entirely?
I'm not familiar with task scheduler, but I guess I could use that to run this wbook on a daily basis.

  Picklefactory 10:15 18 Apr 07

Me again, I've since found this code, which works and also doesn't upset the Outlook security. Next question, I don't know how to run this automatically dependant on result of IF statement or simple sum, can I do that?
Cheers

Sub Send()

Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.To = "[email protected]"
.CC = "[email protected]"
.Subject = "TEST EMAIL"
.Body = "THIS IS THE BODY"
.NoAging = True
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True

End Sub

  Picklefactory 11:38 18 Apr 07

Cracked it
I altered the above code as follows and just added an IF statement to generate a 1 or 2 in adjacent cell, code will check range on workbook opening and send email if required.
Maybe this might aid someone else.

Sub auto_open()
Dim Cell As Object
' If the values in D1:D20 are greater than 1...
For Each Cell In Range("D1:D20")
If Cell > 1 Then

Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.To = "[email protected]"
.CC = "[email protected]"
.Subject = "EVIL SUPPLIER NOTICE"
.Body = "EVIL SUPPLIER HAS FAILED ON DELIVERY, PLEASE CHECK SHEET FOR DETAILS AND THEN BEAT THEM"
.NoAging = True
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True
End If
Next Cell

End Sub

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5