Auto email (1 Viewer)

I’m new to access programming, really love it.

I’m trying to get some code that auto emails a reminder to anyone whose
training records in form, based on the expiry date field. I want it to
to send it once a week.

I have another form trigger based on date too.

I need to add a specific title and body message.

Really, really appreciate any help for sample code.

Sorry if I have put this in the wrong place.

Here is a custom function I use for that:

Function vcSendEmail_Outlook(sSubject As String, sTo As String, sCC As String, sBcc As String, Optional sBodyHTML As String)
Dim OutApp As Object
Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon



Set OutMail = OutApp.CreateItem(0)

OutMail.To = Replace(sTo, ",", ";")
OutMail.CC = Replace(sCC, ",", ";")
OutMail.BCC = Replace(sBcc, ",", ";")
OutMail.Subject = sSubject

If Len(sBodyHTML) > 0 Then OutMail.HTMLBody = sBodyHTML


OutMail.Display 'Send | Display
Set OutMail = Nothing
End Function

Add that to a standard module and use it by calling it like this:

Call vcSendEmail_Outlook ("Your Subject","emailTo","emailCC","emailBCC","Your Message")

Cheers,
Vlad

Thanks for email. Will this be triggered by expiry date field and do
I specify the form and field ref?

Really appreciate your help.

Cheers

Vinny

It's not that easy Vinny

The way this kind of functionality is usually implemented is to have a query (or define a recordset in VBA ) that captures your intended target (for example all users with an expiry date < Today); then in VBA you loop through that recordset and build your list of email addresses as a semi-colon delimited string (default Outlook email separator). Finally you call the function I provided you and put your emails into the bCC field if the message doesn't need to be customized for each user. If it does (like you want to say in the body of the email Dear FirstName LastName....) then you call the function from inside the loop where you get the email address of the user and the related info (names, user specific training courses or materials that are expired etc.).

Cheers,
Vlad

Thanks Vlad. Sorry for me being a bit dopey, the expiry date is in a table and form. Do I need to make reference to the said table. Vlad is thee a good reference book to learn coding. I love trying programming, I get a bit of code from you tube, but to be honest it doesn’t look logical for me, or do you know other sources of code.

Thanks so much for your time and patience. Do you do paid work?

Cheers

Vinny’s

Hi Vinny,

There are many, many sources to learn programming. Books, forums like this one, Access related websites, etc. Do some web searches and you will find lots of sites. I have just created my new site (forestbyte.com) where you can find some useful VBA code samples and some free utilities to download. There is also a "resources" page where I list just a few of the numerous Access websites available.

The code I suggested should be behind a button on your main switchboard (the opening form of your database). The idea is to send the emails all at once (you mentioned once a week). So yes, you build a query based on your table to identify your "expired" users, then you loop through the records of the query and depending on the type of message you want to send out (generic message <Dear user, your training has expired> vs. personalized message <Dear John Smith, your First Aid Level 1 has expired>) you collect the emails and concatenate them in a semi-colon delimited string (for the first case) or collect all user specific info (first name, last name, email, course, etc.) to be used in the emails (for the second case).

And yes, I do paid work, but I don't think this is a venue to discuss that, we are all here to learn and help.

Cheers,
Vlad

Thanks Vlad, much appreciate your help.

Cheers

Vinny