# Mass Mail

If you are willing to use mass mail function which is available in Atollon, you should probably follow next steps before you starts.

### Introduction - how does it work?

First of all you need a distribution group. This can be done by following examples

1. create report and use send mass mail button,
2. create your own distribution group within Contact module and drag&drop contact you would like to have in this group,
3. use some nifty sql functions to create your distribution group :).

#### Wanna language-based sending?

This feature is actually not working in mass mailing so the nifty sql's are needed to create language-based distribution groups. Then you of course can send emails which speak the same language recipient does.

Just to show you how you might do that:

1. Create empty distribution group in contacts.
2. Find out it's id using SELECT id from contact\_distributiongroups where name like 'name-of-your-group';
3. Insert all contact which belongs to your organization and speak Czech:  
    insert into contact\_bind\_distrgroup(dgroup, contact)  
     select $YOUR-DISTRIBUTION-GROUP-ID, occ.contact from organization\_contact\_context occ  
     left join  
     contact\_personinfo cpi on occ.contact = cpi.contact  
     where  
     occ.organization = 1085000 and  
     cpi.preferedlang = 2607000  
    ;

If you are done with distribution group creation process you can switch to real sending. But wait. We should speak more about what's behind it. Sooo

### Default folder aka organization\_contact\_context table

I would say you probably don't know what do I speak about. Let me just explain you. Every contact has its own default folder. As you probably know, every contact can have more than one folder. In this case - who knows which one is the default one? The answer is **default folder**. And also, you probably don't want to create folder for every *person* you have in your system. In this case **Default folder** is your very big friend again.

If you don't want to create Folder for every person in the system (what you probably really don't want to) you can easily update Default Folder using person's **relationship with employer**. This is pretty great because if you update Default Folder for the people you are willing to send your newsletter to, this message will actually stay on the person's employer's Folder.

#### What's the organization\_contact\_context table structure

That's quite simple. Table takes in consideration the contact might have different Default Folder accross organizations which are implemented in the system. So for that you can see following columns within it:

- organization
- contact
- subject
- project
- activity

#### Updating default folder

But first of all you have to update **Primary Employer**. In order to do that there is already (working) function within the Atollon. Just open up windows client and go to Settings -> Contacts -> Contacts maintainance -> Setup default main contact. Run it and make yourself a cup of coffee.

Output of the following command is delete/insert sql function list. You can use **\\o /tmp/your-update-scrip.sql** directly within psql so the output of the select is sent to the update script on your drive and you can import it after using **psql -d $YOUR-DATABASE -f /tmp/your-update-scrip.sql**. The command includes two values you should find and replace before you'll go to use it.

1. $ORGANIZATION-ID - is id of your organization, of course,
2. $ORGANIZATION-SUJBECT - is subject id of the organization.

```
SELECT 'delete from organization_contact_context where contact = '||cm.id||'; insert into organization_contact_context(organization, contact, subject, project) values($ORGANIZATION-ID, '||cm.id||', '||ss.id||', NULL);' from contact_main cm
left join (
    select distinct on (cpc.contact) s.id, cpc.contact as contact from subject s
        inner join contact_personcompany cpc on s.contact = cpc.company
        where
            cpc.isprimary = true
        order by contact
    ) ss on cm.id = ss.contact

where
    ss.id > 0 and

    cm.id in (
        select contact from contact_personcompany where company in (
            SELECT contact from subject where organization = $ORGANIZATION-ID group by contact
            )
        ) and
    cm.id not in (
        select contact from organization_contact_context where subject <> $ORGANIZATION-SUJBECT
    )
order by
    cm.id
;
```

The command itself might be a bit more updated because:

1. company can have two Folders and we are selecting just first one (but how we can know which is the correct one?),
2. person can have only one Primary Employer but he might also be excluded (he got fired) from that company and then validTo timestamp should be considered in that select too.

### Done with distribution groups, ready for mass mailing

If you did update default folder, created your distribution group and are happy what you have you can switch to the real [mass mail sending](mks://localhost/en/user-manual/marketing/Mass_Correspondence "mass-correspondence").

### Bugging

What if something went bad ...

#### Flash player crashed by mass mailing

Well ... that's great question. I was checking contact\_mmlist\_temp table for some changes and experienced some status changes but are those messages being still send or not?

Small hint - use following command where $MASS-MAIL-ISSUE-ID is id of your last sending (you might find it by selecting over all issues and trying to remember for how many contact did you send that mass mail) and check for changes.

SELECT status, count(status) from cONTACT\_MMLIST\_TEMP where issue = $MASS-MAIL-ISSUE-ID group by status;

#### Can not create mass mail list on the server side

This means your temporary mass mail list couldn't be created. This might be probably really bad bug. In my case the problem was in the insert of the distributiongroupid and contactid in the contact\_mmlist\_temp. I found out which insert was the wrong one and tried to apply it manually within plsql where I didn't experience any problem.