Send email before role expiration after long searching and many attempts I definitely need your help.

On the website I am currently working on, people can buy a "Participant" role for 1 year.
I'm using ubercart to handle the purchasing process.

I am trying to set up an automatic email which is sent when the Participant role is about to expire. Lets say 1 month before.

I know there is the Role module in UberCart, but I really don't like it and I also think it is not flexible enough. I also know about the User Expire module, but I am not sure I can do what I want to with it.

I was trying to create a Rule which:
- runs on every Cron run
- scan all the users Expiry Date field 
- send an email to all the user with expiration closer than 1 month

Please give me some help...I am really out of ideas.

I hope I am missing a very easy way to implement it.

Thank you in advance,
guidout
Create a custom module, implement hook_cron(), it is nothing but a function which will be called whenever cron runs.

Here is an article explaining what are hooks.

For code reference install examples module.


Implement hook_cron()
Load all the instance of the computed field, where you store the expiry date

Either you can directly query the database using db_select()
Or, use field_attach_load() to load only that field instances, instead of loading whole user object. Here is an example of how to use field_attach_load().

Check the expiry date.

Send a mail using drupal_mail() if the role is going to expire.



Note: You probably want this cron to run only once a day, for that install Elysia Cron and configure this hook to run once everyday by 12:00 am.