Exporting Emails via the Database

Overview

There may be occasions when you wish to export emails from your Sugar® instance. While the export action cannot be performed via Sugar's user interface, you can export emails by running a SQL query directly against your instance's database.

Use Case

As an example, we will run a query to retrieve all emails from Sugar as well as all emails related to a specific module. If you are using a database management tool (e.g. phpMyAdmin), the results can be exported. Alternatively, they can be stored in a new table on your database. The example queries are for MySQL databases and may need to be adjusted for instances using an Oracle, DB2, or Microsoft SQL Server database.

Prerequisites

• You must have access to Sugar's database via command-line interface or a data management tool such as phpMyAdmin.
• You must be comfortable with SQL queries and the Sugar database.
  Note: If you need assistance running the SQL query and already have a relationship with a Sugar partner, you can work with them to perform the necessary actions. If not, please refer to the Partner Page to find a reselling partner to help with your development needs. 

Steps to Complete

Retrieving All Emails

Use the following query to export all emails from your Sugar instance:

SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address, 
GROUP_CONCAT(DISTINCT bean.bean_module SEPARATOR ',') as Module_Attached_To , 
COUNT(DISTINCT bean.bean_module) as NumOfModulesAttached
FROM emails_text txt
INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
INNER JOIN emails ON emails.id= txt.email_id
GROUP BY emails.id 
ORDER BY emails.date_sent DESC

The query will return information about the emails including their text and the modules of the records to which each email is related. Please note that is will not return the actual record(s) to which the email is associated.

Retrieving Emails by Module

You can also export emails that are related to a particular module (e.g. Accounts or Contacts), but the query you will use differs for person-type and regular modules. Similar to the query above, it will return information about the emails including their text and the modules of the records to which each email is related. Please note that is will not return the actual record(s) to which the email is associated.

Person-Type Modules

Use the query below to retrieve all emails related to a person-type module (i.e. Contacts, Leads, and Targets). For this example, we will retrieve all emails related to the Contacts module. 
Note: Replace the module name for the third inner join to retrieve emails related to another person-type module (e.g. Leads). 

SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address, 
bean.bean_module as Module_Attached_To, concat(contacts.first_name,' ',contacts.last_name) 
as Record_Name_Where_Email_is_attached
FROM emails_text txt
INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
INNER JOIN emails ON emails.id= txt.email_id
INNER JOIN contacts ON contacts.id = bean.bean_id
WHERE bean.bean_module= 'Contacts'
GROUP BY emails.id
ORDER BY emails.date_sent DESC

Non-Person Type Modules

Use the following query to retrieve all emails related to a non-person type module. For this example, we will retrieve all emails related to the Accounts module. 
Note: Replace the module name for the third inner join to retrieve emails related to another non-person type module (e.g. Opportunities).

SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address, 
bean.bean_module as Module_Attached_To, accounts.name as Record_Name_Where_Email_is_attached
FROM emails_text txt
INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
INNER JOIN emails ON emails.id= txt.email_id
INNER JOIN accounts ON accounts .id = bean.bean_id
WHERE bean.bean_module= 'Accounts'
GROUP BY emails.id 
ORDER BY emails.date_sent DESC  

Application

If you are using a data management tool (e.g. phpMyAdmin), you can download the query results to your local computer. Alternatively, you can store the results in a new table in your Sugar database by wrapping your query in a CREATE TABLE statement. The following example creates a table using the first query provided in this article:

CREATE TABLE name_you_want_to_use AS
(SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address, 
GROUP_CONCAT(DISTINCT bean.bean_module SEPARATOR ',') as Module_Attached_To, 
COUNT(DISTINCT bean.bean_module) as NumOfModulesAttached
FROM emails_text txt
INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
INNER JOIN emails ON emails.id= txt.email_id
GROUP BY emails.id 
ORDER BY emails.date_sent DESC);