Ask Your Question
1

How to print a base report and send it by mail (mailmerge)

asked 2020-08-22 21:55:41 +0200

mate89 gravatar image

updated 2020-10-07 11:48:30 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete

Comments

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.

Ratslinger gravatar imageRatslinger ( 2020-08-23 00:43:22 +0200 )edit

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?

frofa gravatar imagefrofa ( 2020-08-24 08:14:41 +0200 )edit

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

mate89 gravatar imagemate89 ( 2020-08-24 09:31:39 +0200 )edit

@mate89,

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?

Ratslinger gravatar imageRatslinger ( 2020-08-24 18:55:25 +0200 )edit

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 Sub
mate89 gravatar imagemate89 ( 2020-08-25 18:05:27 +0200 )edit

@mate89,

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

Ratslinger gravatar imageRatslinger ( 2020-08-25 23:20:49 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-08-26 05:01:29 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-08-24 20:17:53 +0200

Ratslinger gravatar image

updated 2020-08-27 04:27:23 +0200

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

edit flag offensive delete link more

Comments

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.

mate89 gravatar imagemate89 ( 2020-08-26 21:21:03 +0200 )edit

@mate89,

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.

Ratslinger gravatar imageRatslinger ( 2020-08-26 21:33:23 +0200 )edit

@mate89,

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

Ratslinger gravatar imageRatslinger ( 2020-08-27 04:14:44 +0200 )edit

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.

mate89 gravatar imagemate89 ( 2020-08-27 09:38:09 +0200 )edit

@mate89,

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.

Ratslinger gravatar imageRatslinger ( 2020-08-27 18:36:50 +0200 )edit

Yes okay "filter" should be in lower case. That solved the Problem. Thanks!

mate89 gravatar imagemate89 ( 2020-08-27 22:49:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-22 21:55:41 +0200

Seen: 209 times

Last updated: Aug 27 '20