Forum Discussion

Cheri676's avatar
Cheri676
Copper Contributor
Aug 24, 2022

Automatically Send Email Reminder upon opening Main Menu Form

I've got a fairly good grips on understanding the basics of Access but I'm fairly new to coding and can't really find anything that explains how to do what I need. 

 

So the overall aim that I want this code to do is upon opening my Main Menu form reminders for tasks due to be emailed out to the employees looking after that task. So I want an email reminder sent out 30 days before due date and then another sent when the task becomes overdue. I want to do this for range of things but currently am just looking at licence expires for every expiry. 

 

So that you have a clear Idea of what my database is looking like  below is a list of table and Queries.

Tables:

EmployeeT (Headings: EmployeeID, FirstName, LastName, PhoneNumber, PersonalEmail, WorkEmail, Department, Active, AssignedToID)

DriversLicenceT (Headings: LicenceID, EmployeeID, LicenceNumber, LicenceExpiry, LicenceType, Copy of Licence)

AssignedToT (Headings: AssignedToID, EmployeeID, AssignedToEmployeeEmail

SmashLicenceT (Headings:SmashLicenceID, EmployeeID, Smash Licence(this is a yes/no tick box), SmashLicenceExpiry, CopyOfSmashLicence

 

Queries SQL Code:

AllActiveEmployeeLicenceExpiryDuein30DaysQ: 
SELECT EmployeeT.FirstName, EmployeeT.LastName, DriverLicenceT.LicenceNumber, DriverLicenceT.LicenceType, DriverLicenceT.LicenceExpiry, AssignedToT.AssignedToEmployeeEmail, [LicenceExpiry]-Date() AS Expr1
FROM AssignedToT INNER JOIN (EmployeeT INNER JOIN DriverLicenceT ON EmployeeT.EmployeeID = DriverLicenceT.EmployeeID) ON AssignedToT.AssingedToID = EmployeeT.AssignedToID
WHERE ((([LicenceExpiry]-Date()) Between 1 And 30) AND ((EmployeeT.IsActive)="Active"));
 

AllActiveLicenceExpiryOverdue

SELECT EmployeeT.FirstName, EmployeeT.LastName, DriverLicenceT.LicenceNumber, DriverLicenceT.LicenceExpiry, [LicenceExpiry]-Date() AS numday, AssignedToT.AssignedToEmployeeEmail
FROM AssignedToT INNER JOIN (EmployeeT INNER JOIN DriverLicenceT ON EmployeeT.EmployeeID = DriverLicenceT.EmployeeID) ON AssignedToT.AssingedToID = EmployeeT.AssignedToID
WHERE (((EmployeeT.IsActive)="Active") AND (([LicenceExpiry]<Date())=-1));
 
OverdueSmashLicenceExpiryQ:
SELECT EmployeeT.FirstName, EmployeeT.LastName, SmashLicenceT.SmashLicenceExpiry, AssignedToT.AssignedToEmployeeEmail
FROM (AssignedToT INNER JOIN EmployeeT ON AssignedToT.AssingedToID = EmployeeT.AssignedToID) INNER JOIN SmashLicenceT ON EmployeeT.EmployeeID = SmashLicenceT.EmployeeID
WHERE ((([SmashLicenceExpiry]<Date())=-1) AND ((SmashLicenceT.SmashLicence)=True) AND ((EmployeeT.IsActive)="active"));
 
SmashLicenceExpiryDuein30DaysQ
SELECT EmployeeT.FirstName, EmployeeT.LastName, SmashLicenceT.SmashLicenceExpiry, AssignedToT.AssignedToEmployeeEmail
FROM (AssignedToT INNER JOIN EmployeeT ON AssignedToT.AssingedToID = EmployeeT.AssignedToID) INNER JOIN SmashLicenceT ON EmployeeT.EmployeeID = SmashLicenceT.EmployeeID
WHERE (((SmashLicenceT.SmashLicence)=True) AND ((EmployeeT.IsActive)="active") AND (([SmashLicenceExpiry]-Date()) Between 1 And 30));
 
I also have Multiple Item Forms based off of the above queries but am wanting the reminder emails sent upon opening the Main Menu. 
 
This how I want the Email itself set up 

 

        .Body = "Please be aware that " & FirstName & LastName & " Licence is Due for renewal in the next 30 days. Licence Expiry is -" & LicenceExpiry & ",<P>" & _

                    "Please update New Licence Expiry date and upload copy of New Drivers Licence" & ",<P>" & _

                    "Kind Regards" & ",<P>" & _

                    "Kym Wilson"

        .To = AssignedToEmployeeEmail

               .cc = PersonalEmail (Can't get this to work either so any help here would be greatly appreciated)

        .Subject = "Licence Expiry Due in 30 Days " & Now()

 
I have previously been able to get this work but only by clicking button which defeats the purpose of this database 

 

THANK YOU IN ADVANCE!!! Happy to provide for info if required. 

 

access
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Aug 24, 2022
    if the code is working on the Button, just copy the code to your Main Form's Timer Event.
    Make sure to Disable the Timer when it got triggered (Me.TimerInterval = 0).
    • Cheri676's avatar
      Cheri676
      Copper Contributor
      Aug 25, 2022

      arnel_gp 

       

      This partial works but not completely. So I want the email reminders to be automatically sent upon opening the main menu (See below) 

      This is the code I am using 

      'SEND EMAIL REMINDER FOR LICENCE EXPIRY IN 30 DAYS

      Dim O As Outlook.Application
      Dim M As Outlook.MailItem

      Set O = New Outlook.Application
      Set M = O.CreateItem(olMailItem)

      With M
      .BodyFormat = olFormatHTML
      .HTMLBody = "Please be aware that " & FirstName & LastName & " Licence is Due for renewal in the next 30 days. Licence Expiry is -" & LicenceExpiry & ",<P>" & _
      "Please update New Licence Expiry date and upload copy of New Drivers Licence" & ",<P>" & _
      "Kind Regards" & ",<P>" & _
      "Kym Wilson"
      .To = AssignedToEmployeeEmail
      .CC = PersonalEmail
      .Subject = "Licence Expiry Due in 30 Days " & Now()
      .Display

      End With

      Set M = Nothing
      Set O = Nothing

       

      This will open my outlook but wont put the emails in from the form because I haven't directed it to form that I need to use and I don't know how to do this? below is the form I need the code to take the information from (p.s. I know personal email is not in there ) 

       

       

      Thanks in advance

       

       

       

       

       

       

       

       

       

      • arnel_gp's avatar
        arnel_gp
        Steel Contributor
        Aug 25, 2022
        you open a recordset of all expiring and loop through each record and send the email using your code.
        you then need another table to save the license and the expiry date, so next time you check this table and if the expiring license is found, it will not create email again.

Resources