Mass Mail/Newsletters Via Cronjob

[Jafo232]

For those of you who use the "Send Email to Users" function in the admin cp may have noticed it is less than efficient. This is really not the Vbulletin teams fault, it is just the fact that sending a lot of emails via PHP is like emptying lake with a bucket.

For administrators with a very large user base, sending email this way is not only very time consuming, but can be taxing on a servers resources.

With this extension, you can save your mass mailings or newsletters in a batch that can be sent at any time you wish, or on a schedule using a program like crontab.

You can even use a separate server to send the email if you wish.

Version 1.1 now supports the hack Send HTML via admin CP (https://vborg.vbsupport.ru/showthread.php?t=72105) so you can batch mail HTML emails.



Requirements:

Perl (Some hosts call this CGI) w/ the DBI module (MySQL driver)
Net::SMTP module IF you plan on using SMTP (common Perl module)
Sendmail or SMTP
Access to crontab (for scheduled processing)
Telnet/SSH access to your server (for testing purposes only)

As for Telnet/SSH access, you will most likely need this unless you can schedule a cronjob via your servers admin GUI.


CHANGELOG:

01/05/2005: Added support for SMTP
01/05/2005: Modified code to include Vbulletin generated mail headers and encoding


Installation (detailed in readme file):

Install the product via your admin cp.

Open the list.cgi file and edit the database variables and upload to server IN ASCII MODE. Do not use Binary or Auto mode to upload list.cgi. You should upload this file in an area that is NOT accessible by a web browser.

Click Install (https://vborg.vbsupport.ru/vborg_miscactions.php?do=installhack&threadid=104459)

That is it. If you wish to use scheduled batches then you will have to set up a cronjob to execute the list.cgi file.

You can execute the list.cgi file any time you wish on your server like so:

"perl list.cgi"



What does this do exactly?:

In a nutshell, when you enter a message into the mass mail feature of Vbulletin, it saves the message details to a new table created by the product (massmail). When list.cgi is executed, it will go through this table and email everyone in the table and delete their entry after the mail is sent.



Usage:

The only thing you should do differently is set the "Emails to send at once" value to at least the amount of users you have registered. It defaults at 500 but the whole point of this hack is to eliminate having to send email in blocks and wait for the process to finish. Just set it to a real high number.



To Do:

For those Windows servers, I suppose the Send_Mail routine could use SMTP. This is not hard to do I just never use Windows servers so what's the point? :rolleyes:

[Logician]

Very nice idea! Thanks for sharing!

[Man1c M0g]

Does this hack allow you to send HTML-based Newsletters, and can it send to members of a secondary usergroup? Because those abilities would be ULTRA useful! :)

[silurius]

Does this hack allow you to send HTML-based Newsletters, and can it send to members of a secondary usergroup? Because those abilities would be ULTRA useful! :)It's just a guess but I think this extension does not actually alter email format in any way, and focuses exclusively on send-related functionality.

Here's another mod (https://vborg.vbsupport.ru/showthread.php?t=99910) that might do some of what you're after.

[Jafo232]

Well, according to Perlmonks, there is a wrapper with Qmail that emulates sendmail:

http://www.perlmonks.com/?node_id=224380

Qmail comes with a wrapper (in /var/qmail/bin/sendmail, or in the bin directory of wherever you installed Qmail) that emulates sendmail nicely.

My guess is that all you do is enter /var/qmail/bin/sendmail as your sendmail path and it should work.

[kadafi_alive]

Hi, this might be a newbie question but how do you find out if you have:

Perl (Some hosts call this CGI) w/ the DBI module (MySQL driver)

installed?

Thanks in advance

[Snake]

Thanks!

[tuanluu]

thanks for sharing

[tuanluu]

$path_to_sendmail = '/usr/lib/sendmail'; -------> for win 2003 what should I put?

"Upload the list.cgi file IN ASCII (NOT BINARY OR AUTO) mode somewhere on your server where it CANNOT be accessed by a browser or any other user except yourself." -------> you mean anywhere on my server or in forum root? for example c:\secure

[Jafo232]

Hi, this might be a newbie question but how do you find out if you have:

Perl (Some hosts call this CGI) w/ the DBI module (MySQL driver)

installed?

Thanks in advance

Well, most hosts have Perl installed, but your host may be able to tell you. Generally Perl installations also have DBI installed, but you will know pretty quick when you run the Perl script because it will throw a visible error saying it cannot find the module.

[Jafo232]

$path_to_sendmail = '/usr/lib/sendmail'; -------> for win 2003 what should I put?

"Upload the list.cgi file IN ASCII (NOT BINARY OR AUTO) mode somewhere on your server where it CANNOT be accessed by a browser or any other user except yourself." -------> you mean anywhere on my server or in forum root? for example c:\secure


