Extracting full recipient list from a mass mailer (or specific service/product category)

[svdmon]

Greetings

 

We have used to mass mailer tool to send a notification to all our clients with a specific service.

I would like to know if it's possible to get a list of all email addresses it was sent to.

Alternatively and to the same effect would be to get a list of all clients email addresses that subscribe to specific services.

 

I have checked the mail log and only get the individual mails. As it was sent to hundreds of clients this is not a viable means of extracting their info.

 

Any assistance would be greatly appreciated.

 

Cheers

Simon

[brian!]

Hi Simon,

 

We have used to mass mailer tool to send a notification to all our clients with a specific service.

I would like to know if it's possible to get a list of all email addresses it was sent to.

I don't think that there's a built-in way to extract that information - but you basically only need a SQL query to extract that info from the database.

 

I have checked the mail log and only get the individual mails. As it was sent to hundreds of clients this is not a viable means of extracting their info.

it would be if you were to query the database, or write a report to do it, that allowed you to filter/export the required information.

 

create a new file in modules/reports/ and call it email_log.php and paste the code below into it...

 

<?php

if (!defined("WHMCS"))
   die("This file cannot be accessed directly");

$reportdata["title"] = "Email Log";

$filterfields = array("id"=>"ID","userid"=>"User ID","clientname"=>"Client","subject"=>"Subject","message"=>"Message","date"=>"Date","cc"=>"CC","bcc"=>"BCC","email"=>"Email");

$reportdata["description"] = $reportdata["headertext"] = '';

$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
if (!is_array($incfields)) $incfields = array();
if (!is_array($filterfield)) $filterfield = array();
if (!is_array($filtertype)) $filtertype = array();
if (!is_array($filterq)) $filterq = array();

if (!$print) {

   $reportdata["description"] = "This report can be used to generate a custom export of data from the email logs by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";

   $reportdata["headertext"] = '<form method="post" action="reports.php?report='.$report.'">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
   $i=0;
   foreach ($filterfields AS $k=>$v) {
       $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="'.$k.'" id="fd'.$k.'"';
       if (in_array($k,$incfields)) $reportdata["headertext"] .= ' checked';
       $reportdata["headertext"] .= ' /> <label for="fd'.$k.'">'.$v.'</label></td>'; $i++;
       if (($i%5)==0) $reportdata["headertext"] .= '</tr><tr>';
   }
   $reportdata["headertext"] .= '</tr></table></td></tr>';

   for ( $i = 1; $i <= 5; $i ++ ) {
       $reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter '.$i.'</td><td class="fieldarea"><select name="filterfield['.$i.']"><option value="">None</option>';
       foreach ($filterfields AS $k=>$v) {
           $reportdata["headertext"] .= '<option value="'.$k.'"';
           if (isset($filterfield[$i]) && $filterfield[$i]==$k) $reportdata["headertext"] .= ' selected';
           $reportdata["headertext"] .= '>'.$v.'</option>';
       }
       $reportdata["headertext"] .= '</select> <select name="filtertype['.$i.']"><option>Exact Match</option><option value="like"';
       if (isset($filtertype[$i]) && $filtertype[$i]=="like") $reportdata["headertext"] .= ' selected';
       $reportdata["headertext"] .= '>Containing</option></select> <input type="text" name="filterq['.$i.']" size="30" value="'.(isset($filterq[$i])?$filterq[$i]:'').'" /></td></tr>';
   }
   $reportdata["headertext"] .= '</table>
<p align="center"><input type="submit" value="Filter" /></p>
</form>';

}

if (count($incfields)) {

   $filters = array();
   foreach ($filterfield AS $i=>$val) {
       if ($val && array_key_exists($val,$filterfields)) $filters[$val] = ($filtertype[$i]=="like") ? array("sqltype"=>"LIKE","value"=>$filterq[$i]) : $filterq[$i];
   }

   $fieldlist = array();
   foreach ($incfields AS $fieldname) {
       if (array_key_exists($fieldname,$filterfields)) {
           $reportdata["tableheadings"][] = $filterfields[$fieldname];
           if ($fieldname=="email") $fieldname = "(SELECT email FROM tblclients WHERE id=tblemails.userid)";
           if ($fieldname=="clientname") $fieldname = "(SELECT CONCAT(firstname,' ',lastname) FROM tblclients WHERE id=tblemails.userid)";
           $fieldlist[] = $fieldname;
       }
   }

   $result = select_query("tblemails",implode(',',$fieldlist),$filters);
   while ($data = mysql_fetch_assoc($result)) {
       $reportdata["tablevalues"][] = $data;
   }

}

using that, you should be able to get a list of email addresses for a mass mail sent on a specific day and using the given subject - then you can export it to a .csv file.

 

 

Alternatively and to the same effect would be to get a list of all clients email addresses that subscribe to specific services.

again, that's just another SQL query to a different database - you might want to look at tweaking the services.php report to do this.