Automated email reminders based on date

[maGemme]

Hi guys,

I'm building a database that manages licenses for a software that we sell.
I have a small problem and I can't find the proper keywords to finds a solution in the forums.
Here's what I have:
A table with demographic info as well as a 2 date fiels (Date_Activ and Date_Renew). Date_Activ is the activation date of the license and Date_Renew is the renewal date.
I also have a function that sends emails
Finally I have a form based on a query that shows all the licenses that will be due to renew in less than 30 days.

What I want to do is write a vba script that will send a reminder email for every license that is due to expire.

So in my head I have something like,

For every License_ID that is on the form
Call my function to send en email

I'm still fairly new to vba programming and have never looped anything... I need help and ideas.

Thanks
P.S. Using Access 2010

 

[pbaldy]

Here's an example of looping code:

http://www.granite.ab.ca/access/email/recordsetloop.htm

I'd loop the query, not the form.

 

[maGemme]

Thanks for the quick response.

I did see that code while searching for an answer but I must admit I have no idea how to implement it in my DB because I don't fully understand what the code does and there's no explanation with it.

 

[pbaldy]

Okay; post back if you have a question.

 

[maGemme]

Ok so I managed to find some code that worked (see below).

The problem I have now is that I need to set a flag in my table that indicates an email has been sent already. To do that I have a field called "ReminderSent" which is either null or 1 and my query selects records which are not null.

I tried entering a simple Me.ReminderSent.Value = "1" but that doesn't do anything.

What can I do to set my flag at the end of each loop?

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
 
Set db = CurrentDb
 
Set qdf = db.QueryDefs("QryLicenseLess30days")
 
Set rst = qdf.OpenRecordset()



With rst
    Do Until .EOF
                sToName = "something@somewhere.com"
                sSubject = "Invoice #: " & ![Account Name]
                sMessageBody = "Email Body Text "
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
    Me.ReminderSent.Value = "1"
                
  .MoveNext         'Move to the next Record
  
  Loop
End With

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

 

[pbaldy]

In the code I linked to, something similar was done with these lines:

RS.Edit
RS("cpeDateTimeEmailed") = Now()
RS.Update

 

[maGemme]

Well I'm not using the code you linked to, since I'm using a different method I get errors.
Anything other ideas?

 

[maGemme]

Ok so I cheated a bit but achieved similar goal...

Basically after the loop has been run, I run an sql query that updates my records to a 1.

Not the classiest method, but it works.

DoCmd.SetWarnings False

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
 
Set db = CurrentDb
 
Set qdf = db.QueryDefs("QryLicenseLess30days")
 
Set rst = qdf.OpenRecordset()



With rst
    Do Until .EOF

                sToName = "something@somewhere.com"
                sSubject = "Invoice #: " & ![Account Name]
                sMessageBody = "Email Body Text "
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
     
  .MoveNext         'Move to the next Record
  
  Loop
End With

DoCmd.RunSQL "UPDATE [01- Tbl_Licenses] SET [01- Tbl_Licenses].ReminderSent = '1' WHERE ((([01- Tbl_Licenses].ReminderSent) Is Null) AND (([Date_Renew]-30)<=Date()));"
    
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

DoCmd.SetWarnings True

 

[pbaldy]

I realize you were using different code, but the method would have been the same. All you would have had to change is the field name and value to match yours:

...
sToName, , , sSubject, sMessageBody, False, False
.Edit
!ReminderSent = "1"
.Update
.MoveNext