Mail Merge using a SQL database

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

Mail Merge is a LibreOffice feature that allows you to write a template letter with predefined fields and then populate that template with fields from a database. This is very useful if you need to send the same letter to a number of people with only small changes like the name and address of the person you're sending it to.

How to use it

After downloading the mailmerge_by-lanedoCOM.odt file and opening it, you can use the macros contained in the document. Edit the template as you see fit and then run the macros using the menus:

Tools ▸ Macros ▸ Run Macros
mailmerge_by-lanedoCOM.odt → Standard → Mail → DoManualMailMerge

NOTE: The macros connect to a database and will do nothing if that database is not available. So you cannot expect it to just work from downloading the file alone.

Downloads

   Media:mailmerge_by-lanedoCOM.odt

How it works

The macro connects to a SQL database and populates the fields of the template. The output is stored in one big single PDF file to make printing easier.

The template and the DB configuration are specific to Lanedo business, but they can be easily customized.

REM  *****  BASIC  *****

Sub DoManualMerge

Dim args(1) As New com.sun.star.beans.PropertyValue

doc = ThisComponent 
fm = doc.getTextFieldMasters()

'names = fm.getElementNames ()
'For idx = 0 To UBound(names())
'	print names(idx)
'Next idx

'https://wiki.openoffice.org/wiki/Documentation/DevGuide/Text/Text_Fields

res = GetResultSetFromMDB ()

Do While res.next ()
	meta = res.getMetaData()
	cur_row = res.getRow ()
	
	field = fm.getByName(TFName("Title"))
	field.Content = res.getString (9)
	
	field = fm.getByName(TFName("FirstName"))
	firstname = res.getString (2)
	field.Content = firstname

	field = fm.getByName(TFName("Surname"))
	surname = res.getString (3)
	field.Content = surname
	
	field = fm.getByName(TFName("Street"))
	field.Content = res.getString (4)
	
	field = fm.getByName(TFName("Housenumber"))
	field.Content = res.getString (5)
	
	field = fm.getByName(TFName("ZIP"))
	field.Content = res.getString (6)
	
	field = fm.getByName(TFName("City"))
	field.Content = res.getString (7)
	
	field = fm.getByName(TFName("Country"))
	field.Content = res.getString (8)
	
	doc.TextFields.refresh()
	args(0).Name = "FilterName"
	args(0).Value = "writer_pdf_Export"
	url = "file:///Users/gicmo/Coding/Lanedo/MM_" & firstname & "_" & surname & ".pdf"
	
	doc.storeToURL(url, args())

Loop	


End Sub

Function TFName (str As String) As String
 TFName = "com.sun.star.text.fieldmaster.User.Recp" & str
End Function

Sub MailMergeService

Dim MM as Object

MM = CreateUnoService("com.sun.star.text.MailMerge")
MM.DocumentURL = "file:///Users/gicmo/Coding/Lanedo/MailMerge.odt" 'sourceDocName
MM.OutputType = com.sun.star.text.MailMergeType.FILE
MM.OutputUrl = "file:///Users/gicmo/Coding/Lanedo/"
MM.FileNamePrefix = "MM_"
MM.SaveAsSingleFile=True
MM.DataSourceName =  "testdb_marvin"
MM.CommandType = 1 '1 = predefined query, 2 SQL
MM.Command = "all_data"
MM.execute(Array())
MM.dispose()

End Sub


Function db_escape (str As String, connection) As String
 Dim delim As String
 meta = connection.getMetaData ()
 delim = meta.getIdentifierQuoteString ()
 db_escape = delim & str & delim
End Function


Sub GetResultSetFromMDB as Object

Dim dm
Dim driver
Dim params(1) As New com.sun.star.beans.PropertyValue

REM Get Current Spreadsheet
REM http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/module-ix.html
REM  http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/SpreadsheetDocument.html (getSheets -> XSpreadsheets)
REM http://api.openoffice.org/docs/common/ref/com/sun/star/table/module-ix.html
doc = ThisComponent

url = "jdbc:mysql://marvin.local/testdb?user=gicmo&password=XXXXX"

' Database Setup
dm = CreateUnoService ("com.sun.star.sdbc.DriverManager")

params(0).Name = "JavaDriverClass"
params(0).Value = "com.mysql.jdbc.Driver"

con = dm.getConnectionWithInfo (url, params())

if IsNull (con) Then
	MsgBox "Could not connect to DB"
	Exit Sub
End if 

stmt = con.CreateStatement()
sqltxt = "SELECT * FROM " & db_escape("addressbook", con)
GetResultSetFromMDB = stmt.executeQuery (sqltxt)
End Sub

Macros