Under the Covers of Email in Sugar 5

bsoremsugar —  October 22, 2008 — 17 Comments

There have been many changes to the Sugar Email Module since the Ajax-based email client was introduced in Sugar 5.0. This article describes what we have done to improve Email in the Sugar 5.0 and 5.1 releases.

First, let’s do a quick review of the email functionality in SugarCRM. Email is used in a variety of different ways including 1) sending “personal” emails from individual users to leads and contacts, 2) sending out “campaign” emails to large recipient lists through the Sugar Campaigns module, 3) archiving email from customers via the email client in the Sugar application and through integrations with MS Outlook and Mozilla Thunderbird, 4) monitoring inbound “personal” email addresses via POP3 or IMAP on a “personal” user basis (for example, joe.user@example.com) and 5) monitoring inbound “group” email addresses also via POP3 or IMAP (for example, sales@example.com).

In Sugar 5.0, we introduced an entirely new AJAX-based email client built into SugarCRM for sending and reading email. Also in Sugar 5.0, we expanded on the Sugar concept of Personal Mail Accounts and Group Mail Accounts.

Personal Mail Accounts – Every user in your Sugar system has the ability to set up Sugar as their personal email client using IMAP or POP3 to monitor inbound email and SMTP or sendmail for sending outbound email. This type of email is intended to be read and managed by the individual user and only shared with other users once it is imported into the Sugar DB.

Group Mail Accounts
– You can also monitor a group email address such as sales@example.com. Again using IMAP or POP3, Sugar allows you to import this email directly into the Sugar DB and create relationships between the email and other Sugar objects like Opportunities and Cases. Additionally, with emails imported using a Group Mail Account you can trigger Workflow rules (Sugar Professional and Enterprise only).

More details about the changes to email in Sugar 5.1 are discussed below.

Design issues in Sugar 4 and earlier versions

In the Sugar 4.5.1 and prior releases, there were a few design issues that we explicitly set out to address in the 5.0 and 5.1 releases.

Excessive database growth

In 4.5.1, Sugar imported all email content (whether belonging to personal or group accounts) directly into the database. So over a period of time, the Sugar 4.5.1 database could, and often did, grow enormously if you monitored very active email addresses. This was especially unexpected when using Sugar as a personal email client over an IMAP connection as users and sys admins didn’t expect this configuration to automatically archive all email into the Sugar DB.

Additionally, because all email was directly imported into the Sugar DB in 4.5.1, there was no provision to filter email beforehand, especially for spam emails. In contrast, in Sugar 5.x, only email headers are initially downloaded and displayed from the server. The actual email content is obtained only when you read the email or you import the email. No database records are created until the import step in Sugar 5.x.

Also during the 4.5.1 import process, Sugar checked for any Contacts, Leads, Accounts, Users, or Targets with an email address that matched the From email address of the imported email. If there was, Sugar explicitly linked the imported email with those records. Because of the de-normalized schema design in Sugar 4 around email addresses, that meant Sugar created a linking row in the db for each type of link object, which often led to excessive growth of the database.

Lots of stored email with lots of linked relationships meant unnecessarily large databases.  Sugar 5.x addresses this issue by providing a single link from the archived (imported) email to possibly multiple CRM object types.

Slow POP3 Connections

During the 4.5.1 email import process, Sugar would check if an email on the mail server already existed in the Sugar DB by doing a duplicate check function. If the email is not already in the Sugar DB, the email module would import the email from the mail server to the Sugar DB.

For IMAP connections, this approach worked very well. The IMAP protocol provides for querying mail servers to check if any new mail arrived after a given date. This allowed any subsequent call to the IMAP mail server to be very, very fast even if you don’t delete email from the mail server.

However, this design was very slow for POP3 connections. The POP3 protocol does not provide this date range check, i.e., “find new email since we last checked”. This meant Sugar had to do a duplicate check for every email on the POP3 mail server. So imagine if you had thousands of emails in the mail account you want to monitor via POP3 and you left every email on the server, then Sugar had to step through every email every time in order to determine if any new email has arrived.

