I create invoices using a base database report. Is it possible to save the individual pages created by the report as files or, ideally, to send them straight away by email?
Can this also be achieved with a macro?
In Writer it can be done with mailmerge but how to do this with a base report?
Thank you for your answers.
Hello,
I believe this can be done but requires macro coding. This is specific to your Base file and database. What specific LO version are you using? What database? Do you have knowledge to write Base macros?
Can provide some direction as to piecing some items together but there is no copy/paste solution.
Just wondering if you could simply use the MAIL MERGE wizard to make a report TEMPLATE? (Maybe your reports are too complex for that approach). I have had success using the report generator to make all required INVOICES as a set of individual PDF files and then email them as attachments to individual emails, but I guess it’s not a very efficient method if you have a lot of invoices in each send-out batch. Question: I have not used the MAIL MERGE wizard to send out batches of emails - does it even work?
Hi, my LO Version: 6.3.4.2 (x64) Database Postgres. I don’t have much experience in macro programming, but I programmed the invoice generation with one invoice item via WriterMailMerge with a macro, which was relatively simple. But when i have more than only one invoice item then I have to use this base report.
and then email them as attachments to individual emails
You email them with a macro or just by hand?
Best regards
Have put together a process to save individual invoices to PDF files & then fire off as an attachment in a email. The process is fully automated except that each email needs the Send button clicked. Using SimpleCommandMail, the SimpleMailClientFlags.NO_USER_INTERFACE does not work (have seen no Bug report on this either). This is all tested on Ubuntu 20.04 and may be different on other OS’s.
What OS are you using and does the macro you are using wait for you to send? If no pause, can you post code?
Thanks for your instructions. When you use mailmerge in writer then the mail will be send immediately after you run the macro, there is no dialog. But with writer mailmerge I could only generate invoices with only one position so I needed another solution. OS: Windows 10, but it should work with Ubuntu also.
Code:
REM ***** BASIC *****
Sub Main
DIM oMailMerge AS OBJECT
DIM aProps() as object
oMailMerge = createunoservice("com.sun.star.text.MailMerge")
oMailMerge.DataSourceName = "******"
oMailMerge.DocumentURL = ConvertToUrl("C:\inv.odt")
oMailMerge.CommandType = 1
oMailMerge.Command = "sql_query"
oMailMerge.OutputType = 3
oMailMerge.Subject = "Invoice"
oMailMerge.SendAsAttachment = True
oMailMerge.SendAsHTML = False
oMailMerge.AttachmentName = "Inv.pdf"
oMailMerge.AttachmentFilter = "writer_pdf_Export"
oMailMerge.MailBody ="TEXT"
oMailMerge.AddressFromColumn = "mail_buyer"
oMailMerge.BlindCopiesTo= Array("test@test.de")
oMailMerge.execute( aProps())
End SubThank you for the information. This is not working for me in Ubuntu. Can send email but this process (tried many variations) produces an error:
BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.Exception
Message: Mail merge failed. Sorry, no further information available./
home/buildslave/source/libo-core/sw/source/uibase/uno/-
unomailmerge.cxx:805
Will continue to look into and if any progress, will modify existing answer. Unfortunately, at least using in Ubuntu, I have no other answer than what is already provided.
OK, got Mail Merge routine from @mate89 working. In process of incorporating this with what I have already presented & then some further testing. Will then update answer with changes.
Hello,
As noted in my comment there is an issue - dialog waiting to send each item. Another problem, when sent, the item was in the Thunderbird sent folder twice but only one email was actually sent to the recipient. Regardless, will post sample.
This sample was created with some modification to existing Base field and code. It was tested on Ubuntu 20.04 with LO v7.0.0.3 using Thunderbird mail. Evolution was also tested and worked but because of my unfamiliarity with Evolution, there were multiple logins required per email.
Please note, LO documentation and Base samples referenced can be found here → LibreOffice Base Guide
From the Base samples (documentation page above) I used the Example_Report_Bill. There was an existing macro in Module1, so all macros referred to here were placed in Module2.
Modified the customer table to add a varchar field named email. This has fake email addresses and they need to be changed to actual addresses for testing purposes.
Copied query sale_calculated and named as sale_calculated_individual. The original is read into the macro, modified, and written back under the copied name. It selects individual items to be printed and is used in the report.
Report bill was copied as bill_individual and modified to use sale_calculated_individual query. Did not modify bill report simply to keep thing as clear as possible.
LO documentation has a couple of macros. See Chapter 9 - Macros. Launching, formatting, directly printing and closing a report (about page 68). There are three routines:
-
ReportStart
-
ReportLineHeightAuto
-
ReportPDFstore
Comments were placed in the macro as items were added to read the items to be printed and loop through the code to process only one item at at time - generate the report, save as PDF (unique name with current date) and attach to email and send.
This last routine, email, has two other versions found (not used in this process but included to potential testing). Have not been able to avoid the human interface to click the send button. All macros noted as to where they originated from (URL or document).
To get the routine to work a few changes need to be made. As noted above, the customer records email fields needs valid addresses to actually send.
In the macros, ReportPDFstore needs location to store PDF’s. That location is also needed in subSimpleMailWithPDF. Also in subSimpleMailWithPDF you need to set Originator email.
Sample ----- Email_Example_Report_Bill.odb
Edit 2020-08-26:
Have modified above sample to use MailMerge routine from @mate89.
Now only three routines:
-
mmReportStart
-
mmReportPDFstore
-
mmMailMergeWithPDF
The last two need to be modified with your directory information. This database needs to be registered and that registered name is entered in the last routine.
There is a processing period between documents. This process saves the Writer file (.odt) and the MailMerge sub actually does the conversion.
This process does have a problem - at least on Ubuntu 20.04 with Thunderbird. There is nowhere in the MailMerge facility to enter a From address. Without this, mail going to places like gmail will be returned. Don’t have any work around.
Situation fixed - see comments. All working OK.
Replaced sample 2020-08-26 PM because reports were left open. They were only set with visible = false. See comment for further detail.
New Sample ---- Email_Example_Report_Bill_Mail_Merge.odb
Thank you very much, looks very good.
Because of your problem: When I use mailmerge I first have to go to libre office settings → libreoffice writer → serial email/mailmerge (in german Serien-Email) and there I have to put in my personal mail address login information. Libre Office uses this mail account to send the mails generated by mailmerge. So there is no need to have thunderbird or another mail program.
Yes that did fix the problem. Had originally and must have removed. Did put password there as that was my original problem.
Other processes such as simple mail do use the email program.
Adding:
Rem Reports were set to NOT visible. At this point it is no longer needed.
oReport.close(1)
just before:
mmMailMergeWithPDF(sInvoice, sID2)
in mmReportPDFstore. Otherwise they are left open.
Will modify second sample to reflect this
When execute the macros (press button formular) I get an error:
Bill_Customer->
Type: com.sun.star.sdbc.SQLException
Message: Table not found in statement [UPDATE “Filter”]
MailMergeSend->
Cannot open document “bill_individual”
Is there anything wrong? I registered the database in the libreoffice settings and adjust the filepath.
I should have made it more clear. As stated, this is a sample from the Base samples. There was an existing form already → bill_customer. This is not part of the process in the answer. The form to use is MailMergeSend. It is a simple form with just a button to start the process.
Edit:
Have also found the error in the code from the sample. The Update statement in the macro uses the table name of Filter when it should actually be filter.
Again, this process is not what I dealt with in the answer. Just left it as copied from the samples.
Yes okay “filter” should be in lower case. That solved the Problem. Thanks!