[SOLVED] CiviMail fails to select all qualified contacts in a static group

[Victor.Odhner]

WHAT I LEARNED:
  CiviMail does not select contacts where do_not_email IS NULL.
  The NULL values may be from an earlier version of Civi.

I've added useful SQL queries here, to diagnose the failure to select all qualified recipients in a static group.

Note that techniques here do not cover mailings using more complex selections, or exclusions; and don't test for empty email addresses, or the lack of either a primary email or one marked for bulk mailings.

It appears that the important logic is here:

    administrator/components/com_civicrm/civicrm/CRM/Mailing/BAO/Mailing.php

CiviMail failed to send to the whole static target group.  Problem persisted in Civi 4.4.6 on Joomla 2.5.11, problem first observed in 4.3.  There were 55 contacts in the group, but at Wizard Step 2 it showed a count of only ~20 selected to send.  Send to all of those selected was successful.  We had four contacts in the group with no email address, and the Do Not Email option was set for those.  Do Not Email was not checked for any other contacts.

None were on Hold, none were unsubscribed, all contacts had is_deceased = 0 (NULL values there were a known problem), most had primary email and others had bulk-mail checked.  After some consultation, used a SQL query against contacts in the target group, and saw a bunch where do_not_email IS NULL.

select C.id, C.display_name, C.do_not_email, C.is_opt_out,
         C.is_deceased, C.is_deleted, C.created_date
  from civicrm_contact C
  where C.id in
       ( select contact_id from civicrm_group_contact
          where group_id = 5 and status = 'Added' );

The CiviCRM GUI did not make it obvious that the value was not normal.  All contacts were entered manually in Civi's GUI or a profile, and the NULL values in do_not_email don't seem to correlate with entry method or created_date except that the most recent NULL was created on 2014-03-04; it's now 2014-07-08 and there have been a few dozen new contacts added in that time.  Maybe a bug was fixed since then?

The following query set all of the NULLs to zero:

update civicrm_contact C set C.do_not_email = 0  where C.do_not_email IS NULL;

RESULT:  When I re-used the mailing that had failed, the number of members of the group that weren't explicitly disqualified equaled the number selected in Step 2 of the wizard.

[penguintrax]

Thanks for this. I updated the privacy preferences on 80+ records to remove a do not email setting, then none of them appeared in my mailing. Frustrating as hell.

[PJKs_shirt]

Same thing happened to me for contacts where is_deceased IS NULL.

Problem resolved when set is_deceased to '0' for "undead"    contacts.