Automatically Send Email Reminder upon opening Main Menu Form

[Cheri676]

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. 

[#2]

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).

[#3]

@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

[#4]

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.