Writing a Sugar report on email module.

I did just successfully open and refresh the accounts query from yesterday. 

Interesting. If you're able to query accounts, it should ideally work the same for emails as well - Maybe the same temporary issue that you faced with accounts is causing the issue with emails as well.

Can you try a simple query for example:

select * from emails limit 5;

or

select * from emails_text limit 5;

If not, can you try running a manual query in your Excel's Microsoft Query and see if you are able to see results from running the queries above

I connected to the table through the wizard and I am able to see records (partially covered window). I then created a new query not using the wizard and input each snippet as SQL, but neither worked. The errors were the same on each: 

(I hit OK)

My workday is almost over, but I'll be chasing this again on Monday and will check back in then. Thank you very much for your help.

Some specifics: I need to export all emails with their full content, including the body text. I also need to add in the SugarID for the account with which the email is associated, the same ID that shows up in the address bar when viewing a client. As I extract, I'll need to limit the date range within the coding and run multiple queries to have better control over the size of the downloads. I'm downloading 9 years of data, 766530 emails. 

Interesting, I had the exact same popup as you had and I didn't get the "Could not add the table" error. Maybe try to query for specific fields and see if that works?

select id, name from emails;

If the Microsoft Query is working, then you could probably use that to insert data into excel instead of using Saved Queries? Apologies, unfortunately, this is something related to Excel and I have no idea why it doesn't work. 

The only alternative that I can suggest is to use a Visual Query Editor tool like MySQL Workbench where you can create a simple DB connection and start running queries out of it. It can also export data to Excel/variety of other formats.

It's a simple visual tool provided by MySQL itself: https://dev.mysql.com/downloads/workbench/

And you just need few seconds to connect to a DB and run queries:

Once you run the query, you can click on "Export" to save it as Excel/CSV etc.

Thank you! I have some things I have to complete first, but I'll circle back to this later. Would you be able to help me by tweaking the code to insert the account id and the date parameter field? I'm certain you can do it in a fraction of the time it would take me to look up the parameter specifics and make the changes myself.

Sure, happy to help.

If you're looking at email contents, emails_text is the table that you need. It has From/To/Email Body

Subject comes from emails.name

If the emails are sent from Accounts module, the link is established in a table called "emails_beans", with bean_module = 'Accounts'.

Finally, use the date_sent in emails table to filter when this email was actually sent.

So combining all three, the query that you need is something like:

select eb.bean_id as account_id,
       e.id as email_id,
       e.name as subject,
       e.date_sent as date_sent,
       et.from_addr,
       et.to_addrs,
       et.cc_addrs,
       et.description -- Use this or description_html
from emails e,
     emails_beans eb,
     emails_text et
where e.id = eb.email_id
  and e.deleted = 0
  and eb.deleted = 0
  and e.id = et.email_id
  and et.deleted = 0
  and eb.bean_module = 'Accounts'
  and e.date_sent between '2020-01-01' and now();

Thank you! I'll try this later. I really appreciate your help. 

Now I need to add SQL to my list of things to teach myself. 

Again, thank you for all of your help. I give up with this task at least. No matter what I try, it tells me object Emails does not exist, when I'm looking at an Emails table in SugarCRM and in the Schema list. I've spent enough time on it now that I'm going to recommend we hire someone to help with the extraction of data from SugarCRM.

Selection behavior does depends on deployment specifics
do you have balancing server? is there a number of servers to maintain big database? are their any replications? 

selecting data from server and user laptop can also matter - on enterprise scale, Sugar database could be deployed on the number of servers, therefore the context of the schema/database should be given for successful selection

Therefore, you may try to add the schema/database name explicitly into your query to get either data or real response from server
for Ult/Ent you may use Admin -> Diagnostic Tool to get config of all the servers

also please note MSQuery is fairly old - 
it wont allow selecting all the records you expect in the email table
Agree with Neeraja that that MySQL Workbench would fit better

All the best, 
Dmytro

You're speaking Greek to me. I have no clue about any of it. I'd quite literally never gone into admin mode on SugarCRM or touched SQL in any form until I started looking at this last week. I've already made the recommendation that our CEO hire someone to tackle the project.