Today I want to talk about a small module I wrote to integrate with CiviCRM from Drupal. It started from a need I had to batch process a bunch of bounced email addresses and set them "on hold". The reason was that I used Mandrill via SMTP to send out my mailings. Hang on, I’ll explain.

If you don’t want to read the boring history, you can scroll down to the module code I’d be very glad if you could comment on.

I decided to use Mandrill to send out my mailings because of their very nice offer (for free) and the SMTP connection possibility. However, some problems ensued when it came to processing bounced emails.

The normal flow of bounce processing in CiviCRM is the following: "for each email sent via CiviMail's mass mailing feature, a new unique "invisible" sender address is created using the variable envelope return path or VERP. When CiviCRM receives a bounce, it looks at the invisible sender address to decide which email bounced. Contacts will be marked as on hold when their email bounces."

However, between the mass email being sent and the CiviCRM receiving the bounce there is another email inbox you need to set up for the processing. My problem with Mandrill (as opposed to using the simple mail() function with which it worked just fine) was that the bounced email was not being sent to this inbox, and for the life of me I couldn’t figure out why. I think the Mandrill headers were preventing this to happen - to speculate a bit here.

Luckily, Mandrill provided me with a list of the bounced emails, so all was not lost. I just needed to re-import them back into CiviCRM and mark them "on hold". Sh*t is what I said when I saw you can’t do this. So I quickly did an SQL query that changed the email table and manually set the emails "on hold". But since this is obviously not a sustainable solution, I wrote a small Drupal module I was hoping you could give me your feedback on, especially because it deals with integration with CiviCRM and I don’t have a lot of experience with that.

So here is the code that does the processing. If there are better or more appropriate ways, please let me know. Patches are welcome of course. And I am particularly interested in the interaction with CiviCRM - connection, querying, updating etc.

function civicrm_on_hold_form_submit($form, &$form_state) {
  //Setting the required action : set or unset "On Hold"
  $action_machine = $form_state['input']['civicrm_on_hold_action'];
  if ($action_machine == 'set_1') {
    $action_text = t('set to "On Hold".');
    $action_number = 1;
  }
  else {
    $action_text = t('set to no longer be "On Hold".');
    $action_number = 0;
  }
  //Exploding the emails text field into an array of emails
  $emails = explode("\n", $form_state['input']['civicrm_on_hold_emails']);
  
  //Initializing the CiviCRM API
  civicrm_initialize();
  //Returning for each email address, the db information about the email
  $email_full = array();
  foreach ($emails as $email) {
    $params = array(
      'version' => 3,
      'email' => trim(check_plain($email)),
    );
    $result = civicrm_api('Email', 'get', $params);
    foreach($result['values'] as $key => $value) {
      $email_full[$key] = $value;
    }
  }
  
  //Setting the "On Hold" status for each email
  foreach($email_full as $id => $email) {
    $params = array(
      'version' => 3,
      'email' => $email['email'],
      'id' => $id,
      'contact_id' => $email['contact_id'],
      'on_hold' => $action_number,
    );
    $result = civicrm_api('Email', 'create', $params);
  }
  
}

One thing that troubles me is that using the CiviCRM API (which I think I should use), I had to do something redundant: returning information about the email addresses (contact_id and id) so that I can then make an update. I did a lot of research and this was the best way I found - you cannot do update queries having only the email address as the identifier? What do you think?

I have the [working] module set up in a repository on Drupal.org so if you want to give a go at it, you can clone it. It is though not ready for production. Let me know what you think. Cheers!