Automatic Emails from Database.

[ConnorGiles]

Hi Guys so I am going to explain this in as much detail as possible. So pull up a chair.

My goal is that in my database we have products that have a date required, Some products have some leeway towards the date it is delivered.

However some products have no leeway and need to be at the site for a certain date only.

What I wish to accomplish is when a product on the urgent delivery table's delivery date comes to within a week of it needing to be delivered. An automatic email would be sent to me (to start with, other people can be added when it is finished) with a copy of the record.

I would like it to send in this format :

" MESSAGE FROM THE DATABASE! "

" [Company Here] needs [Product Here] to be delivered by [Date Required] "

There may be more then one product so if there is for that day then it should cascade like so

" [Company Here] needs [Product Here] to be delivered by [Date Required] "
" [Company Here] needs [Product Here] to be delivered by [Date Required] "
" [Company Here] needs [Product Here] to be delivered by [Date Required] "

This email will be sent twice - Once when the date required is a week away and once again when it is 3 days away.

Would there be any way of completing this - one thing I am worried about is the times the email will be sent and also the amount of times it is sent. because if one of these is wrong then the idea goes out of the window.

Does anyone have any ideas?

Thanks in advance :banghead:

 

[Frothingslosh]

I'm pretty sure it's not possible to do with macros, but if you drop into VBA, it's quite doable.

In a nutshell, you create an email object (file scripting object, technically), fill out the various necessary properties such as To, From, Subject, and Body, and then tell Outlook to email it. (Yep, it entails automation.)

While I can't help directly right now (the code I last wrote to do that was for a prior employer, and right now I'm running a crapload of reports for work), THIS THREAD is covering a very similar situation and may be of use to you.

 

[ConnorGiles]

Thank you very much Frothingslosh,

I shall check out the thread

 

[Minty]

How will you ensure Access is running to generate the email ?
Is the data stored locally or server BE db?
If this data stored in a SQL server back end you can easily accomplish this with a stored procedure and a scheduled job, no need for any user intervention or machine left on to run access.

 

[ConnorGiles]

We do indeed,

My database is stored within a server, I do have to open it but I don't think it would be too much hassle to just host it via the server.

How would I go about doing this Minty if I can get my DB mounted on the server.

 

[Minty]

If your data is in a SQL server (2008 onwards) it has an in-built dbMail system.
The set up of it is not overly difficult, and if you are familiar with SQL you can easily produce automated HTML formatted email reporting, without any user intervention.

It's a bit too involved to describe in detail here, but if you have a google around once you have the SQL mail set up I can give you some hints about the SP and HTML formatting if required. We use it for loads of automation. Simple things like who's not in today;

 

[bigalpha]

I have a bit of code I've used to generate an email based on a date field in a form. Mine is set to generate the email via command button but I'm sure you could automate it. (I slightly edited it to remove PII). It generate a block of email address for each class based on that particular date.

Private Sub cmdEmail_Click()
On Error GoTo cmdEmail_Click_Err

    DoCmd.SendObject , "", "", DLookup("emails", "qrycertemail"), "user100@hotmail.com", "", "Certificates for " & _
    "" & DLookup("classtype", "tblClasstype") & " Training Class taken on " & DLookup("trainingclassdate", "qrycertemail"), "** This is an automatically generated email**" & _
    "" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "If you have not already picked up your certificates, they are available for pickup Monday through Friday 0800-1600 at the " & _
    "" & " Office located in Building 999 . No appointment is necessary." & _
    "" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "If you received this email in error, or you have other questions, please respond to this email. " & _
    "" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Thanks," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Staff", True, ""

cmdEmail_Click_Exit:
    Exit Sub

cmdEmail_Click_Err:
    MsgBox Error$
    Resume cmdEmail_Click_Exit

End Sub