Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Sending Custom Mass Mail - Fastest Way Possible

by edimusrex (Monk)
on May 29, 2015 at 15:05 UTC ( #1128296=perlquestion: print w/replies, xml ) Need Help??

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

Replies are listed 'Best First'.
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

      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; }

        Awesome. Glad it was of help. I develop professionally in Python now, so it's nice to keep somewhat fresh with Perl by trying to help out here.

        Cheers,

        -stevieb

      Yea, that seems like a good option. So maybe ingest my email list from the database into a hash first on fork from there.
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.

      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

        Yes, that's orders of magnitude too long. Time to do some benchmarking of your code and find the bottleneck. Have you used Devel::NYTProf before? It would be my initial recommendation.

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?

      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.

        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. :)

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.

    -derby
      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
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.
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.

      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1128296]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (5)
As of 2022-05-27 14:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (95 votes). Check out past polls.

    Notices?