The best possible design that makes a POP3 configuration fast is to delete email from the mail server after it has been imported into Sugar. Sugar 4 allowed you to configure POP3 connections that left email on the server after importing into Sugar. Sugar 5 no longer allows that configuration for POP3.

Note that starting with Sugar 5.0, all POP3 emails are deleted from the server after they are imported. Sugar 5, however, does provide the “Leave Messages on Server” setting for IMAP connections beginning with Sugar 5.1.0a.

Limited user interface

  • The overall UI was not intuitive in 4.5.1.
  • The UI would allow you to view only one email account.
  • For each Group Mail Account, you could map only one Group Folder to that account.
  • The concept of Sent and Trash folders did not exist in the Email UI. For instance, the Sugar system couldn’t automatically save a copy of the email in the Sent folder of an IMAP account.

Sugar 5 delivers a new email system

All of these issues were addressed in Sugar 5 but it is important for developers and sys admins to understand how the Sugar 5 email system works. Here are detailed steps on how to use email in Sugar 5.0 and 5.1.

Making Sugar 5 auto-import similar to 4.5.1

If you want all emails to be automatically imported like in 4.5.1, then simply create a Group Mail Account in the Email Admin screen and define a destination Group Folder for the monitored email (i.e., have an entry in the field “Assign To Group Folder”). Then set up the Sugar Scheduler system and enable the “Check Inbound Mailboxes” scheduler. This process will import the full content of all messages into the Sugar system from the monitored mailbox on a regular basis.

Note that while the Sugar 5.x DB has a much more efficient schema design when compared to the 4.5.1 version, this method will still cause the database to grow, perhaps significantly, as the entire content of imported emails is automatically stored.

Group Mail Accounts

Group mail accounts are designed to have multiple users monitor and respond to email that comes into the mail account. A group mail account of sales@example.com, for instance, may be associated with all your sales representatives.To monitor a group email address, the system administrator needs to configure the monitored email accounts in the Admin screen and configure the Sugar scheduler to process email coming into those accounts.

    Group Mail Account WITH Group Folder – The recommended approach for Group Mail is to use a Group Folder. A Group Folder essentially provides storage for incoming emails. It also provides for the convenient administration of access privileges. Users that have visibility to a Group Folder (via the Group Folder “Assign to Team” field available in Sugar Professional and Sugar Enterprise only) will automatically see the Group Folder in the Email tab.

    In this configuration, Sugar will periodically connect to the mail server and automatically import the entire email into the Sugar DB. When imported into the Sugar DB, it is possible to automatically create Cases, execute Workflows or create relationships between the email and other Sugar objects like Opportunities and Cases.

    This configuration works best when the mail server has a powerful spam filter to help you prevent importing spam emails. You also need to plan to have enough database storage space to store a large number of emails.

    Group Mail Account WITHOUT Group Folder - In order to mitigate database growth and still give multiple users access to a Group Mail Account, you can also create a Group Mail Account without a Group Folder. In this scenario, the Sugar system will not automatically import email into the Sugar DB. Rather the inbound email scheduler process will connect to the mail server and cache the headers of email messages in the Group Account.

    In order to subscribe to a Group Mail Account without a Group Folder, each user must click on the Settings button in the Email tab, click on the Accounts tab and then add the Group Mail Account to the list of email folders in the Email tab. When using Group Folders as described above, the Group Folder will automatically appear in the user’s folder pane in the Email screen.

    Similar to personal email described below, emails in a Group Mail Account without a Group Folder must be manually imported into the Sugar DB. That means Workflows and linking to other objects can only happen once the user clicks the “Import to Sugar” button in the email. The exception to this is with emails that have the Case macro in the subject line. Those emails are automatically imported into the Sugar DB in all circumstances so that the automatic case logic is always executed.

Personal Mail Accounts

In the case of monitoring personal email, each user needs to configure his personal email account in the Email tab. When a user sets up such an account, the Sugar system downloads just the headers of all the email messages, but not the email body, and puts the headers in the email_cache table in the Sugar DB.Once the user clicks on an email to read it, the email message body is now downloaded from the mail server and put into the local file system in the cache directory. However at this point, the email is still not “imported” into Sugar and therefore it is neither linkable to other objects nor visible to other users.

