Mail Merge using a SQL database

From The Document Foundation Wiki
< Macros‎ | Writer
Jump to navigation Jump to search
Other languages:
English • ‎Esperanto • ‎español • ‎italiano

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.



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

doc = ThisComponent 
fm = doc.getTextFieldMasters()

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


res = GetResultSetFromMDB ()

Do While ()
	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)
	args(0).Name = "FilterName"
	args(0).Value = "writer_pdf_Export"
	url = "file:///Users/gicmo/Coding/Lanedo/MM_" & firstname & "_" & surname & ".pdf"
	doc.storeToURL(url, args())


End Sub

Function TFName (str As String) As String
 TFName = "" & str
End Function

Sub MailMergeService

Dim MM as Object

MM = CreateUnoService("")
MM.DocumentURL = "file:///Users/gicmo/Coding/Lanedo/MailMerge.odt" 'sourceDocName
MM.OutputType =
MM.OutputUrl = "file:///Users/gicmo/Coding/Lanedo/"
MM.FileNamePrefix = "MM_"
MM.DataSourceName =  "testdb_marvin"
MM.CommandType = 1 '1 = predefined query, 2 SQL
MM.Command = "all_data"

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

REM Get Current Spreadsheet
REM (getSheets -> XSpreadsheets)
doc = ThisComponent

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

' Database Setup
dm = CreateUnoService ("")

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