[Solved] Automate Mail Merge and PDF creation

[mbj42938]

I'm trying to figure out how to automate MailMerge and PDF creation with OpenOffice with either Visual FoxPro or Visual Basic.

It's relatively easy using Microsoft Word but I can't figure out how to do this with OpenOffice. The code is relatively simple in Word like this:

goWord = CreateObject("word.application")
goWord.documents.open(lcTemplate)
goWord.ActiveDocument.MailMerge.OpenDataSource(lcDataFile)
goWord.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = .f.
goWord.ActiveDocument.MailMerge.Execute()

goWord.ActiveDocument.SaveAs(m.lcFileNew)
goWord.ActiveDocument.ExportAsFixedFormat(lcFilePDF, 17, .f., 0, 0, 1, 1, 0, .t., .t., 0, .t., .t., .f.)

The code above will mail merge a CSV file with a Word document and the last two lines will save the merged doc file and a pdf file.

How can I do this with OpenOffice?

[JeJe]

Have you looked at the documentation on automation?

https://www.openoffice.org/udk/common/m ... ation.html

A search will find threads here on mail merge and PDF Creation.

[mbj42938]

I spent hours trying to find what is probably just a few line of code so I was hoping maybe someone here might be able to provide it.

[JeJe]

My guess is its unlikely its going to be a few lines of code - some things are much longer in OO that Office VBA. So if you post what you've got and where you're stuck - at the moment you're asking us to write it all for you. (Someone might... sometimes they do... but, the idea of this is to help people solve their problem not do it for them, and also not many people use automation.)

[mbj42938]

Thank you again for your response. I did not know it was going to be a lot more code in OO than Microsoft Office. So, I wasn't expecting anyone to write it all for me if it is a lot more than a few lines of code that I posted. The reason I'm using automation is because I'm using a back end web server and I want to automate mail merge with a doc and CSV file and then have the ability to convert to PDF. Maybe OO will do this through automation or perhaps there is another program that will work besides MS Office that is open source. I found a few articles and got OO to automate things like typing in text but I haven't specifically found the code to mail merge and convert to PDF so I'll keep digging unless perhaps there is another open source program that might be more suited for what I'm trying to do. Thanks,

[robleyd]

https://www.pitonyak.org/oo.php is a highly recommended source of information about OpenOffice macros.

[Jurassic Pork]

Hello,
you can try this :
1 - put your csv files in a folder ( ex : csv)
2 - create a new base database (.odb) (ex CsvFiles.odb) connect to an existing database (select Text/Csv)
and choose the folder where the csv files are (Path to text files) and select the options for the csv.
In the tables window you must have your csv files as tables. Then register your odb file.
3 - In your Odt file, display database source, you can see the CsvFiles Database. Drag and drop the fields (drag from
column header) in your odt text.

MailMergeWriter.png (46.78 KiB) Viewed 1051 times

4 - Then with this code :

Code: Select all

REM  *****  BASIC  *****
Sub Main
    Dim oPub As Object
	oPub = createUnoService("com.sun.star.text.MailMerge")
	with oPub
		.OutputType = com.sun.star.text.MailMergeType.FILE
		.DataSourceName = "CsvFiles"
		.CommandType = 0
		.Command = "relstarter"
		.DocumentURL = ThisComponent.getURL()
        .SaveFilter = "writer_pdf_Export"
        .FileNamePrefix = "Export"
		.SaveAsSingleFile = True
		.execute(Array())
	end with
End Sub

you can mailmerge the table relstarter from CsvFiles database . The result is a pdf -> export0.pdf

in attachment a zip file with all the files

Friendly, J.P

[mbj42938]

Hello - thank you for the sample code. I'll try it and let you know if I have any questions. In general, I'll only have one doc file (the template) and one CSV file (which will only be one record to generate a one page form). Thanks

[mbj42938]

I'm about to start testing this code but I have a question. I noticed that my code for integration with Microsoft Word will not work on certain servers depending on the OS when I run my back end web server as a service. It works on some operating systems but not others. So, I'm wondering if I'm even using the right tool for the job or if there is a DLL or something else better for the job. I'm wondering if Open Office might have the same problems with automation when a service is trying to automate it. Thanks for your feedback on this.

[mbj42938]

Hello - I tried to work with your code and convert to VFP. I've commented out the code I'm stuck on (code I'm stuck on starts with an asterisk). I appreciate any feedback you can assist with:

Code: Select all

loOO = CreateObject("com.sun.star.ServiceManager")
loDesktop = loOO.createInstance("com.sun.star.frame.Desktop")
loMM = loOO.createInstance("com.sun.star.text.MailMerge")
with loMM
*	.OutputType = com.sun.star.text.MailMergeType.FILE

	.DataSourceName = m.lcFileCSV1
	.CommandType = 0
	.Command = "m.lcFileCSV2"

*	.DocumentURL = ThisComponent.getURL()

	lomm.SaveFilter = "writer_pdf_Export"
	loMM.FileNamePrefix = "Export"
	loMM.SaveAsSingleFile = .t.

* the line below it doesn't know what array() is in VFP
*	loMM.execute(Array())

endwith

[JeJe]

If you're saying this gives an error

Code: Select all

.OutputType = com.sun.star.text.MailMergeType.FILE

then have you tried the constant instead .OutputType =2

I don't know VFP but Array() creates and passes an empty array in Basic - have you tried defining an empty array in VFP and then passing that?

Have you seen this page?

https://www.tek-tips.com/viewthread.cfm?qid=1514000

[mbj42938]

I'll try that. Thank you