Merge documents from your Thunderbird address book

[psilocybe]

Hi,

It is possible to easily merge documents in Writer from your Thunderbird address book.

For this you need:

• Install Thunderbird, if not already done, and create addresses in the address book with at least a name and an email address.

• Install the jdbcDriverOOo extension with LibreOffice.

Once jdbcDriverOOo is installed and LibreOffice is restarted correctly, you must connect to your Thunderbird address book. To do this, make sure Thunderbird is closed, and follow these steps:

• In LibreOffice go to: File-> New -> Database -> Connect to an existing database -> SQLite Driver -> Next:
  • For windows: C:\Users\your_name\AppData\Roaming\Thunderbird\Profiles\your_profile.default-release\abook.sqlite [1]
  • For Linux: /home/your_name/.thunderbird/your_profile.default-release/abook.sqlite [1]

• Finish the wizard with the default options (register the database in LibreOffice so that it is accessible for document merging in Writer) and you should find your Thunderbird address book made up of 3 tables (list_cards, lists, properties)

The Thunderbird address book cannot be used as is, we need to create a view in order to consolidate the data. Thanks to @Villeroy for this query.

In Base create a view called AddressBook (regardless of the SQL command it contains). Once created, try to modify it in SQL mode (right click on the view then Edit in SQL View...) and paste the contents of the following query:

Code: Select all

SELECT DISTINCT "DisplayName"."V" AS "DisplayName", "PrimaryEmail"."V" AS "PrimaryEmail", "SecondEmail"."V" AS "SecondEmail", "AllowRemoteContent"."V" AS "AllowRemoteContent", "CellularNumber"."V" AS "CellularNumber", "Company"."V" AS "Company", "Custom1"."V" AS "Custom1", "FaxNumber"."V" AS "FaxNumber", "FirstName"."V" AS "FirstName", "HomeAddress"."V" AS "HomeAddress", "HomeCity"."V" AS "HomeCity", "HomeCountry"."V" AS "HomeCountry", "HomePhone"."V" AS "HomePhone", "HomeState"."V" AS "HomeState", "HomeZipCode"."V" AS "HomeZipCode", "JobTitle"."V" AS "JobTitle", "LastModifiedDate"."V" / 86400.00000 + 25569.00000 AS "LastModifiedDate", "LastName"."V" AS "LastName", "NickName"."V" AS "NickName", "Notes"."V" AS "Notes", "PhotoType"."V" AS "PhotoType", "PhotoURI"."V" AS "PhotoURI", "PopularityIndex"."V" AS "PopularityIndex", "PreferDisplayName"."V" AS "PreferDisplayName", "PreferMailFormat"."V" AS "PreferMailFormat", "WebPage1"."V" AS "WebPage1", "WorkAddress"."V" AS "WorkAddress", "WorkCity"."V" AS "WorkCity", "WorkCountry"."V" AS "WorkCountry", "WorkPhone"."V" AS "WorkPhone", "WorkState"."V" AS "WorkState", "WorkZipCode"."V" AS "WorkZipCode", "_JabberId"."V" AS "_JabberId", "card", "_vCard"."V" AS "_vCard" FROM "properties" AS "P" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "AllowRemoteContent" ) AS "AllowRemoteContent" ON "P"."card" = "AllowRemoteContent"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "CellularNumber" ) AS "CellularNumber" ON "P"."card" = "CellularNumber"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "Company" ) AS "Company" ON "P"."card" = "Company"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "Custom1" ) AS "Custom1" ON "P"."card" = "Custom1"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "DisplayName" ) AS "DisplayName" ON "P"."card" = "DisplayName"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "FaxNumber" ) AS "FaxNumber" ON "P"."card" = "FaxNumber"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "FirstName" ) AS "FirstName" ON "P"."card" = "FirstName"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "HomeAddress" ) AS "HomeAddress" ON "P"."card" = "HomeAddress"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "HomeCity" ) AS "HomeCity" ON "P"."card" = "HomeCity"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "HomeCountry" ) AS "HomeCountry" ON "P"."card" = "HomeCountry"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "HomePhone" ) AS "HomePhone" ON "P"."card" = "HomePhone"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "HomeState" ) AS "HomeState" ON "P"."card" = "HomeState"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "HomeZipCode" ) AS "HomeZipCode" ON "P"."card" = "HomeZipCode"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "JobTitle" ) AS "JobTitle" ON "P"."card" = "JobTitle"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "LastModifiedDate" ) AS "LastModifiedDate" ON "P"."card" = "LastModifiedDate"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "LastName" ) AS "LastName" ON "P"."card" = "LastName"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "NickName" ) AS "NickName" ON "P"."card" = "NickName"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "Notes" ) AS "Notes" ON "P"."card" = "Notes"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "PhotoType" ) AS "PhotoType" ON "P"."card" = "PhotoType"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "PhotoURI" ) AS "PhotoURI" ON "P"."card" = "PhotoURI"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "PopularityIndex" ) AS "PopularityIndex" ON "P"."card" = "PopularityIndex"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "PreferDisplayName" ) AS "PreferDisplayName" ON "P"."card" = "PreferDisplayName"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "PreferMailFormat" ) AS "PreferMailFormat" ON "P"."card" = "PreferMailFormat"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "PrimaryEmail" ) AS "PrimaryEmail" ON "P"."card" = "PrimaryEmail"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "SecondEmail" ) AS "SecondEmail" ON "P"."card" = "SecondEmail"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WebPage1" ) AS "WebPage1" ON "P"."card" = "WebPage1"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WorkAddress" ) AS "WorkAddress" ON "P"."card" = "WorkAddress"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WorkCity" ) AS "WorkCity" ON "P"."card" = "WorkCity"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WorkCountry" ) AS "WorkCountry" ON "P"."card" = "WorkCountry"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WorkPhone" ) AS "WorkPhone" ON "P"."card" = "WorkPhone"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WorkState" ) AS "WorkState" ON "P"."card" = "WorkState"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "WorkZipCode" ) AS "WorkZipCode" ON "P"."card" = "WorkZipCode"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "_JabberId" ) AS "_JabberId" ON "P"."card" = "_JabberId"."C" LEFT OUTER JOIN ( SELECT "card" AS "C", "value" AS "V" FROM "properties" WHERE "name" = "_vCard" ) AS "_vCard" ON "P"."card" = "_vCard"."C"

