Sales order email sent to multiple different customers

[beTeamsys]

Hello everyone,

Since one week now, every single "sale order" email that is sent is sent to the right customer + multiple other customers at the same time.
It vary from 1 to 5 or 6 other customers that shouldn't receive the email.

I already tried to clean the 1.9.2.3 Magento's database (core_email_queue and recipient tables) but this behavior still occurs. I really can't explain or understand why...

No module was recently added to the store and nothing changed in the global configuration of the sales emails.

Do anyone of you have any idea about that issue ?


Thank you.

[beTeamsys]

Any ideas are welcome 

Thanks

[Mike Hall]

TL;DR: use the "Bcc" option instead of the "Separate Email" option for order copy emails, because the latter is seriously broken in Magento 1.9.1. It will expose your copy email addresses to the customer.

There is something fundamentally broken with how Magento is handling this. When you have multiple (comma-separated) email addresses specified in "Send Order Email Copy To" with "Send Order Email Copy Method" set to "Separate Email", Magento creates multiple separate messages in the core_email_queue table - one for the customer and one for each Copy recipient.

(example: set to copy to test@example.com, foobar@example.com)

message_id    entity_id    entity_type    event_type    message_body_hash
4             19           order          new_order     b0faf3b948557fc38cf1ef564d0db16e
5             19           order          new_order     b0faf3b948557fc38cf1ef564d0db16e
6             19           order          new_order     b0faf3b948557fc38cf1ef564d0db16e

 That is fine - it has created separate messages for each recipient.

However the actual recipients are stored in another table - core_email_queue_recipients. This is where the problem is. Instead of assigning one recipient to each message, this is what happens:

recipient_id    message_id    recipient_email
13              4             foobar@example.com
14              5             foobar@example.com
15              5             test@example.com
16              6             foobar@example.com
17              6             test@example.com
18              6             customer@example.com

it assigns 1 recipient to the first message, 2 to the second, 3 to the third etc. The more recipients you have added to the Copy field, the more emails the last email on that list will receive.

What should have been added to core_email_queue_recipients is this:

recipient_id    message_id    recipient_email
13              4             foobar@example.com
14              5             test@example.com
15              6             customer@example.com

What is worse is that the emails are no longer "separate" because the email sent to the customer will have included in the "To" field a list of all the other email addresses (which should have been sent separately and without knowledge of the customer) - thus exposing to the customer your entire copy list.

[beTeamsys · Accepted solution]

Thank you for your answer ! 

I've already been investigating on that track, the config was actually already using the Bcc instead of "separate emails".

The issue here was that the email was sent to various other customers (using Bcc) and not emails that are configured in the Magento admin configuration. This means we had random customers who received the order email of some other customer. 

The issue seem to be fixed now, I just reversed a change I made few days before, setting the "Add store to URL" in Configuration > Web to "Yes".

[comechmetrology]

Hi.

I have encountered this issue too and we found the  answer on the magento stackexchange.

There appears to be a missing foreign key constraint on the core_email_queue_recipeints table, so when the cron job clears the core_email_queue table the recipients are left, and the truncation resets the primary key to 1, causing possible collisions of past customer email addresses with new orders.

To fix the problem in magento 1.9:

DELETE FROM core_email_queue_recipients WHERE message_id NOT IN (SELECT message_id FROM core_email_queue);
DELETE FROM core_email_queue_recipients WHERE recipient_id < (SELECT recipient_id FROM (SELECT recipient_id FROM core_email_queue_recipients ORDER BY message_id ASC, recipient_id DESC LIMIT 1) AS r);
ALTER TABLE core_email_queue_recipients ADD FOREIGN KEY(message_id) REFERENCES core_email_queue(message_id) ON DELETE CASCADE;

This will clear the orphan records from the recipient table, and apply the missing FK rule to the database.

The full detail of the answer can be found here: http://magento.stackexchange.com/questions/53961/new-order-email-being-sent-twice/87299#87299

Hope this helps!