Ask Your Question

Mail merge does not work [closed]

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

Lovro gravatar image

updated 2020-07-21 05:55:25 +0200

Alex Kemp 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("") 
'set dbMasterfields = oDoc.getTextFieldMasters()
with mm
    .OutputType =
    .OutputUrl = "file:///C:/temp/"
    .DocumentURL = ConvertToURL("C:\Users\Lovro Hrust\Documents\Državna mreža\OB-, Obilazak mjerne postaje-program")
    .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 
end with
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-21 05:55:44.322254


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

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.


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 
  myMailMerge.CommandType = 
  myMailMerge.Command = "biblio" 

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

   '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


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

Question Tools

1 follower


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

Seen: 619 times

Last updated: Jan 26 '17