Mail Merge using a SQL database

    From The Document Foundation Wiki
    < Macros‎ | Writer
    This page contains changes which are not marked for translation.
    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