AutoEmail Module

Forge Link
Online Demo

Autoemails extension and vtiger CRM 5.1

• Each new lead will automatically be present under this add-on with the condition that the new lead contains an email address.
• Add in opt-out email functionality to leads which will be honored by this extension.
• The project will periodically call a mail script(cron job) which will send the follow-ups based on the time the lead has been in the system (now-createdtime)
• Manage the history of these emailing so the module can track what has been sent, and what timed auto-responder emails are still pending
• Allow for varying amount of follow-up emails – Choose an amount of follow-up emails to be administered via this module/interface. This would be a global setting as offering this setting per lead would become to cumbersome.
• All emails sent need to be customizable html-based – eg allow insert of company logo, and advertise other specials we may have during that time frame.
• These emails should be able to use lead data to insert customer name and other information for email personalization.
• Allow for varying time-frames for each of the follow-up emails – eg immediately, 1 week, 1 month, 6 months, 1 year
• Module should be compatible with vtigerCRM 5.0.4 and designed to be compatible with future updates to the base vtigerCRM system.
• Should include an interface to view which leads received a follow-up email originating from this module. This report should include time and date of each email, as well as the subject line. Also need the ability to filter by email address/lead.

This extension adds email opt out option to Leads

This module manages a set of email templates that are to be sent by the vtigerCRM system at defined times or events. There are three types of automatic emails:

• Entity Created/Altered/Deleted: This email template will be sent when the associated event occurs.
• Value-Triggered: This email template will be sent when the indicated field values are set to the given values.
• Scheduled Report: This email template will be sent at the established time with the selected report attached.

Each of these types can be configured to send the email template to:

• Users: vtigerCRM users
• Addresses: any fixed email address.
  • This field accepts the special value {ENTITY_MAIL} which will be established to the current entities email. For example, if we are creating an Account this variable will be set to the Account’s email.
  • Various emails can be indicated separating them with a comma
• Assigned user: indicates whether the owner of the affected entity is to be sent a copy of the email. If the entity is assigned to a group and this variable is set, then all members of the group will receive the email
• Assigned user’s group: indicates whether all members of the group to which the owner of the affected entity belongs are to be sent a copy of the email. If the entity is assigned to a group, this option is ignored.
• Exclude Triggering User. If the user who causes an event associated to an email is also to receive the email

The entity events (create, alter, delete and value change) can be configured to send the email immediately after the event or with a delay and on a determined schedule. The scheduled report can only be sent on a given schedule.

Most of this event checking and email sending is done by a script that must be called periodically in a cron or scheduled task. The granularity at which this script is called is important or some scheduled email may be sent more than once.

Each time the sendAutoEmails script is called it will fetch a list of pending emails to send. In the advent of retrieving a very large list (due to server downtime or a mass delete/change operation), the script will not have enough time to execute and send out all the emails before it is called again. In this case it will be possible that some emails will be sent twice or more depending on the workload and frequency at which the script is called. To avoid this situation I would recommend executing the script manually and waiting for it to end. For example, suppose we have had a few hours of downtime and we are not sure of how many pending emails are now ready to be sent. We stop the scheduled job and execute the script manually. Once it has finished all pending emails have been sent and we can activate the scheduled job once more.

In this same line I would recommend setting a frequency not to short, for example 5 minutes, which should give the script enough time to send out all it’s emails. Obviously your mileage will vary depending on the amount of autoemails you activate.

Some combinations may not make much sense like sending an email to an account every minute after it has been created. But we have preferred to leave the power and flexibility in the extension instead of forcing the users to do things in a determined way that may only be cutting off possibilities we don’t see at this moment.

The valid schedule types are:

• Reactive: as soon as the event occurs
• Reactive Delay: a number of minutes after the event occurs
• Schedule:
  • Each Minute
  • Hourly
  • Daily, indicate time of day
  • Weekly, indicate day of the week and time of day
  • Bi-Weekly, indicate day of the week and time of day
  • Monthly, indicate day of the month and time of day
  • Annually, indicate month, day of the month and time of day

Due to performance issues when using the application on a normal basis and trying to connect to an email server each time an event occurs we have decided to create a record in the database indicating that the event has occurred and an email must be sent. Then the email will be sent out the next time the scheduler is launched and will do the job in the background. Due to this, the immediate emails are really of type Reactive+2min which may find some inconsistency if a Reactive+1min email is programmed. Also due to the almost insignificant difference between Reactive+1min and immediate I would recommend not to program any Reactive+1min emails. This is also recommendable for Reactive schedules shorter than the sendAutoEmails script frequency. If the sendAutoEmails script is called once every 5 minutes, a Reactive+3min will not be sent until the next 5 minute period is over, or even worse in the case that “now”+3min falls into the “next” 5 minute period.

Some inconsistencies may occur when sending Reactive emails to deleted entities. Thanks to the fact that vtigerCRM really doesn’t eliminate the deleted information, most if not all the information related to a deleted entity will exist in the moment of sending a reactive email a month after elimination. But we can’t be sure that custom fields or related information will exist so these types of emails should not depend on elaborated field relation but be more in the line of simple static text.

