[Solved] Mail merge from basic using a query

[nmarco]

Hi all, I'm (desperately) looking for a working example of a simple mail merge using oobasic
getting data from a query.
I've found 2 examples in forums, but none of them seems working. I don't understand the meaning
of:
Command
and
DataSourceName

I've tried something like this:

Code: Select all

       oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
       oMailMerge.CommandType = QUERY
       oMailMerge.Command = ?
...

Whatever I write on oMailMerge.Command gives me the error:
com.sun.star.sdbc.SQLexception

Can someone give me a simple but working example ?
Thank you very much
Marco

[Villeroy]

What is "QUERY"? And if it you mean a certain UNO constant it should be specified as such: com.sun.star.some_module.some_group.QUERY
Usually, a constant named ....QUERY means that the command specifies some name of a query stored in the database.
Like this constant group: http://api.openoffice.org/docs/common/r ... dType.html

c.s.s.ct.TABLE with some table name
c.s.s.ct.QUERY with some query name
c.s.s.ct.COMMAND with "SELECT ... FROM ..."

[nmarco]

Hi, thank you very much for the answer
I changed the line:

oMailMerge.CommandType = 1

I think it's the correct value

The code now is:

Code: Select all

oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
oMailMerge.DocumentURL =  docName
oMailMerge.DataSourceName =  "DB-POS"
oMailMerge.CommandType = 1
oMailMerge.Command = "writer-pagina_iniziale" ' connection.Queries.getbyname("writer-pagina_iniziale").Command ' "writer-pagina_iniziale"
oMailMerge.OutputType = com.sun.star.text.MailMergeType.FILE
oMailMerge.OutputUrl = "file:///C:/"
oMailMerge.SaveAsSingleFile=True
oMailMerge.execute(Array())
oMailMerge.dispose()

DB-POS is the registered name of my DB
writer-pagina_iniziale is the name of the stored query inside the database
doc-name is a string with the correct URL of the document (with mailmerge fields based on "writer-pagina_iniziale")

With this code I get the error (I traslate it from italian):
Operation not supported from the OS
What's wrong with it ?

Marco

[nmarco]

Solved ... the problem was a missing parameter, "FileNamePrefix" ...
The complete working (for me) code for a query based MailMerge with file output is:

Code: Select all

Sub myMailMerge(sourceDocName as String, dbName as String, queryName as String, outputDocDir as String, outputDocPrefix as String)

Dim oMailMerge as Object
oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
oMailMerge.DocumentURL =  sourceDocName
oMailMerge.DataSourceName =  dbName
oMailMerge.CommandType = 1
oMailMerge.Command = queryName
oMailMerge.OutputType = com.sun.star.text.MailMergeType.FILE
oMailMerge.OutputUrl = outputDocDir
oMailMerge.FileNamePrefix = outputDocPrefix
oMailMerge.SaveAsSingleFile=True
oMailMerge.execute(Array())
oMailMerge.dispose()
End Sub

sourceDocName: it's the URL of the document with mailmerge fields
dbName: it's the name of the registered database containing the (see next line)
queryName: the query saved in the database
outputDocDir: directory where I want to put the output
outputDocPrefix: prefix of the filename generated

( It is heavily based on the code I've found here: http://user.services.openoffice.org/en/ ... 20&t=22804 )

This function can be written better, and there are a lot of other options for the mailmerge. But it works for me, and I'm not a programmer. I hope that someone more
skilled can write a better function for the community

I think that changing CommandType to 0 and Command to the name of the table (for a table based mailmerge) or CommandType to 2 and Command to a SQL query for a direct
query Mailmerge should work, but I have no time to test it now.

Thank to Villeroy for having answered me
Marco