How to navigate to a specific mail merge entry by macro

English
,
#1

What I want to do is conceptually simple. I have a collection of Calc spreadsheets that each contain the number of a particular row in a database (containing client name, contact info, etc.). The spreadsheet has a combo box containing several Writer templates that all have mail merge fields connected to the same database. I’m trying to make a macro that launches the selected template and automatically navigates to the correct mail merge entry.

It seems like the dispatch command “uno:MailMergeCurrentEntry” almost does what I want, but I can’t figure out how to manipulate what number the current entry is. I guess I could execute MailMergeNextEntry dozens of times in a row, but that hardly seems like the right answer.

#2

No idea, how to solve your problem in a macro, as I worked around this topic…
.
I assume you wish to print one selected letter? I solved this by means of a filter-query and the template is bound to this query, not the complete table. So there is only one row to select, when I print the letter. (But I trigger this from Base and call Writer more as a report-engine then.)

#3

Having some “ID” column (a unique row number, invoice number or similar), you can add a parameter query to the database document like SELECT * FROM "Table" WHERE "ID" = :Enter_ID_Number and link the serial letter to that query (menu:Edit>Change Database…).
This way, you will be prompted for the ID when running the mail merge. The mail merge will be limited to the single record specified by the unique ID.

Another way:
Get the data source window (Ctrl+Shift+F4) and use a filter to curtail the record sets, select the row header left of the right record and click “Data to Fields” which will fill the fields with the data of the selected record.

Another, even more convenient way (still with no macro code), requires that you store database data in a database rather than spreadsheet.

#4

I think you should use the service MailMerge instead of UNO commands.
This service provides all the capabilities for the process. In particular, you can execute a SQL query to retrieve data from the database: property CommandType should be com.sun.star.CommandType.COMMAND, the Command property specifies the SQL query.

1 Like
#5

I think you should use the service MailMerge instead of UNO commands.

I had tried this previously, but couldn’t get it to work until I found this post explaining how to set up the MailMerge service so it gives the right field names to Writer.

Somebody tell me if I did something wrong, but this code does what I was looking for:

Sub MailMergeTemplate

Dim mmService as Object
Dim oRowSet as Variant
Dim dataSheet as Object
Dim tempName as String
Dim template as Object
Dim idCell as String
Dim idNo as String
Dim cmdSQL as String

dataSheet = ThisComponent.Sheets.getByName("Case Info")
idCell = dataSheet.GetCellRangeByName("A1").String 'gets text of ID cell
idNo = Right(idCell, Len(idCell) - 4) 'cuts off "ID: "

mmService = createUnoService("com.sun.star.text.MailMerge")
mmService.setPropertyValue("DataSourceName", "Client Data")
mmService.DocumentURL =  ConvertToURL("C:\Users\[blahblah]\Form.ott")
mmService.OutputType = com.sun.star.text.MailMergeType.SHELL
mmService.CommandType = com.sun.star.sdb.CommandType.TABLE
mmservice.Command = "Cases"

oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.setPropertyValue("DataSourceName", "Client Data")
oRowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
cmdSQL = "SELECT * FROM ""Cases"" WHERE ""ID"" = " & idNo
oRowSet.setPropertyValue("Command", cmdSQL)
oRowSet.execute()

mmService.ResultSet= oRowset

template = mmService.execute(Array())
template.getCurrentController().getFrame().getContainerWindow().setVisible(true)

End Sub
1 Like
#6

@Ratslinger has written many wonderful messages…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.