Where is your Sendmail program located? Most Windows servers do not have Sendmail, so your probably out of luck until I write up an SMTP routine.

As for your second question, I do not think the instructions were vague, put is somewhere where nobody can access it via a web browser. Where that is on your system, only you and your host will know that.

[Paul M]

Interesting .... I slightly modified the existing vb mail system to process it's queue every 15 minutes via a vb cron job - pretty much the same end result, but using the existing mail tables and system. I had considered releasing this at some point but this has probably saved me the effort now. :)

[tuanluu]

okay thanks for quick reply

[Jafo232]

Interesting .... I slightly modified the existing vb mail system to process it's queue every 15 minutes via a vb cron job - pretty much the same end result, but using the existing mail tables and system. I had considered releasing this at some point but this has probably saved me the effort now. :)

Using the vb cronjob would probably cause the same issues for large userbases as PHP will still hang (and possibly timeout) with large mailings.

I usually prefer to write up a separate tables for my hacks just so they do not muck with user data by accident (faulty uninstalls, etc).

[Jafo232]

Ok, I added SMTP support for you Windows users.

I also modified the code so it will be compatible with the HTML hack here (https://vborg.vbsupport.ru/showthread.php?t=72105) .

I also modified the code a bit to use the Vbulletin headers which is needed for the HTML hack and non English sites.

Just uninstall product, and re-install and you are all set.

[BWilliams]

Getting this error:

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '$db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX .

[Jafo232]

I cannot reproduce that error. Perhaps when you unziped the file, it was corrupted?

[kadafi_alive]

Hey, is it possible to incoporate this with the Vb Commbull (newsletter) hack?

[Jafo232]

Hey, is it possible to incoporate this with the Vb Commbull (newsletter) hack?

I am unsure, do you have a link to this hack?

[groberthall]

I would also like to see a combination hack.... Commbull by cron......ummm

the lunk is
https://vborg.vbsupport.ru/showthread.php?t=97809&page=1&pp=15&highlight=commbull

[Keyser S?ze]

this isnt a newsletter its more of an enhancement to the sendmail function in the admin cp

[Jafo232]

I do not think it will work with that hack, sorry.

[ChurchMedia]

This is cool. Could it be modified to work with the Community Bulletin Email Generator hack?

Thanks!

[Jafo232]

This is cool. Could it be modified to work with the Community Bulletin Email Generator hack?

Thanks!

Please read like 4 posts up..

[globalinsites]

Great hack. I will put it on my list of hacks to install. Does this work for the combull community bulletin hack too? Or can it be made to work with the combull community hack too? Whenever I use the combull hack I can send a maximum of 1300 at a time. If for example I try to send 1500 at a time then it usually freezes after a while and I have to start over again.

Edit: Oops, sorry. I used quick reply before I read the page with the latest posts..

[ChurchMedia]

Just FYI, I used this to try to send out 28,000 newsletters. It killed my server and MySQL is dead and won't restart. When it was working it would send out 5-10 emails to the same person every once and a while. My site is down now. I'm not sure what caused it exactly, but I'll have to stick to my other mail program. :(

[NxTek]

I'd like to use this to send out a monthly reminder. If this is setup as a cron and I'd like to send out other mass mails throughout the month, won't it resend those too?

Unless I'm misunderstanding something, I don't see how this automates things if the message has to be entered into the admincp.

[Jafo232]

Unless I'm misunderstanding something, I don't see how this automates things if the message has to be entered into the admincp.

Because when you send a message through the admincp, you have to keep clicking on pages until all the mail is sent. Get a site with thousands of users, this could take all day.

[NxTek]

The only thing you should do differently is set the "Emails to send at once" value to at least the amount of users you have registered. It defaults at 500 but the whole point of this hack is to eliminate having to send email in blocks and wait for the process to finish. Just set it to a real high number.
So why not just do that to begin with? That's what I do manually every month. I set it to 10000 and send the mail.

[Jafo232]

So why not just do that to begin with? That's what I do manually every month. I set it to 10000 and send the mail.

Well for one thing, chances are your server times out before all the emails are sent. Many servers only allow a PHP script to execute for X amount of seconds before its process is killed. In fact, it is a setting in the php.ini file.

If you have a lot of users, it could take quite a long time before it can send to all those users, and most likely, will time out.

See http://us3.php.net/manual/en/ref.info.php#ini.max-execution-time

For more information

[NxTek]

I'm sending out about 5300 each month with no problem. I was under the impression this mod would send out the monthly reminder without me manually doing it.

[Jafo232]

Your impressions were wrong. :)

[NxTek]

Yes I guess so :D

[NxTek]

When trying to uninstall -

