Create an Automatic Email Sender with Outlook Through Access

[lukascollings]

At my company we work in a lab so our staff needs to re-certify certain safety courses on a yearly basis and so on. To remind them I have to manually send them an email saying that their certification is expiring soon and they need to update. We have all of this information in a database and I am hoping to pull queries from this to send emails automatically for me. Say if it is expiring within 30 days the database will automatically send the emails. I am new to Access so this has not been the easiest task to accomplish. I was able to get to the point where I created a from that listed each staff with their courses and expiry dates and beside it I put a send email button. But when you click send it only sends for the certain person. I want to create it so that you can click the button and it sends to everyone in the list who is expiring in 30 days. Hopefully in the future this can update itself without the click of a button. Any help would be greatly appreciated. I have prowled the internet and found some helpful websites and youtube videos that have helped in some ways, but not getting me to where I need. One youtube video (https://www.youtube.com/watch?v=KlzrWoH_Z-0) had exactly what I wanted but it did not work, assuming this was due to the fact that I have Access 2007 and he was using Acces 2010, therefore I could not create a navigation form.

[ranman256]

You have a form with a list box that shows the query of those expiring.
a button will scan the list, grabbing the email, then sending the warning.

sub btnSend_click()
for I = 0 to lstBox.listcount-1
    LstBox = lstBox.itemdata(I)
    vEmail = lstBox
   
    SendEmail vEmail, "Your acct will expire", "expires on" & vDate
next
end sub

'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!!   checkmark OUTLOOK OBJECTS in the vbE menu, Tools, References
'-------

Public Function SendEmail (ByVal pvTo, ByVal pvSubj, ByVal pvBody, optional pvFile ) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

On Error GoTo ErrMail

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody

    if not IsMissing(pvFile) then .Attachments.Add pvFile, olByValue, 1
    
   .Send
End With

EmailO = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function

ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function

[lukascollings]

Hmm thank you for your help but when I click my button nothing happens...

[orange]

There's info here that may be useful.

[Micron]

What's your definition of "automatically"? If you want it to be so that you don't have to interact with a form, you need a trigger such as an AutoExec macro whose action is to Run Code when the database opens. The code has to be a function in a standard module. That function has to get a recordset (list) of recipients and their email address (probably from a table) and whatever else you'd need, loop through it and send the email like Ranman256 outlines. You likely do not want this to happen every time someone opens the db, so your table would need fields (perhaps yes/no) that identify whether or not to send the email (no if checked because already sent) and maybe to identify those who should be part of the process versus those who are not. Therefore, the code has to include an update as part of the loop to change this flag to no after the email is successfully sent. A form is the easier route for sure, but I would not consider it to be very automatic.

[lukascollings]

You are quite right about that! thank you for the help! I will try to play around with Ranman256's code to see if I can get what I want. Access is a lot of fun to use but can be tricky!!