How to add cc emails to new email

Stay on this forum and keep asking questions, there is a lot to learn from a great many people. Many others are much more qualified to answer than I.

My requirements have changed slightly or rather I misunderstood them.  I am to create a new module to save the cc emails related to each case.  Then in our workflows do a select from the table generated by the new module and append the cc information with that result set. Your answer is accurate to the original question so I am marking this as the answer and If I need more help i'll make another post on this forum.  Thank you for your help!

You might want to ponder that request for a bit, I don't see why you would need a new module to save the Cc's. The information is already there, the emails should be linked to the people Cc'd. Take a look at the emails_email_addr_rel table, which is related to emails and to email_addresses. looking at emails.parent_type = 'Cases' should give you emails that are related to cases and address_type = 'cc' will give you all those who are cc'd.

The query would look something like this:

select eabr.bean_module, eabr.bean_id
from cases c
join emails e on e.parent_id = c.id and e.parent_type = 'Cases'
join emails_email_addr_rel eear on eear.email_id = e.id and address_type = 'cc'
join email_addresses ea on ea.id = eear.email_address_id
join email_addr_bean_rel eabr on eabr.email_address_id = ea.id

where

eabr.bean_module could be Contacts, Users, Leads, Accounts - basically any module with an email address on it and eabr.bean_id the id.

You could restrict your search by module with a where clause and/or join the relevant table for the appropriate information.

For example for contacts first and last name:

select con.first_name, con.last_name
from cases c
join emails e on e.parent_id = c.id and e.parent_type = 'Cases'
join emails_email_addr_rel eear on eear.email_id = e.id and address_type = 'cc'
join email_addresses ea on ea.id = eear.email_address_id
join email_addr_bean_rel eabr on eabr.email_address_id = ea.id and eabr.bean_module = 'Contacts'
join contacts con on con.id = eabr.bean_id

HTH,
FrancescaS

That is very helpful and also raises more questions about how I am supposed to go about solving my problem. So if the information I want is already there and all I need to do is write the query as stated in your comment I guess my next question would be how would I ensure that every email sent in my companies workflow about a case is appended with the correct CC addresses?

My other dev here and I looked at the api to do this. Specifically the InboundEmail.php file in ./modules/InboundEmails/InboundEmails.php  .  Could I extend this in the custom directory for the specific functions that send emails? 

Thank you again for all your help thus far.

I have not found a way to extend InboundEmails in a truly upgrade safe manner.

I am not sure I understand your requirement fully. Are you sending emails from an external source with Cc's that need to be recorded in your sugar instance as Cases?

In general, if you send an email and copy the Cases queue (in the To or Cc) you should have all the information in your database, including any Cc's included in the email you sent.

PS. See the third point in my suggestions for Email handling here: Suggestions for improvements to Inbound Email

So problem is when a customer emails us it will create a case.  The workflow will not keep track of who is CC'ed in emails. So when the helpdesk agent responds they manually put in the same people that were CC'ed from the customer. This is a problem because the Customer will CC people so that they are also aware of the status of issues.  The helpdesk agent could forget/not notice/misspell an email then that person is left out of the loop.

So In order to solve this issue I need to know where the emails are sent out from and how to extend that with appropriate information so that it CC's people automatically and there will be no user error when sending the email.  Thanks to your sql statement I have the info I need which would be all the CC'ed people in a case. Now I just need to know how to add these people to every email sent out by sugar in regards to a specific case.

Does that make sense?Francesca Shiekh

That is the problem I solved by adding the "Reply All" option to the rowactions in the email subpanel in Cases (the crazy code I provided above). It takes all the Cc's from the original email and adds them to the email being composed by the helpdesk agent who is responding to the customer (just like a Reply All on a regular email client). They then also have the option of removing/adding more recipients/cc's as needed.

Well I definitely need to review that code for the next couple of hours.

So your solution will create a row action button in the emails subpanel for cases, but this solution will only update that view that appears when the plus button is created.  I need it to work with the notes module in a case.  So when the helpdesk agent creates a note it will send an alert(email) depending on what the note is.  This email currently doesn't have any of the CC'ed people on it from previous emails.

Sorry I didn't say this before.  I'm asking more questions as I go.

So this would explain the need for a new module so that I can go through the admin panel and tell the workflow to send an email to all the people in the cc_email module.

Do you think this will work or did I make more questions?Francesca Shiekh

Gosh, looks like you have a lot of customizations in your Cases module, the Public Note is not an out-of-the-box.

The best I can do is tell you how I would approach it, but that doesn't make it the right way or the only way... so take it with a grain of salt.

My basic attitude is: don't duplicate data unless ABSOLUTELY necessary.

In your case I don't think it's necessary.

You must have an email compose related to that Public Note somewhere.

You should be able to pull all email addresses from the case related emails and add them to the Cc. If you don't add the address_type on the query below you'll get ALL addresses.

select distinct(ea.email_address)
from cases c
join emails e on e.parent_id = c.id and e.parent_type = 'Cases'
join emails_email_addr_rel eear on eear.email_id = e.id and address_type = 'cc'
join email_addresses ea on ea.id = eear.email_address_id
join email_addr_bean_rel eabr on eabr.email_address_id = ea.id
where cases.id = $case_id

Without knowing how your Public Note emails go out it's hard for me to help you with the code.

If you are using an email drawer you should have an app.drawer.open for module: Email somewhere, and you should be able to pass some "prepopulate" data, as I do in the ReplyAll from my customCompose, the biggest difference is that I'm replying to a specific email and therefore get all recipients from that email, you instead have to find all the addresses included in any of your Case's emails and therefore need the query above to get them (and depending on how many emails you have on that case that could be quite a few).

HTH

Good Luck, you sure jumped in on the deep end!