You can now leverage the contents of the Thunderbird Address Book (read-only because it is a view) in LibreOffice and perform document merges from your Thunderbird addresses.
This can be done with the merge tools offered by LibreOffice or the eMailerOOo extension which allows to:

• Create mailing list.

• Personalize the subject of the email.

• Send emails in HTML format.

• Merge then convert into PDF format, any files attached to the email.

• Group the sending of the mailing in a thread.

If you want to use eMailerOOo, simply use the previously created view (AddressBook) as the main table and:

• Declare as Email address columns: PrimaryEmail and SecondEmail.

• Declare as Primary key columns the card column (the penultimate in the list of columns).

[1] Take care to replace your_name and your_profile with the values adapted to your configuration.

[Nick N,]

Hi guy,

Didn't you notice that the user can merge emails simply by activating the Option menu? Is your extension by the way really necessary?

Regards

Nick

[psilocybe]

user can merge emails simply by activating the Option menu?

Which menu option allows you to merge with Thunderbird contacts?
Which menu option allows you to merge with:

• Create mailing list.

• Personalize the subject of the email.

• Send emails in HTML format.

• Merge then convert into PDF format, any files attached to the email.

• Group the sending of the mailing in a thread.

I'm afraid that none...

[Nick N,]

Ok! Feel free to implement as you please. Who am I to tell you dos and don'ts.

Anyhow, if you open the User Options dialogue box, you will find above mentionned feature.

Regards

Nick

[psilocybe]

you will find above mentionned feature.

