Mail merge does not work

[Lovro]

Hello,
I am having problem with mailmerge macro functionality. Following macro (below) is in another (Base) document, it finishes with no errors, but document is not showing if outputtype is set to SHELL (4). If I set it to FILE (2) then I get file, but the fields are not populated from database. I would like to have document show on screen and fields populated with field values from database record.

	Dim mm as Object
mm = CreateUnoService("com.sun.star.text.MailMerge") 
'set dbMasterfields = oDoc.getTextFieldMasters()
with mm
	.OutputType = com.sun.star.text.MailMergeType.SHELL
	.OutputUrl = "file:///C:/temp/"
	.DocumentURL = ConvertToURL("C:\Users\Lovro Hrust\Documents\Državna mreža\OB-5.3.2.0-1, Obilazak mjerne postaje-program template.ott")
	.DataSourceName =  "Status postaja" 'database name
	.CommandType = 2 '0=Table, 1 = predefined query, 2 SQL
	.Command = "SELECT ""Postaje"".""Postaja"", ""Obilasci"".""Datum obilaska"", ""Djelatnici"".""Ime"" || ' ' || ""Djelatnici"".""Prezime"" AS ""Osoba_IP"", ""Obilasci"".""Elek ploča"", ""Obilasci"".""Elek ploča napomena"", ""Obilasci"".""Gašenje"", ""Obilasci"".""Gašenje napomena"", ""Obilasci"".""Alarm"", ""Obilasci"".""Alarm napomena"", ""Obilasci"".""Klima"", ""Obilasci"".""Klima napomena"", ""Obilasci"".""Mrežni preklopnk"", ""Obilasci"".""Mrežni preklopnik napomena"", ""Obilasci"".""UPS"", ""Obilasci"".""UPS napomena"", ""Obilasci"".""Temperatura"", ""Obilasci"".""Vlažnost"", ""Obilasci"".""Kontejner"", ""Obilasci"".""Čišćenje"", ""Obilasci"".""Meteorološki krug"", ""Obilasci"".""Razno"" FROM ""Obilasci"" INNER JOIN ""Postaje"" ON ""Obilasci"".""ID_Postaja"" = ""Postaje"".""ID"" INNER JOIN ""Djelatnici"" ON ""Obilasci"".""ID_Djelatnici"" = ""Djelatnici"".""ID"" WHERE ""Obilasci"".""ID"" = " & oControl.Value 
	.execute(Array())
	.dispose
end with

[Lovro]

I discovered that by exchanging the code at the end with
doc= .execute(Array()) doc.getCurrentController().getFrame().getContainerWindow().setVisible(true)
window does show, but the fields are still not populated (actually they disappear)

[Ratslinger]

My results for FILE or SHELL using SQL are as yours - blank field results. However, changing .CommandType = 2 to a 1, placing the SQL in a query and changing .Command = to be “YOUR-QUERY-NAME” worked for both FILE and SHELL.

It seems to use SQL something is missing but haven’t been able to find what it is.

Edit:

Found the missing piece and also my suspicion that field names may play a part in all this.

The code found is on this page.

The specific area:

 'Mailmerge options 
  'OK 
  myMailMerge.CommandType = com.sun.star.sdb.CommandType.TABLE 
  myMailMerge.Command = "biblio" 

 '**************************************************************** 
   ' SQL query 
   Dim oRowSet As Variant 
   Dim cmdSQL As String 
   oRowSet = createUnoService("com.sun.star.sdb.RowSet") 
   oRowSet.setPropertyValue("DataSourceName", "Bibliography") 
   oRowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND 
   cmdSQL = "SELECT * FROM ""biblio"" WHERE ""Year"" <> '2004'" 
   oRowSet.setPropertyValue("Command", cmdSQL) 
   oRowSet.execute() 

   'LinkResultset & Mailmerge 
   myMailMerge.ResultSet = oRowSet 

   '******************************************************************** 

Now on my test I changed all the appropriate fields to coincide with my DB but the result still was blank fields. The remaining key was the field names. Originally set my mail merge documents’ fields to a query named DATEMATH. When I changed myMailMerge.Command = "MY_TABLE" to myMailMerge.Command = "DATEMATH" all worked. Just to prove it wasn’t coming from the original query, I used literals in the SQL statement and the changes showed up in the result. This is probably used with the result set to generate the master field names (just an educated guess).

[Lovro]

I also came to same conclusion like you. So I resolved my problem by changing query programatically (for those interested in code: set RepQuery = ThisDatabaseDocument.CurrentController.ActiveConnection.Queries.getByName("Name of query") RepQuery.Command = "SQL Command goes here" and calling query instead of SELECT statement. Either it is a bug or we are missing something.

[Ratslinger]

I’m not sure you got my answer. The edit portion shows how to use a SQL statement (Select) and it works in my testing. It just needs care in setting up because of master field naming. But I have now used this successfully for both Query & SQL and for both FILE & SHELL.