|
edimusrex has asked for the wisdom of the Perl Monks concerning the following question:
I am a little stumped here on what to do. Currently we send out a weekly news letter to all our users but we like the emails to be custom to each user meaning the first line in the body says -- Hi Bob! -- or whatever. I use HTML Template to accomplish this but I can't find a way to do that and send a bulk message to all users using BCC so I am sending 1 message at a time which as you can imagine takes forever. My question is if someone knows a better way to accomplish this?
Here is the code thus far, you will see it connects to a database to grab users which are subscribed and builds the emails from that
#!/usr/bin/perl
use strict;
use warnings;
use MIME::Lite;
use HTML::Template;
use DBI;
use Cwd qw( abs_path );
use File::Basename qw( dirname );
use Config::Properties;
use Getopt::Long;
use Term::ANSIColor;
if (scalar $#ARGV == -1) { &usage(); }
my %props;
&properties();
my ($mail,$list,$remove,$add);
GetOptions(
'mail' => \$mail,
'list' => \$list,
'remove' => \$remove,
'add' => \$add,
) or die &usage();
my %connect = (
'database'=>$props{MySQL_Database},
'host'=>$props{MySQL_Host},
'port'=>$props{MySQL_Port},
'user'=>$props{MySQL_User},
'password'=>$props{MySQL_Password},
'file'=>$props{HTML_File},
);
my $dsn = "DBI:mysql:database=$connect{database};host=$connect{host};p
+ort=$connect{port}";
my $dbh = DBI->connect( $dsn, $connect{user}, $connect{password} ) or
+die "Failed to connect to the database: " . DBI->errstr;
my $sql = qq|SELECT `emailAddress`, `firstName` FROM $connect{database
+}.`users` WHERE `status` = (SELECT `Id` FROM $connect{database}.`assc
+_status` WHERE `Status` = 'FULL') AND `blacklisted` = (SELECT `Id` FR
+OM $connect{database}.`assc_blacklist` WHERE `Blacklisted` = 'No') AN
+D `unsubscribed` = (SELECT `Id` FROM $connect{database}.`assc_unsubsc
+ribed` WHERE `Status` = 'No')|;
my $sql_remove = qq|UPDATE $connect{database}.`users` SET `unsubscribe
+d` = (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `
+Status` = 'Yes') WHERE `emailAddress` = ?|;
my $sql_add = qq|UPDATE $connect{database}.`users` SET `unsubscribed`
+= (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `Sta
+tus` = 'No') WHERE `emailAddress` = ?|;
if($mail) {
print "This option will email all subscribed users. Are you sure
+you would like to continue? : (yes|no) ";
chomp(my $res = <>);
if ($res !~ /^yes$/) {
print "Closing Script\n";
exit;
}
my $sth = $dbh->prepare($sql);
$sth->execute or die "Failed to execute query:$!";
my $file = HTML::Template->new(filename => $connect{file});
while (my $result = $sth->fetchrow_hashref) {
$file->param(USER_NAME => $result->{firstName});
&sendMail($result->{emailAddress},$file->output);
}
$dbh->disconnect;
exit;
}
if($list) {
my $sth = $dbh->prepare($sql);
$sth->execute or die "Failed to execute query:$!";
while (my $result = $sth->fetchrow_hashref) {
print "$result->{emailAddress}\n";
}
$dbh->disconnect;
exit;
}
if($remove) {
my $ans = 1;
while($ans) {
print "Enter email address you would like to remove from list
+: ";
chomp(my $em = <>);
my $sth = $dbh->prepare($sql_remove);
$sth->execute($em) or die "Failed to execute query:$!";
print "$em has been unsubscribed!\n\nWould you like to remove
+another user? : (yes|no) ";
chomp(my $res = <>);
if (lc $res !~ /^yes$/ ) { $ans = 0; }
}
$dbh->disconnect;
exit;
}
if($add) {
my $ans = 1;
while($ans) {
print "Enter email address you would like to add to list : ";
chomp(my $em = <>);
my $sth = $dbh->prepare($sql_add);
$sth->execute($em) or die "Failed to execute query:$!";
print "$em has been subscribed!\n\nWould you like to add anoth
+er user? : (yes|no) ";
chomp(my $res = <>);
if (lc $res !~ /^yes$/ ) { $ans = 0; }
}
$dbh->disconnect;
exit;
}
sub sendMail{
my $subject = "<subject title goes here>";
my $to = $_[0];
my $body = $_[1];
my $msg = MIME::Lite->new(
From => '<someemail@somewhere.com',
To => $to,
Subject => $subject,
Type => 'text/html',
Data => $body,
) or die "Error creating multipart container: $!\n";
$msg->send or die "Failed To Send!: $!\n";
print "Message sent!\n";
}
sub properties {
open my $fh, '<', dirname(abs_path($0))."/mailer.props" || warn "F
+ailed to open :$!";
my $properties = Config::Properties->new();
$properties->load($fh);
%props = $properties->properties;
return;
}
sub usage {
print color("yellow"), "\n$0 Usage :\n", color("reset");
my $message = <<EOF;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++
+
+
+ +
+ --mail -:- Send weekly email to all users subscribed
+
+ +
+
+
+ +
+ --list -:- List subscribed users by email
+
+ +
+
+
+ +
+ --remove -:- Unsubscribe user from email, requires you to
+ enter the email address
+ +
+
+
+ +
+ --add -:- Add user to subscribed list
+
+ +
+
+
+ +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++
EOF
print $message;
exit;
}
Help is always greatly appreciated. I hope this all makes sense |
Re: Sending Custom Mass Mail - Fastest Way Possible
by stevieb (Canon) on May 29, 2015 at 15:44 UTC
|
You can use the built-in fork() to do what you want. It'll allow you to run multiple processes simultaneously, while injecting your template info into each distinct email message.
Set up the %email hash with the template information for each user, then change the print/sleep operations in start(), write some logic to populate the template and send the email.
#!/usr/bin/perl
use warnings;
use strict;
my %email = (
ken =>
{
name => 'Ken May',
addr => 'kenm@hello.com',
},
suzie =>
{
name => 'Suzie Sheer',
addr => 'ss@hello.com',
},
steve =>
{
name => 'Stevie B',
addr => 'sb@hello.com',
}
);
my @children;
for my $key (keys( %email )){
my $pid = fork();
if( $pid ){
print "PID $pid forked: $key\n";
push @children, $pid;
}
else {
my $proc = start($key);
exit($proc);
}
}
for my $child (@children){
my $pid = waitpid($child, 0);
print "PID $pid exited...\n";
}
sub start {
my $key = shift;
print "$key Starting...\n";
print "$email{$key}->{'addr'}, $email{$key}->{'name'}\n";
sleep(5);
print "$key Ending...\n";
}
-stevieb | [reply]
[d/l] |
|
|
Thank you for that suggestion! Man, that really sped up my script. I had to make some server side adjustments to exim but with forking the script now completes in less than a minute where it was taking well over 15 before. Here is what the adjusted code looks like
#!/usr/bin/perl
use strict;
use warnings;
use MIME::Lite;
use HTML::Template;
use DBI;
use Cwd qw( abs_path );
use File::Basename qw( dirname );
use Config::Properties;
use Getopt::Long;
use Term::ANSIColor;
use Parallel::ForkManager;
my $pm = new Parallel::ForkManager(40);
if (scalar $#ARGV == -1) { &usage(); }
my (%props,%emails);
&properties();
my ($mail,$list,$remove,$add);
GetOptions(
'mail' => \$mail,
'list' => \$list,
'remove' => \$remove,
'add' => \$add,
) or die &usage();
my %connect = (
'database'=>$props{MySQL_Database},
'host'=>$props{MySQL_Host},
'port'=>$props{MySQL_Port},
'user'=>$props{MySQL_User},
'password'=>$props{MySQL_Password},
'file'=>$props{HTML_File},
);
my $dsn = "DBI:mysql:database=$connect{database};host=$connect{host};p
+ort=$connect{port}";
my $dbh = DBI->connect( $dsn, $connect{user}, $connect{password} ) or
+die "Failed to connect to the database: " . DBI->errstr;
my $sql = qq|SELECT `emailAddress`, `firstName` FROM $connect{database
+}.`users` WHERE `status` = (SELECT `Id` FROM $connect{database}.`assc
+_status` WHERE `Status` = 'PROVISIONAL') AND `blacklisted` = (SELECT
+`Id` FROM $connect{database}.`assc_blacklist` WHERE `Blacklisted` = '
+No') AND `unsubscribed` = (SELECT `Id` FROM $connect{database}.`assc_
+unsubscribed` WHERE `Status` = 'No')|;
my $sql_remove = qq|UPDATE $connect{database}.`users` SET `unsubscribe
+d` = (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `
+Status` = 'Yes') WHERE `emailAddress` = ?|;
my $sql_add = qq|UPDATE $connect{database}.`users` SET `unsubscribed`
+= (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `Sta
+tus` = 'No') WHERE `emailAddress` = ?|;
if($mail) {
print "This option will email all subscribed users. Are you sure
+you would like to continue? : (yes|no) ";
chomp(my $res = <>);
if ($res !~ /^yes$/) {
print "Closing Script\n";
exit;
}
my $sth = $dbh->prepare($sql);
$sth->execute or die "Failed to execute query:$!";
my $file = HTML::Template->new(filename => $connect{file});
while (my $result = $sth->fetchrow_hashref) {
my $fname = $result->{firstName};
my $email_address = $result->{emailAddress};
$emails{$email_address} = $fname;
}
$dbh->disconnect;
for my $x (keys %emails) {
my $pid = $pm->start and next;
$file->param(USER_NAME => $emails{$x});
my $proc = sendMail($x,$file->output);
exit($proc);
$pm->finish;
}
$pm->wait_all_children;
exit;
}
if($list) {
my $sth = $dbh->prepare($sql);
$sth->execute or die "Failed to execute query:$!";
while (my $result = $sth->fetchrow_hashref) {
print "$result->{emailAddress}\n";
}
$dbh->disconnect;
exit;
}
if($remove) {
my $ans = 1;
while($ans) {
print "Enter email address you would like to remove from list
+: ";
chomp(my $em = <>);
my $sth = $dbh->prepare($sql_remove);
$sth->execute($em) or die "Failed to execute query:$!";
print "$em has been unsubscribed!\n\nWould you like to remove
+another user? : (yes|no) ";
chomp(my $res = <>);
if (lc $res !~ /^yes$/ ) { $ans = 0; }
}
$dbh->disconnect;
exit;
}
if($add) {
my $ans = 1;
while($ans) {
print "Enter email address you would like to add to list : ";
chomp(my $em = <>);
my $sth = $dbh->prepare($sql_add);
$sth->execute($em) or die "Failed to execute query:$!";
print "$em has been subscribed!\n\nWould you like to add anoth
+er user? : (yes|no) ";
chomp(my $res = <>);
if (lc $res !~ /^yes$/ ) { $ans = 0; }
}
$dbh->disconnect;
exit;
}
sub sendMail{
my $subject = "Good ole subject line";
my $to = shift;
my $body = shift;
my $msg = MIME::Lite->new(
From => 'email@email.com',
To => $to,
Subject => $subject,
Type => 'text/html',
Data => $body,
) or die "Error creating multipart container: $!\n";
$msg->send or die "Failed To Send!: $!\n";
print "Message sent to - $to\n";
}
sub properties {
open my $fh, '<', dirname(abs_path($0))."/mailer.props" || warn "F
+ailed to open :$!";
my $properties = Config::Properties->new();
$properties->load($fh);
%props = $properties->properties;
return;
}
sub usage {
print color("yellow"), "\n$0 Usage :\n", color("reset");
my $message = <<EOF;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++
+
+
+ +
+ --mail -:- Send weekly email to all users subscribed
+
+ +
+
+
+ +
+ --list -:- List subscribed users by email
+
+ +
+
+
+ +
+ --remove -:- Unsubscribe user from email, requires you to
+ enter the email address
+ +
+
+
+ +
+ --add -:- Add user to subscribed list
+
+ +
+
+
+ +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++
EOF
print $message;
exit;
}
| [reply]
[d/l] |
|
|
| [reply] |
|
|
|
|
Yea, that seems like a good option. So maybe ingest my email list from the database into a hash first on fork from there.
| [reply] |
Re: Sending Custom Mass Mail - Fastest Way Possible
by hippo (Bishop) on May 29, 2015 at 15:35 UTC
|
Currently we send out a weekly news letter to all our users ... I am sending 1 message at a time which as you can imagine takes forever.
Since you quote no figure for "all our users" and no duration for "takes forever" there's little to go on. If you have 500 users and it takes half a day, then you probably have a fixable bottleneck. Conversely, if you have 5000 users and it takes half an hour then you are doing pretty well and perhaps your best next step is ... outsourcing.
| [reply] |
|
|
ok, to give you some numbers it takes about 15 minutes to send to 53 users, as that number grows I expect the run time to increase. Seems a little long in my opinion
| [reply] |
|
|
| [reply] |
|
|
Re: Sending Custom Mass Mail - Fastest Way Possible
by chacham (Prior) on May 29, 2015 at 16:47 UTC
|
Side comment. The queries use dynamic SQL, which can be bad for security and performance. $connect{database} is particularly bad, because any query could be run as long as it ends in a semi-colon. No breakout required. Though, there may be no way to address that issue directly and still be convenient. Are you sure you want the database to be a variable?
| [reply] |
|
|
Not too overly concerned with it as the script is run locally and the database user only has access from the localhost. Any recommendations on improving my code is of course welcomed. I have learned all this on my own and on the fly so I really do appreciate any help offered.
| [reply] |
|
|
Just be wary because although it is "not required here", scripts often go off in unexpected ways, and fixing it then may be impractical. Further, fixing the small scripts is excellent practice for the larger ones to come. :)
| [reply] |
Re: Sending Custom Mass Mail - Fastest Way Possible
by derby (Abbot) on May 29, 2015 at 15:59 UTC
|
Hmmm ... by default, I believe MIME::Lite uses sendmail (or postfix?) to send messages. If you don't have sendmail on the box, it may default to SMTP and that's just going to be a disaster sooner than later. I would check to see if you've properly configured sendmail (or postfix?) to properly queue and delay send the emails.
| [reply] |
|
|
Yea Postfix is on the machine. I didn't think to check if there is a delay setting, that might be causing a bottleneck. I might just use another module to use SMTP authentication and skip Postfix altogether
| [reply] |
Re: Sending Custom Mass Mail - Fastest Way Possible
by edimusrex (Monk) on May 29, 2015 at 16:45 UTC
|
It does indeed look like it's choking on the sendmail side of things server side. We're using stunnel with postfix and it's getting all sorts of Error detected on SSL (read) file descriptor: Connection reset by peer (104) and such.
Is there another perl module that one would recommend in which I can use SMTP authentication and send HTML formatted email? Maybe it would allow me to bypass the server side mailer. | [reply] |
Re: Sending Custom Mass Mail - Fastest Way Possible
by soonix (Canon) on May 30, 2015 at 10:32 UTC
|
Seconding chacham's remark, I think prepending the table names with "$connect{database}." in the SELECT and UPDATE statements is superfluous, because you are connected with only that database.
Plus removing these parts would increase readability of the SQL strings. | [reply]
[d/l] |
|
|
because you are connected with only that database.
Good point. I completely missed that. FWIW, you can connect to another database in SQL Server by using a qualified object name, which includes the database name. Conversely, this cannot be done in Oracle, as it would require a database link, which instead follows the object name.
| [reply] |
|
|