This menu if you are in English is called: Mail Merge Email
It allows you to send emails, but will not work with a Google account, and will under no circumstances allow you to merge Thunderbird contacts into writer documents.
It also does not offer the sending options that the eMailerOOo extension has.

Can you imagine sending a mailing which requires, during each sending, to select the recipients of the mailing.
This is what LibreOffice offers, but in fact it is completely unusable...

In fact it is not comparable...

[Nick N,]

I did not mean to argue. I accept your suggestion because thunderbird address book file is indeed stored with sqlite extension.

Nevertheless in Writer you may also access to thunderbird address source to obtain the list.

Regards

Nick

[donkeybollocks]

I'd like to know what your process is for accessing your Thunderbird address book from Writer.

[Mountaineer]

....
Nevertheless in Writer you may also access to thunderbird address source to obtain the list.

While there is an entry in menu, I guess it will not work since Thunderbird changed the format of the address-book. And the structure of the database is not easily accessed.

I use an ODBC connection and created my own db-view, but I guess some will prefer just using an extension, wich hides the details.

[Nick N,]

Yes, you're right.

To be true to you, i did an address list with base and then i used mail merge feature of writer.

I am sorry, I did not want to bother.

Bye

Nick

[Villeroy]

Hi guy,

Didn't you notice that the user can merge emails simply by activating the Option menu? Is your extension by the way really necessary?

Regards

Nick

In the LibreOffice options, you can specify your outgoing SMTP Server with log-in credentials. However, this feature is broken currently. You can not mail merge via email right now. What you can do is generating text documents or PDFs that can be sent via email with another mail merge tool.
Psilocybe's driver extension has nothing to do Thunderbird's capability to send mail. The driver can be used to access the Thunderbird address book. Several years ago, Libre/OpenOffice was shipped with a database driver for Thunderbird address books stored in *.mab files. Recent versions of Thunderbird store all kinds of data in SQLite databases (*.sqlite files). Libre/OpenOffice never added a built-in SQLite driver for the new address books, since SQLite should be accessible by means of existing open source drivers. Turned out that it was difficult to find the right driver and add it to the office configuration, so one can create Base documents presenting your Thunderbird contacts as database tables.
Even if you managed to do all that, you would have found out that Thunderbird stores all address data as property values in 3 columns contact-ID, property name and property value.
The driver extension makes it very easy to install an adequate version of the SQLite driver which connects Base with the address books, and the view (or query) transforms the property values into a straight table with many columns such as forename, surname, email, street, city, zip, state etc.

[Nick N,]

Good Morning Villeroy,

Thank you for having made yourself clear.

Regards

Nick

[psilocybe]

Thank you Villeroy.

It's still crazy these people who manage to find reviews on software that they haven't even installed or downloaded...
How can you give an opinion on something you don't know?

It is a disease which must be contagious, because on the French site I cannot manage to have a thread not polluted by this type of remark most often coming from the site admins!!!

Now in order to put things in their place I call them the super cadors and apparently there are many...

[psilocybe]

I'd like to know what your process is for accessing your Thunderbird address book from Writer.

There must surely be tutorials on merging in Writer, but to put it simply, in a Writer document, you just have to insert merge fields using the menu:
Insert -> Fields -> More fields
In Database Selection you must select the right data source, open the main table (AddressBook) and choose the field you want to insert.

Now you have a writer document which can be merged on the data source (your Thunderbird address book).

Document field.png (58.63 KiB) Viewed 490 times

Writer document with data source fields.

If you want to see the data from your address book in the document, once the fields have been inserted, simply display the data source in Writer using the menu: View -> Data Sources (Ctrl + Maj + F4)

You must ensure that the correct data source and the correct table (AddressBook) are selected in the browser (left part)
And select in the right part the record that you want to see appear (you must select by the line header in order to have the entire line in blue color)
If you use the Data to Fields button then you will see the data from the data source in the Writer document.

Document data.png (91.15 KiB) Viewed 490 times

Writer document with data to fields.

Besides, I don't explain why we see the AddressBook view twice in the Writer data source browser (Ctrl + Maj + F4)