Database error in vBulletin 3.5.2:

Invalid SQL:
DROP massmail;

MySQL Error : You have an error in your SQL syntax near 'massmail' at line 1 Error Number : 1064
Date : Thursday, February 2nd 2006 @ 08:21:39 AM
Script : http://www.xxxx.com/forums/admincp/plugin.php
Referrer : http://www.xxxx.com/forums/admincp/plugin.php?do=productdelete&productid=Mass-Mail-Cron&s=

[lisquid]

My host limits me to 600 emails an hour do you think you could give us the option to send the emails in batch ammounts every [integer of time we input beetween batches here] until complete?

[UK Jimbo]

I like the sound of this hack, it would be really nice to get it to have it look at load averages and only proceed with the next block if the load on the server is below a given amount. I'll see if I can add this feature in :)

[oliwood]

Just FYI, I used this to try to send out 28,000 newsletters. It killed my server and MySQL is dead and won't restart. When it was working it would send out 5-10 emails to the same person every once and a while. My site is down now. I'm not sure what caused it exactly, but I'll have to stick to my other mail program. :(

exactly the same for me... is there a bugfix ofr have i to remove this nice hack?

The cronjob runs once a day at 1 a.m. but also the Mails were send 10 times to poeple and the my server crachs after half an hour.

Would it help to add a sleep 1 after the removing of the MySQL entry?

[dutchbb]

Wow I really need this, is there a 3.6 release planned please?

[Joep11]

Wow I really need this, is there a 3.6 release planned please?

Curious about that too!

[grandeur_69]

i haven't read the whole post, so pardon if this is repeated, but doesn't vB have a batching option? or is this something different?

[masterross]

hi,

i've got this error after sending first mail:
DBD::mysql::db do failed: MySQL server has gone away at /root/list.cgi line 29.
Broken pipe DBI: MySQL server has gone away at /root/list.cgi line 29.


any help?

[rastler]

Hi,

I have a doubt,

If we use this Hack will the mails be sent twice, Once using the PERL file and the massmail table and second with existing mail system of vbMail ?

Rastler

[xcingix]

When trying to uninstall -

Database error in vBulletin 3.5.2:

Invalid SQL:
DROP massmail;

MySQL Error : You have an error in your SQL syntax near 'massmail' at line 1 Error Number : 1064
Date : Thursday, February 2nd 2006 @ 08:21:39 AM
Script : http://www.xxxx.com/forums/admincp/plugin.php
Referrer : http://www.xxxx.com/forums/admincp/plugin.php?do=productdelete&productid=Mass-Mail-Cron&s=
I have the same problem.
I have left this product in my instillation for years because I cant uninstall it.
Can someone help me uninstall this?

[UK Jimbo]

I've not ever installed this software but I'm pretty confident that the uninstall is trying to remove the massmail table from the database. In which case the correct syntax is:

DROP TABLE massmail;

[xcingix]

I've not ever installed this software but I'm pretty confident that the uninstall is trying to remove the massmail table from the database. In which case the correct syntax is:

DROP TABLE massmail;just run that query and it will be uninstalled?

[UK Jimbo]

just run that query and it will be uninstalled?

No. You need to modify the uninstall code for the product.

Login to your admincp and go to "Manage Products"

Find this product and choose to edit it.

Look for the "DROP massmail" text in the uninstall code and replace it with "DROP TABLE massmail"

Save these changes and then try running the uninstall process again.

[xcingix]

No. You need to modify the uninstall code for the product.

Login to your admincp and go to "Manage Products"

Find this product and choose to edit it.

Look for the "DROP massmail" text in the uninstall code and replace it with "DROP TABLE massmail"

Save these changes and then try running the uninstall process again.
this is the current uninstall though.
$db->query_write("DROP " . TABLE_PREFIX . "massmail");

[UK Jimbo]

Looks like it does just remove that table. To make it run correctly change it to the following:

$db->query_write("DROP TABLE " . TABLE_PREFIX . "massmail");

[xcingix]

Looks like it does just remove that table. To make it run correctly change it to the following:

$db->query_write("DROP TABLE " . TABLE_PREFIX . "massmail");still doesnt work
Database error in vBulletin 3.7.0 Beta 2:

Invalid SQL:
DROP TABLE massmail;

MySQL Error : Unknown table 'massmail'
Error Number : 1051
Date : Tuesday, December 18th 2007 @ 10:16:28 PM
Script : http://forums.ny-dev.com/admincp/plugin.php?do=productkill
Referrer : http://forums.ny-dev.com/admincp/plugin.php?do=productdelete&productid=Mass-Mail-Cron&s=
IP Address : 69.112.68.55
Username : danny.michel
Classname : vb_database

[xcingix]

anyone?