Page 1 of 1
[Solved] Automate Mail Merge and PDF creation
Posted: Sat Apr 06, 2024 1:33 am
by 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?
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Sat Apr 06, 2024 11:58 am
by 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.
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Sat Apr 06, 2024 9:05 pm
by 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.
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Sat Apr 06, 2024 10:05 pm
by 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.)
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Sun Apr 07, 2024 1:45 am
by 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,
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Sun Apr 07, 2024 2:32 am
by robleyd
https://www.pitonyak.org/oo.php is a highly recommended source of information about OpenOffice macros.
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Sun Apr 07, 2024 3:26 pm
by 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 1162 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
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Tue Apr 09, 2024 2:30 am
by 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
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Tue Apr 09, 2024 8:40 pm
by 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.
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Wed Apr 10, 2024 10:04 am
by 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
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Wed Apr 10, 2024 11:56 am
by 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
Re: OpenOffice Automation for MailMerge and PDF Creation
Posted: Wed Apr 10, 2024 7:57 pm
by mbj42938
I'll try that. Thank you