Reactive Value-Triggered AutoEmails will only be sent the first time the criteria is true, after that the email will not be sent. This email can be forced again eliminating a record in the database or editing the autoemail and saving it again. This second method is more drastic and will activate the email for all entities that fulfill the criteria. Tweaking the database the email can be activated for only one (or various) entities.

Attachments. Any number of files can be attached to an email template. The files must be accessible by the apache user and left in a defined directory. By default this directory is /tmp, but it should be changed to a more useful (and controlled) directory. This must be done by editing the modules/Setttings/AutoEmail.php script and changing the $aeattachmentpath variable.

  var $aeattachmentpath = '/tmp/'; // Must end with a slash

The email template is an HTML enabled text box in which a variety of place holders or tags can be inserted depending on the base module the template is made for

On each autoemail, except scheduled reports, you will find an “Entities Sent to” button which will give a list of entities that the email has been sent to. For every email that this extension sends to an entity in the system it also attaches a reference that will appear in the “More Information”:Email section of that entity.

⇒ Let’s say I want to send out a mailing today to all my contacts whose last name’s first letter is ‘J’.

I would setup an autoemail like this:

Once saved we wait until the selected start time arrives. The mailing will start only if the sendAutoEmail.php is called exactly at the indicated time. You can also execute this script manually at this time.

Now we must wait for the mailing to finish and deactivate or delete the autoemail or it will be sent out again tomorrow at the indicated time. Be careful!!

If our mailing only needs less than an hour to finish you could set the Schedule Type to Hourly, but be careful if you get interrupted and don’t deactivate the autoemail the whole mailing will be sent again in the next hour. If our mailing takes more than one day you can do the same process but selecting the weekly or monthly schedule type. In any case remember to deactivated it.

⇒ Monthly newsletter

sendAutoEmail.php script must be executed on the selected day and time

Some examples that come to mind:

• Account creation, reactive=0: Welcome to our company
• Account creation, reactive=10080: You have been our client for a week
• Invoice status=paid, reactive=0: Thank you for your business
• Scheduled Client Report, bi-weekly
• Send monthly email, on the 4th day of the month, to all Accounts that have fieldA=X and fieldB between Y,Z

• When comparing values of an internal vtigerCRM field, the ID of the entity must be used. For example, if we want to trigger an email each time a certain account pays an invoice we will create an autoemail against the invoice entity, select the accountid field and use the ID of that account, not the name. This number can be consulted in the detailview of that account or on the listview as admin user
• As an extension to the above comment when using the “Assigned to” field to make comparisons we must use the user’s internal ID. This number is harder to find. You must access the User’s detalview in Settings. On the browser’s address box the URL finishes with record=ID, this number is the one that must be used in the comparisons.
• vtigerCRM saves all select box values internally in english (or in the language they were created if they are a custom field select box). When this module compares the values of select boxes it will use the saved value, not the translated one. So if you are using the application in spanish (for example) and see the select boxes with spanish text, when you create the autoemail you must use the english text which is being saved internally.

NOTE: We could change this way of working to make it more user friendly. Contact us.

Carlo Beschi from Studiosynthesis
I created an autoemail template - type triggered value - to inform my user whenever the field “associated to product” of a trouble ticket gets modified and contains the “vtiger” string.

This can’t be done easily. As stated in the documentation the current version of the module does not fetch values of other related tables when comparing value-triggered fields. So you can’t access “product description” from troubleticket you can only access the product_id which is the field that is in the trouble ticket. So if you want to send an email for a subset of products you will have to play with their ID’s and different autoemail definitions.

For example:
Description: email vtiger product between 6 and 14
Field: value triggered product id between 6 and 14

Description: email vtiger product 22
Field: value triggered product id equals

The body of the email will always be the same.

• There is a bug when editing the value-triggered autoemails which losses all the configured values. We have see the error during testing but haven’t been able to reproduce it systematically, it seems to happen randomly, so we haven’t been able to fix it. Need procedure to reproduce error so we can eliminate it. Got this one
• Make value-triggered comparisons more user friendly.
• Install concurrency mechanisms to avoid launching an event while one is still working. For example if we program an hourly autoemail and when it is launched the previous hour’s launch is still working don’t launch this one. Have to think this throughly.
• Add “auto deactivate” option. So an autoemail will deactivate itself once it has finished working. Could be very useful for on demand mailings.
• Getting a report of all emails sent can be achieved by consulting the table vtiger_entityautoemail. This could be grouped by entities.
• Getting a report of all pending emails is a lot more difficult. Some can be found in the vtiger_autoemailsched table but others would have to be “created” on the fly because they are really calculated dynamically. For example, an email scheduled to be sent every day, or monthly isn’t written anywhere as pending but calculated dynamically on the day it must be sent. We would need to generate a section on the report indicating the active autoemails that have periodic scheduling.
• Track email hits/views. Monitor email marketing responses, including open e-mails, click-throughs, ...

