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.
- 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)
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"
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.
- Declare as Email address columns: PrimaryEmail and SecondEmail.
- Declare as Primary key columns the card column (the penultimate in the list of columns).