Access 2003 send email based on multiple date tests

[wbow]

HI,
Newbie to VBA so please bear with me.

I have a simple database with two tables, one of which records names of people, the second lists their qualification expiry dates.

I have a form where the engineering manager updates the qualification dates and I have set up the date fields to turn red when the dates are <=(Now()+90), [ie.within 3 months of expiry].
The form also lists the manager to whom the engineer reports and the e-mail address.

What I would like to do is send a e-mail to the managers listing all the engineers they are responsible for where the qualifications are <=(Now()+90), [ie.within 3 months of expiry] and the names of those qualifications.

Thank you

 

[KiaruB]

try this


Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
dim strsql as string
dim rs as dao.recordset

strSql = "SELECT * FROM table_QUALIFICATIONS"

set rs=currentdb.openrecordset(strsql,dbopensnapshot)

if not rs.bof and not rs.eof then

rs.movefirst

do until rs.eof

if date >= dateadd("m",-3,rs!QUALIFICATION_ENDDATE) and _
date < rs!QUALIFICATION_ENDDATE then

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = ""
.Subject = ""
.Body = ""
.Send
End With

Set appOutLook = Nothing
Set MailOutLook = Nothing

end if

rs.movenext

loop

end if

rs.close
set rs=nothing

 

[wbow]

KiaruB,
Many thanks, will give it a try.

 

[wbow]

KiaruB,
Tried the code, but this is where my newness to VBA kicks in as I could not get it to work.

I have 70 engineers, each having a possible 30 qualifications with associated expiry dates and a form where these are updated along with an e-mail address and Send E-Mail command button which I will link to your code.

I see the code will cycle through the records [rs] and test the date condition. Will this test each of the 30 dates and how do I;
On clicking the Send E-Mail command button populate the To; field with the name from the e-mail addressee text box?
In the subject of the e-mail how do I insert each of the qualification names that meet the date test criteria?

Apologies, but I really am this green to VBA.

Thanks for you patience.