send automatic email

[blues66]

Good morning, I'm new in this forum and I'm trying to solve since some days a problem. After exploring several forum, I think I can here found the better experts.
I have a table which contains several fields with "user names" and other anagraphic data, included the fields "email" and "medical certificate expiration date". I need that 15 days (or "x" days) before the expiration date, the relevant user name can receive an alert email informing him to provide to renew his certificate.
I'm not so practice abt VBAm but I really appreciate your help and also your patience (sorry if some grammatical mistaken are present, I'm from italy)

Thanks a lot

 

[dkinley]

Hi and welcome to the forums!

You can use the code from this link to help you on your way ...

http://www.access-programmers.co.uk/forums/showthread.php?p=775884#post775884

And here are some links to help you learn your way around Access & VBA:
http://www.techonthenet.com/access/index.php
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.functionx.com/vbaccess/
http://www.lebans.com/

For your query to pick up those records you want, you can use the following for expiration date:

DateDiff("n",[ExpirationDate],Now())

and in the criteria ...

<15

-dK

 

[blues66]

Hi and welcome to the forums!

You can use the code from this link to help you on your way ...

http://www.access-programmers.co.uk/forums/showthread.php?p=775884#post775884

And here are some links to help you learn your way around Access & VBA:
http://www.techonthenet.com/access/index.php
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.functionx.com/vbaccess/
http://www.lebans.com/

For your query to pick up those records you want, you can use the following for expiration date:

DateDiff("n",[ExpirationDate],Now())

and in the criteria ...

<15

-dK

Hi dK,

thanks a lot for your help and for the precious links, very useful. Now I can send email from access but not yet automatically.

Can you tell me in which way I can insert the code by you suggested
-DateDiff("n",[ExpirationDate],Now())- in the following code? :This is my code :
____________________________________________
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("qrySendingMail", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(2)) = False Then
sToName = .Fields(2)
sSubject = "Expiration Date "
sMessageBody = "" & vbCrLf & _
"Dear " & .Fields(0) & vbCrLf & _
"" & vbCrLf & _
"We inform you abt the expiration date: " & .Fields(3) & " !!" & vbCrLf & _
"" & vbCrLf & _
"TextTextTextTextTextText." & vbCrLf & _
"" & vbCrLf & _
"Signature"

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
_________________________________
Thanks again for the support and for the patience.

 

[dkinley]

In your query, qrySendingMail, that is where you use the other bits of code ...

Create a new column, name it anything, but it should like the following:

Expire: DateDiff("n",[ExpirationDate],Now())

Rename the field [ExpirationDate] to the field name that you store the expiration date.

Then, in the criteria of this column, use < 15. This will limit the records returned from your query to the 15 threshold you wanted.

-dK

 

[blues66]

In your query, qrySendingMail, that is where you use the other bits of code ...

Create a new column, name it anything, but it should like the following:

Expire: DateDiff("n",[ExpirationDate],Now())

Rename the field [ExpirationDate] to the field name that you store the expiration date.

Then, in the criteria of this column, use < 15. This will limit the records returned from your query to the 15 threshold you wanted.

-dK

thanks dk, now I have the record exactly as I need .
The VB code run well in order to send email but in manual way. In which way I can now automatize the email sending procedure?

Thanks again

 

[dkinley]

Sorry for not responding sooner ... holiday break and all =]

The query you have is called from the code in the following thread ...

http://www.access-programmers.co.uk/forums/showthread.php?p=775884#post775884

Look at post #2 - it gives a good explanation. You will have to tweak and so forth to get it how you want since it is your application.

You can put this code on a button event, a timer event, a close event, etc., for some sort of automation.

-dK