CiviCRM Community Forums (archive)

can you resend the mailing, but EXCLUDE all the recipients who received the first mailing

lobo

what hapened to the 800 recipients. a few questions on that:

1. how did u figure out this number?

2. did civimail attempt to deliver email to them?

3. what happened on the delivery end? did it soft bounce or hard bounce? or neither?

lobo

Any ideas on how to try to recover from the situation? My main goal is to avoid manually selecting those 800 records...

I am thinking that maybe these records with Bounce type unknown are not included in resending (maybe a NULL in the Bounce Reason?).
I could investigate a bit into this. Could you help me pinpoint the location of the SQL that builds the exclude part? Then I can check the data in the problematic mailing (and if this indeed is the cause I could then 'fix' the delivery records so that resending picks it up again)

Goran,
I just created a customization for our 2.2.6 install last weekend that adds a list of the bounced contact IDs to the bottom of the Bounces report page. I then have a custom search where I copy&paste this comma-separated bounced-ids list and create a new mailing out of it.
If you were interested to try it, please let me know and I'll send you the code, it's quite easy. I'll keep watching this thread but you can also email me to sreality2009 account on the gmail dot com server
Boris

edit Nov 16, 2009: a better solution is lower in the thread, you can skip this one
---------
It's not very polished, but it's working. So here we go:

in Bounce.php in \CRM\Mailing\Event\BAO\
add this line
               'contact_id'      => $dao->contact_id,
as the last line of the $results[] array at the very end (around line 236)

in Event.tpl in \templates\CRM\Mailing\Page\
add these lines e.g. just above the <div class="action-link">:
{if $rows[0].contact_id }
<div style="font-size: 10px;">
Contact IDs:
{foreach from=$rows item=value}
{$value.contact_id},
{/foreach}
</div>
{/if}

Now we should have comma-separated list of contact IDs below the table in the bounces report page.

Next, we'll create a custom search where when can use these:

save this code as file aaaIDsearch.php and put it into the \CRM\Contact\Form\Search\Custom\ directory
<?php

//Custom Search Path: CRM_Contact_Form_Search_Custom_aaaIDsearch
//Search Title: aaaIDsearch
//Weight: 1

/*
SELECT
civicrm_email.contact_id as contact_id,
civicrm_email.email    as email,
civicrm_email.id as eid,
contact_a.sort_name as sort_name
FROM       civicrm_email civicrm_email, civicrm_contact contact_a
WHERE civicrm_email.contact_id IN (3,4) AND civicrm_email.is_bulkmail = 1 AND civicrm_email.contact_id = contact_a.id
*/


/*
 +--------------------------------------------------------------------+
 | CiviCRM version 3.0                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2009                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007.                                       |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License along with this program; if not, contact CiviCRM LLC       |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2009
 * $Id$
 *
 */

require_once 'CRM/Contact/Form/Search/Custom/Base.php';

class CRM_Contact_Form_Search_Custom_aaaIDsearch
   extends    CRM_Contact_Form_Search_Custom_Base
   implements CRM_Contact_Form_Search_Interface {

    function __construct( &$formValues ) {
        parent::__construct( $formValues );

        $this->_columns = array( ts('Contact Id')   => 'contact_id'  ,
                                 ts('Name')         => 'sort_name' ,
                                 ts('Email')         => 'email' ,
                                 ts('Email Id')         => 'eid' 
                                  );
    }

    function buildForm( &$form ) {

        $form->add( 'text',
                    'ids',
                    ts( 'Contact IDs (comma-separated):' ),
                    true );

        /**
         * You can define a custom title for the search form
         */
         $this->setTitle('aaaIDsearch');
         
         /**
         * if you are using the standard template, this array tells the template what elements
         * are part of the search criteria
         */
        $form->assign( 'elements', array( 'ids' ) );
    }

    function summary( ) {
    }

    function all( $offset = 0, $rowcount = 0, $sort = null,
                  $includeContactIDs = false ) {

//SELECT
        $selectClause = "

civicrm_email.contact_id as contact_id,
civicrm_email.email    as email,
civicrm_email.id as eid,
contact_a.sort_name as sort_name

";

        return $this->sql( $selectClause,
                           $offset, $rowcount, $sort,
                           $includeContactIDs, null );

    }
    
    function from( ) {
        return "
FROM      civicrm_email civicrm_email, civicrm_contact contact_a
";
    }

    function where( $includeContactIDs = false ) {
        $params = array( );
        $name   = CRM_Utils_Array::value( 'ids',
                                          $this->_formValues );

//WHERE
        $where  = "
civicrm_email.contact_id IN (".$name.") AND civicrm_email.is_bulkmail = 1 AND civicrm_email.contact_id = contact_a.id 
     
     ";
        $count  = 1;
        $clause = array( );

        //if ( ! empty( $clause ) ) {            $where .= ' OR ' . implode( ' OR ', $clause );        }

        return $this->whereClause( $where, $params );
    }

    function templateFile( ) {
        return 'CRM/Contact/Form/Search/Custom/Sample.tpl';
    }

   
    function setTitle( $title ) {
        if ( $title ) {
            CRM_Utils_System::setTitle( $title );
        } else {
            CRM_Utils_System::setTitle(ts('Search'));
        }
    }
}


