unsubscribe from mailing lists

In case you are using lot mailing lists with expected frequent change of subscribers amount, following scenario might be helpfull for you. Its written for separate mysql database called e.g. mailing_lists with two tables, first table ml_addr is for storing mail acounts with auto-incremented ID as primary key and type 1 (for mailing list) and default null for members, unique index is set for column 'a_email', second table ml_lst is the "tying table" where are two columns lst_id for id of mailing list and mb_id for id of subscribers, unique index are set for combination (lst_id, mb_id) because users can be subscribed in more than 1 mailing lists.

Whole process is rule based, therefore you can specify in which case you want to add or remove users in/from mailing list subscription.

Database creation part

mysql>create database mailing_lists collate utf8_general_ci;

1.table:

mysql>create table ml_addr ( id int(11) not null auto_increment, a_type int(1) default null, a_email varchar (255) not null unique, primary key (id));

2.table:

mysql>create table ml_lst ( lst_id int(11), mb_id int(11), unique key (lst_id, mb_id));

Now you need to insert manually mailing lists addresses into database, for mutiple insertion search google how to insert from bulk file:

mysql>insert into ml_addr (a_type, a_email) values ( '1', 'mailinglist@yourdomain.net');

In this case as this was first record in ml_addr table its id is 1, so in console>domain>Mailing Lists>mailinglist

-select members from database

-set DB settings to connect to the created mamiling_lists database

-sql statement, I would suggest to fill there full path to txt file with following sql statement:

select ml_addr.a_email from ml_addr join ml_lst on ml_addr.id = ml_lst.mb_id where ml_lst.lst_id='%id of your mailing list%'

to show id of mailing lists simply execute: select * from ml_addr where a_type='1';

Content filter part:

subscribe member:

to auto insert account as specific mailing list create 2 rules to match:

where To: message header matches: mailinglist1@domain.net;mailinglist2@domain.org...

and where subject matches: subscribe

action:

Exectute sql statement:

insert into ml_addr (a_email) values ('%%Sender_Email%%');

create second rule with same condition as first rule with action:

Execute sql statement:

insert into ml_lst (lst_id, mb_id) select s1.id as mlid, s2.id as uid from ml_addr s1 cross join ml_addr s2 where s1.id <> s2.id and s1.a_email='%%Recipient_Email%%' and s2.a_email='%%Sender_Email%%';

-two rules are required because we can execute only one sql statement in one rule or you can use these sql statements in a script made for insertion into mysql from a bulk file: %%sender_email%% is account you want to add as a member of a mailinglist (%%recipient_email%%)

unsubscribe member:

create rule:

Where To: message header matches mailinglist@domain.net

 AND Where Subject: message header matches unsubscribe

actions:

Exectute sql statement:

delete from ml_lst where lst_id in (select id from ml_addr where a_email='%%Recipient_Email%%') and mb_id in (select id from ml_addr where a_email='%%Sender_Email%%');

and delete message

and stop processing more rules

final step is to add html footer to mailing list in mailing lists>message tab

here is an example of html footer with "mail to" function:

<i>
<br><a href="mailto:mailinglist@domain.net?subject=unsubscribe">unsubscribe</a>
</i>

for subscription change "unsubscribe" to "subscribe" to be triggered by the rule created for subscription, you can have both links in footer of each message.

To clean up the database from emails whitch are no longer used create a windows task or cron job to execute:

delete from ml_addr where id not in (select lst_id from ml_lst) and id not in (select mb_id from ml_lst);

!be aware, this will delete every record from table ml_addr which ID is not used in ml_lst table, so in case you would have there stored email of mailing list without members, it will be erased also.

Note: This scenario does not support personalised variables, you would have to modify table ml_addr according to the f1 help in mailing list chapter:

Variable values can be stored within the database and extracted within this SQL to create personalized messages, but:

-the first field returned MUST be the email address
-the second field returned (if at all) MUST be the member Rights or blank
-subsequent fields can be any variable values you wish to use.

o.vanek