Looking for ideas on how to implement scheduled e-mails..... RRS feed

  • Question


    I have a client with a customized CRM that wants to send e-mails automatically to contacts on a monthly basis.


    The scenario is this:


    • There is a custom entity which I will call Entity A. There is 1:n relationship between a contact and Entity A. EntityA has a date field called Renewal Reminder.
    • A contact may have several Entity As with varying renewal reminder dates.
    • The client would like every month an e-mail to be sent to all contacts that have Entity As whose renewal reminder date is within the next 90 days.
    • The e-mail that is sent should be stored against the history record of the contact.

    The client specifically wants a contact to only receive one e-mail that lists all the Entity As that need renewing (e.g. if the client has three Entity As due for renewal they should only get one e-mail that lists the Entity As with their renewal dates and not three e-mails).


    The difficulty I'm having is how to aggregrate all the Entity As that are due into one e-mail. I'm mananged to do this in SQL by querying the filtered views and then using SQL to send the e-mail. I could then use SQL Server Agent to schedule the process. However, that doesn't seem very elegant and it doesn't write the e-mail to the history of the contact.


    I would be very grateful for people's thoughts on how to approach this.


    Thursday, September 11, 2008 7:05 AM


  • Hi Feridun,


    I would definately recommend using a Plugin.  Plugins can access the CRM web service which in turn can build the email using the email object reference and send it.  The benefit of this is that the email will be handled as if it was sent via the GUI i.e. in the history of the regarding object etc....

    You might want to come up with some funky way of scheduling the plugin.  I normally add an attribute of type bit to the entity and have the plugin registered on the update message of the same entity.  If the bit value is true the plugin will continue and process the mail.  At the end of the plugin execution you can use the plugin to "reset" the bit to false.

    You can then create a simple workflow that waits for a specific date and the only thing this workflow does is to update the bit field to true.  This update will cause the plugin to fire and in turn find the bit value to be true and send the email.

    At the end of the workflow have the workflow restart itself and then wait until the next month.

    You can refer to the SDK for plugin information and samples.


    I hope this helps.


    Kind regards

    Thursday, September 11, 2008 9:06 AM