go to .../civicrm/admin/options/custom_search?group=custom_search&reset=1 (or ?q=civicrm/admin/options/custom_search&group=custom_search&reset=1 if you don't have clean URLs and the previous URL shows Option Groups instead of Custom Search Options), there click to register new search, insert the values from the top of the file there, i.e:
//Custom Search Path: CRM_Contact_Form_Search_Custom_aaaIDsearch
//Search Title: aaaIDsearch
//Weight: 1
and Save it.

Now go to custom search and try it. If you'll use the id list from the Bounces report, don't insert the last "," to the custom search box or you'll get a SQL error.

That should be it, you can also refer to http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components if you run into issues with the custom search.

Let me know if the above worked for you, I might have made a mistake somewhere.
It's a dirty workaround, but it's working for me

here we go, updated version per Lobo's suggestion above:

1) Save this code as a new file named aaaMailBounceResend.php and put it into the \CRM\Contact\Form\Search\Custom\ directory
<?php

//Custom Search Path: CRM_Contact_Form_Search_Custom_aaaMailBounceResend
//Search Title: aaaMailBounceResend
//Weight: 1

/*
            SELECT
contact_a.id           as contact_id,
contact_a.sort_name    as sort_name,
civicrm_email.email    as email,
civicrm_email.id as eid
            FROM        civicrm_contact as contact_a
            INNER JOIN  civicrm_mailing_event_queue
                    ON  civicrm_mailing_event_queue.contact_id = contact_a.id
            INNER JOIN  civicrm_email
                    ON  civicrm_mailing_event_queue.email_id = civicrm_email.id
            INNER JOIN  civicrm_mailing_event_bounce
                    ON  civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
            LEFT JOIN   civicrm_mailing_bounce_type
                    ON  civicrm_mailing_event_bounce.bounce_type_id = civicrm_mailing_bounce_type.id
            INNER JOIN  civicrm_mailing_job
                    ON  civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
                    AND civicrm_mailing_job.is_test = 0
            INNER JOIN  civicrm_mailing
                    ON  civicrm_mailing_job.mailing_id = civicrm_mailing.id
            WHERE       civicrm_mailing.id = 24
*/



/*
 +--------------------------------------------------------------------+
 | CiviCRM version 3.0                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2009                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007.                                       |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License along with this program; if not, contact CiviCRM LLC       |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2009
 * $Id$
 *
 */

require_once 'CRM/Contact/Form/Search/Custom/Base.php';

