Ask Your Question
0

Mail merge does not work

asked 2017-01-25 11:57:32 +0200

Lovro gravatar image

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
edit retag flag offensive close merge delete

Comments

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)

Lovro gravatar imageLovro ( 2017-01-25 12:10:09 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-01-25 23:34:21 +0200

Ratslinger gravatar image

updated 2017-01-26 02:31:43 +0200

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).

edit flag offensive delete link more

Comments

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.

Lovro gravatar imageLovro ( 2017-01-26 16:58:10 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-01-26 18:09:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-01-25 11:57:32 +0200

Seen: 413 times

Last updated: Jan 26 '17