Note that this process of querying the mail server multiple times and storing as little data as possible in the Sugar DB works well for mail servers like qmail, MS Exchange, GMail, Yahoo and others. However there are many mail servers that don’t allow more than certain number of connections within a specified time. So when you click on any message, the mail server may refuse further connections. If you run into this issue, it’s best to use a Group Mail Account with Group Folder and have all of the email imported into the Sugar DB on a periodic basis.

    Import Mail – To link the email to other objects and make the email visible to other users, the user must import the email into the Sugar system from the mail system. An “Import to Sugar” button is presented to the user when the email is opened. When clicked, the Sugar system downloads the entire email again from the mail server and puts it in the email table. During the import process (similar to archiving an email in the Sugar Plug-In for Outlook), the user is presented the option of explicitly linking the email to a Sugar object such as an Opportunity or Case.

    Check Mail – The “Check Mail” button in the Email screen checks mail for all configured personal email accounts only. So if you have configured more than one account (i.e. GMail or Yahoo), Sugar will do the check mail process for both the accounts and all the folders within the accounts. This check mail process will only get the headers of the messages. Once you click on a particular message, the Sugar system will go to the mail server and download the message.

    Synchronize – The purpose of synchronize action is to bring the mailbox in sync with the mail server. So in this scenario, the Sugar system will delete all the emails headers from the email_cache db table and check mail for all the accounts in all the folders.

Using Monitored Folders

For both types of mail accounts (Group and Personal), we have provided the facility to subscribe to multiple folders within the account. When you set up an account and select IMAP as the protocol, next to Monitored Folders text box field, there is a select button. When you click on that, it shows a list of available folders to subscribe to. You can also select a Trash folder where if you delete an email from an IMAP or POP folder, the Sugar system will delete the email from the email_cache table, delete the email from the mail server and for an IMAP account, and move the email to the Trash folder on the mail server you have configured for this account. You can also configure the Sent Folder for an IMAP account in which when you compose an email and if you select this account in the from dropdown field, we will create a copy of that message in the selected Sent folder of an IMAP account.

Relationships between Emails and Contacts/Leads/Targets

If you have a Contact, Lead or Target whose email address matches the email address of the imported email then those emails will automatically appear in the Contact/Lead/Target’s history subpanel. Note that this only happens after an email is imported which happens automatically with Group Mail Accounts with Group Folders or by clicking the “Import to Sugar” button in all other configurations. Unlike in Sugar 4.5.1 and earlier, you do not need to explicitly link the email to the records. Sugar will implicitly link the email to the records via the email address and not by creating a row in the database as used to happen in 4.5.1.

You can also now manage all the relationships of an email starting in Sugar 5.1. This was not possible in Sugar 5.0. Also, beginning in Sugar 5.1.0 patch A, we have provided a “View Relationships” button which will show the DetailView of an email and you can view/add/remove all the related records.