class CRM_Contact_Form_Search_Custom_aaaMailBounceResend
   extends    CRM_Contact_Form_Search_Custom_Base
   implements CRM_Contact_Form_Search_Interface {

    function __construct( &$formValues ) {
        parent::__construct( $formValues );

        $this->_columns = array( ts('Contact Id')   => 'contact_id'  ,
                                 ts('Name')         => 'sort_name' ,
                                 ts('Email')         => 'email' ,
                                 ts('Email Id')         => 'eid' 
                                  );
    }

    function buildForm( &$form ) {

        $form->add( 'text',
                    'ids',
                    ts( 'Mailing ID' ),
                    true );

        /**
         * You can define a custom title for the search form
         */
         $this->setTitle('aaaMailBounceResend');
         
         /**
         * if you are using the standard template, this array tells the template what elements
         * are part of the search criteria
         */
        $form->assign( 'elements', array( 'ids' ) );
    }

    function summary( ) {
    }

    function all( $offset = 0, $rowcount = 0, $sort = null,
                  $includeContactIDs = false ) {

//SELECT
        $selectClause = "

contact_a.id           as contact_id,
contact_a.sort_name    as sort_name,
civicrm_email.email    as email,
civicrm_email.id as eid

";

        return $this->sql( $selectClause,
                           $offset, $rowcount, $sort,
                           $includeContactIDs, null );

    }
    
    function from( ) {
        return "
            FROM        civicrm_contact as contact_a
            INNER JOIN  civicrm_mailing_event_queue
                    ON  civicrm_mailing_event_queue.contact_id = contact_a.id
            INNER JOIN  civicrm_email
                    ON  civicrm_mailing_event_queue.email_id = civicrm_email.id
            INNER JOIN  civicrm_mailing_event_bounce
                    ON  civicrm_mailing_event_bounce.event_queue_id = civicrm_mailing_event_queue.id
            LEFT JOIN   civicrm_mailing_bounce_type
                    ON  civicrm_mailing_event_bounce.bounce_type_id = civicrm_mailing_bounce_type.id
            INNER JOIN  civicrm_mailing_job
                    ON  civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
                    AND civicrm_mailing_job.is_test = 0
            INNER JOIN  civicrm_mailing
                    ON  civicrm_mailing_job.mailing_id = civicrm_mailing.id
";
    }

    function where( $includeContactIDs = false ) {
        $params = array( );
        $name   = CRM_Utils_Array::value( 'ids',
                                          $this->_formValues );

//WHERE
        $where  = "
            civicrm_mailing.id = ".$name."
     
     ";
        $count  = 1;
        $clause = array( );

        //if ( ! empty( $clause ) ) {            $where .= ' OR ' . implode( ' OR ', $clause );        }

        return $this->whereClause( $where, $params );
    }

    function templateFile( ) {
        return 'CRM/Contact/Form/Search/Custom/Sample.tpl';
    }

   
    function setTitle( $title ) {
        if ( $title ) {
            CRM_Utils_System::setTitle( $title );
        } else {
            CRM_Utils_System::setTitle(ts('Search'));
        }
    }
}

2) In your web browser go to [...]/civicrm/admin/options/custom_search?group=custom_search&reset=1 (or [...]?q=civicrm/admin/options/custom_search&group=custom_search&reset=1 if you don't have clean URLs and the previous URL shows Option Groups instead of Custom Search Options)

3) Click to Register new search, and copy&paste the values from the top of the file you just created, i.e:
//Custom Search Path: CRM_Contact_Form_Search_Custom_aaaMailBounceResend
//Search Title: aaaMailBounceResend
//Weight: 1
and save the new custom search.

4) Go to custom searches and to aaaMailBounceResend (or click "Run" directly in the table on the page where you just registered the new custom search)

5) Into the search field, type in the mailing ID of the mailing whose bounces you want to resend. (You can find the mailing ID as the parameter "mid=" in the URLs of the reports in CiviMail)

6) Click search, on the results page first click "Selected records only", then click "All XXX records" (this workaround is needed for the actions menu to activate itself, until the stays-grayed-out bug is fixed).

7) Select "Schedule/Send a Mass Mailing"

!!! make sure that for 'Base group' you select an empty group (I have a "mailing testing" group created for this and for sending testing emails), otherwise the new mailing WILL BE SENT TO the bounces PLUS ALL THE MEMBERS OF THE BASE GROUP, which we obviously don't want !!!

9) !!! double-check that the number of Recipients on the wizard pages is the same as the number of original bounces (+ e.g. 1 testing email address if you prefer to have it in the base group).

From then on continue like with every other mail : )
(I suggest to keep the mailing name the same like the original and add a suffix to indicate the second attempt)

very cool, thanks for posting this, forgot to set batch limits and this search saved the day