CiviCRM Community Forums (archive)

Background: Today I sent an email to the folks in one of my groups. The outgoing email parameters had been changed (I don't know how), so all deliveries failed and all the email addresses referenced in the mailing were put 'on hold'.

Recovery strategy: I chose to find all the email addresses for contacts in the affected group and mark them as not 'on hold'. This over-corrects the problem, in that some genuine 'on hold' addresses are changed, but this is not a big deal: the next mailing will discover the truly 'on hold' addresses.

Option 1: Upgrade to CiviCRM v3.2 - see Kurund's comment below.

Option 2: This process assumes you sent the email to a group.

1. Discover the group number by going to CiviCRM > Contacts > Manage Groups and finding the group in the list. The group number is stored in the 'ID' column. The group number is indicated by <ID> in the following SQL and you'll need to replace that with your group number.

2. Check the affected contacts match your expectations by running the following query in SQL...

select
 civicrm_email.email,
 civicrm_contact.display_name,
 civicrm_group.name
from
 civicrm_email,
 civicrm_contact,
 civicrm_group_contact,
 civicrm_group
where
 civicrm_email.on_hold = 1 and
 civicrm_group.id = <ID> and
 civicrm_email.contact_id = civicrm_contact.id and
 civicrm_group_contact.group_id = civicrm_group.id and
 civicrm_contact.id = civicrm_group_contact.contact_id

The email addresses, contact names, and group name should match your expectations. The number of rows returned ought to be reasonable. If not, something is wrong and some more investigation may be required.

3. Remove the 'on hold' status from these contacts by running the following query in SQL...

update
 civicrm_email,
 civicrm_contact,
 civicrm_group_contact,
 civicrm_group
set
 civicrm_email.on_hold = 0
where
 civicrm_email.on_hold = 1 and
 civicrm_group.id = <ID> and
 civicrm_email.contact_id = civicrm_contact.id and
 civicrm_group_contact.group_id = civicrm_group.id and
 civicrm_contact.id = civicrm_group_contact.contact_id


Fantastic!