As with all our modules:

1. unzip files from top of vtigerCRM source directory
2. apply patch file: modules/Settings/autoemails.diff

    patch -p 0 < modules/Settings/autoemails.diff

3. execute config_autoemails.php script with

    http://yourserver/yourvtigercrm/index.php?module=Settings&action=config_autoemails

4. add the sendAutoEmails.php script as a cron or scheduled task to be launched each minute
5. patch file only adds new text strings to en_us files, if you are using a localized version you must add new strings to your local language file and translate them. The new strings can be consulted here:

   A /trunk/include/EntityRelations.php
   A /trunk/include/js/AutoEmailJS.js
   A /trunk/include/js/multiControl.js
   A /trunk/modules/Settings/AutoEmailDetailView.php
   A /trunk/modules/Settings/AutoEmailEditView.php
   A /trunk/modules/Settings/AutoEmail.php
   A /trunk/modules/Settings/AutoEmailSubclasses.php
   A /trunk/modules/Settings/autoemails.diff
   A /trunk/modules/Settings/class.multiControl.php
   A /trunk/modules/Settings/config_autoemails.php
   A /trunk/modules/Settings/CustomFunctionRegistry.php
   A /trunk/modules/Settings/DeleteAutoEmail.php
   A /trunk/modules/Settings/ListAutoEmails.php
   A /trunk/modules/Settings/RegisteredEmailFunction.php
   A /trunk/modules/Settings/RegisteredFunction.php
   A /trunk/modules/Settings/SaveAutoEmailEnabled.php
   A /trunk/modules/Settings/SaveAutoEmail.php
   A /trunk/sendAutoEmails.php
   A /trunk/Smarty/templates/AutoEmailDetailView.tpl
   A /trunk/Smarty/templates/AutoEmailEditView.tpl
   A /trunk/Smarty/templates/ListAutoEmails.tpl

   M /trunk/data/CRMEntity.php
   M /trunk/include/js/en_us.lang.js
   M /trunk/include/language/en_us.lang.php
   M /trunk/include/utils/ListViewUtils.php
   M /trunk/modules/Leads/language/en_us.lang.php
   M /trunk/modules/Leads/Leads.php
   M /trunk/modules/Settings/language/en_us.lang.php
   M /trunk/modules/Users/massdelete.php
   M /trunk/Popup.php
   M /trunk/Smarty/templates/SetMenu.tpl
   M /trunk/Smarty/templates/Settings.tpl

1. overwrite these files with original vtiger CRM version

   data/CRMEntity.php
   include/js/en_us.lang.js
   include/language/en_us.lang.php
   include/utils/ListViewUtils.php
   modules/Leads/language/en_us.lang.php
   modules/Leads/Leads.php
   modules/Settings/language/en_us.lang.php
   modules/Users/massdelete.php
   Popup.php
   Smarty/templates/SetMenu.tpl
   Smarty/templates/Settings.tpl

2. Access the database and eliminate entries related to autoemails in the vtiger_relatedlists table. In the vtiger_relatedlists table you will have (most probably at the end of the table) two records with the values

   7, 0, 'getSentAutoemails', 7, 'Sent Autoemails', 0
   7, 0, 'getPendingAutoemails', 8, 'Pending Autoemails', 0

   eliminate both of them

3. Alternatively you can eliminate the new files (see list above) although they don’t bother and the new database tables which will no longer be used (autoemailtypes, scheduletypes, autoemails_sched, vtiger_entityautoemail, autoemails)

This module is capable of sending out a very big amount of emails. You could easily be accused of spamming and breach your hosting contract. TSolucio will not take any responsibility for these actions. This module can and should be used for sending consented emails to your customers. This extension respects the opt-out fields of your leads, accounts and contacts but any mistake configuring the value-triggered emails or periodic scheduled emails can easily send out hundreds or thousands of emails.

• Add ConfirmReadingTo as an option in template creation and in sending code:

  Index: modules/Settings/AutoEmail.php
  ===================================================================
  --- modules/Settings/AutoEmail.php      (revision 902)
  +++ modules/Settings/AutoEmail.php      (working copy)
  @@ -431,6 +431,7 @@
         $mail->Password = $mail_server_password;
         $mail->From = $this->getFrom();
         $mail->FromName = $this->getFrom();
  +      if ($AcuseRecibo) $mail->ConfirmReadingTo = $mail->From;
         $toaddrs = $this->getTo();
         $toarray = array_unique(split(',',$toaddrs));
  
  

• Add option to send email to specific user when an error occurs sending autoemail. This way somebody would be able to easily see and revise those autoemails that haven’t been able to be sent
• Add a “Send Now” button on the template screen, specially useful for sending mailings.

Thanks go to:

• IMED Mobility for financing and making this extension a reality.
• PeaceWorks Computer Consulting Inc. and Sutherland-Schultz Inc. for sharing their code and idea with the community. This extension is based on their initial code and general solution to the problem