17 responses to Under the Covers of Email in Sugar 5

  1. 

    Hi

    I have problem with outbounding e-mails.
    I have newest SugarCRM 5.1.0a.
    I use IMAP protocol.
    I use INBOX.Sent folder to store outbounding e-mail, but e-mails are stored only in local folder of SugarCRM

    Please help me :)

  2. 

    Hi

    I have problem with outbounding e-mails.
    I have newest SugarCRM 5.1.0a.
    I use IMAP protocol.
    I use INBOX.Sent folder to store outbounding e-mail, but e-mails are stored only in local folder of SugarCRM

    Please help me :)

  3. 

    when you send an email, you need to select this IMAP account in the from drop down list and after the send has been done, you need to do check mail on INBOX.Sent folder and then you will see this email in INBOX.Sent folder (I will fix this part so that you don’t need to do check mail process in the next release of 5.1)

  4. 

    when you send an email, you need to select this IMAP account in the from drop down list and after the send has been done, you need to do check mail on INBOX.Sent folder and then you will see this email in INBOX.Sent folder (I will fix this part so that you don’t need to do check mail process in the next release of 5.1)

  5. 

    Hi,

    I use an external mail client. After having updated today to Version 5.1.0a (Build 4837) Community Edition, I cannot see the mails in the folders: My Email, My Drafts and Sent Emails.

    I am really keen on good advice, as my mail is no longer accessible.

    Kind regards,
    Jonas

    » Operating system type and version (Linux, Kernel version 2.6.18-028stab053.17 )
    » Sugar Suite version Version 5.1.0a (Build 4837) Community Edition
    » Webserver type and version (1.3.41 (Unix))
    » PHP version (5.2.5)
    » MySQL server version (5.0.45-community-log)
    » I use Firefox v3.0.3, released September 26, 2008

  6. 

    Hi,

    I use an external mail client. After having updated today to Version 5.1.0a (Build 4837) Community Edition, I cannot see the mails in the folders: My Email, My Drafts and Sent Emails.

    I am really keen on good advice, as my mail is no longer accessible.

    Kind regards,
    Jonas

    » Operating system type and version (Linux, Kernel version 2.6.18-028stab053.17 )
    » Sugar Suite version Version 5.1.0a (Build 4837) Community Edition
    » Webserver type and version (1.3.41 (Unix))
    » PHP version (5.2.5)
    » MySQL server version (5.0.45-community-log)
    » I use Firefox v3.0.3, released September 26, 2008

  7. 

    Correction
    I use the Sugar e-mail client.

  8. 

    Correction
    I use the Sugar e-mail client.

  9. 

    I resolved the issue by running Quick Repair and Rebuild Quick Repair and Rebuild and Executing the suggested script.
    Jonas :-)

  10. 

    I resolved the issue by running Quick Repair and Rebuild Quick Repair and Rebuild and Executing the suggested script.
    Jonas :-)

  11. 

    Nice concept introduction. With brand new 5.1d installation on IIS / PHP 5.2.6 NTS, FastCGI / Memcached / SQL Express 2008, I see the following problems, after matching with the above explanations:

    – Mails which are auto-assigned to a case are not visible in the Group folder anymore. The system has (only) a GroupMail account with GroupFolder. A single user account has not any configured personal mail since not necessary. The mail can be viewed from the case and new ones are shown as unread, but where is the mail itself visible and how to get notifications about new case-mails?

    – The incoming E-mail overview shows corrupted/strange characters in the FROM and TO fields for certain incoming mails – seems to be on non HTML mails. How can this be fixed? Which encoding must be set for the SQL Server database used by SugarCRM – blank DB was created previously by hand?

  12. 

    Nice concept introduction. With brand new 5.1d installation on IIS / PHP 5.2.6 NTS, FastCGI / Memcached / SQL Express 2008, I see the following problems, after matching with the above explanations:

    – Mails which are auto-assigned to a case are not visible in the Group folder anymore. The system has (only) a GroupMail account with GroupFolder. A single user account has not any configured personal mail since not necessary. The mail can be viewed from the case and new ones are shown as unread, but where is the mail itself visible and how to get notifications about new case-mails?

    – The incoming E-mail overview shows corrupted/strange characters in the FROM and TO fields for certain incoming mails – seems to be on non HTML mails. How can this be fixed? Which encoding must be set for the SQL Server database used by SugarCRM – blank DB was created previously by hand?

  13. 

    Thanks for laying out the email differences between v4 and v5. Much appreciated. Cheers!

  14. 

    Thanks for laying out the email differences between v4 and v5. Much appreciated. Cheers!

  15. 

    hello, Fine, the dog is allowed on all the furniture, but is not allowed to sleep with the … The dog can sleep on the bed whenever he wants!

Trackbacks and Pingbacks:

  1. Under the Covers of Email in Sugar 5 | Email Marketing Tool - November 13, 2008

    […] samirgandhi added an interesting post today on Under the Covers of Email in Sugar 5Here’s a small readingEmail is used in a variety of different ways including 1) sending “personal” emails from individual users to leads and contacts, 2) sending out “campaign” emails to large recipient lists through the Sugar Campaigns module, 3) archiving … […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s