Mail merge to separate documents with file names based on merge fields

[Gillian1]

I want to create a mail merge of customer invoices that will save the file to separate documents. I understand that this is possible in LibreOffice Writer but that it creates its own file names based on text I provide followed by a numerical sequence number. What I would like to do is have the file names based on a combination of two fields - the invoice number and the customer name. If that isn’t possible, then just the invoice number. Has anyone written an addon to do this?

Thank you.

In response to w_whalley’s answer below:

Thank you w_whalley for your answer. It sounds great, but I am struggling to get far enough down the mail merge process to find out if it works. Following your answer I downloaded the latest version of Writer (3.6) and I started by the merge document and then using the Tools > Mail Merge Wizard (the only option I could find to do a mail merge), inserted the fields I need in the appropriate places. I then attempted to filter my database but when I click on the [Filter] box it turns a blue colour but nothing happens. No dialogue box opens.

I then looked at the user guide and see that there’s a manual way to do a mail merge and that I should reach the data source by clicking View > Data sources but unfortunately ‘Data sources’ does not appear in the View menu. Hitting the [F4] key doesn’t do anything either.

I tried a repair of the installation (I had only installed Writer and Calc plus all the extras except additional languages) but that made no difference.

I am using a PC running Windows Vista and any suggestions as to what I’m missing - some kind of extension perhaps - would be gratefully received.

[Gillian1]

Not sure how to add a comment to an answer, but Thank you w_whalley for your answer. I have added to my original above as I am struggling to get the mail merge working. I seem to be missing features.

[w_whalley]

Sorry, I had assumed you had installed LibreOffice Base and that you had an embedded database containing your fields. What is the source of your invoice number and customer name? You may want to review a mail merge tutorial before proceeding. There is a link on this page to a Writer document on using mail merge, https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Writer_Guide

[Gillian1]

Getting there slowly: My datasource is an MS Excel spreadsheet. Without Base installed the View > Data Sources & File > Wizards > Address Data Source don’t appear in the menu options in Writer. The Writer user guide should be updated to say you have to have Base installed in order to ‘see’ other data sources to register them, even if they are not created in Base. Have now saved spreadsheet as a Calc file. Fig 311 in the guide shows the ‘Records’ and ‘Output’ but this is not displayed for me.

[w_whalley]

I would forget the wizard for now. Now that your data is in a Calc file, you need to make that Calc file the data source of a Base database. Start Base and choose connect to an existing database with the type spreadsheet. Browse to your Calc spreadsheet, and select it. Register the database and save it with a distinctive name. Now create a new Writer doc and View/Data Sources. You should see your Calc sheet in the upper left data source window. In that window navigate to the Table and sheet.

[Gillian1]

Hi. Yes, I’ve already got that far, and can see the data sheet. What I’m not seeing is the bit underneath the datasheet view that has ‘Records’ on the left with radio buttons to select All, Selected Records, or from/to, and on the right ‘Output’ with radio buttons to select Printer or File.

[w_whalley]

That comes later when you print the mail merge document. In your text document with the Data Sources visible, click and drag the field names of the source spreadsheet view into your text document. This will insert fields. Save the text document, then print it. Answer the form letter question Yes. Now you should see the missing dialog.

[Gillian1]

Fantastic! The ‘File > Print’ instruction confused me as I expected to see a preview first. BTW, saving as MS 2007,2010 format doesn’t work - it saves field names (e.g. Invoices.Sheet1.Advertiser) instead of the data. MS2003 is ok. Finally, is it possible to add format to a field that onIy appears when there is a value to display? In this case it’s a discount off the invoice amount). In MS Word I click Alt F9 and add #(0.00), i.e. {MERGEFIELD “discount”#(0.00)} (backslash char dropped here)

[w_whalley]

If a field is empty (null) in the data, then it will just be missing in the mail merge document. If it is 0.00 in your spreadsheet, it will print as 0.00. You might add a column to the spreadsheet that is blank if the discount is 0.00 and use that column in your mail merge

[Gillian1]

I think, in that case, I will convert my spreadsheet data in that field to negative numbers and amend the display format of those cells in the spreadsheet to be bracketed for negative numbers. And adjust the formula for total, of course. Thank you for all your help over the last couple of days and for your patience.

[w_whalley]

This is possible. In your source table or query you need a field that combines invoice number and customer name. Likely this combination would be unique.

When you are ready to print the mail merge, choose the output as File, then Save as individual documents, Generate file name from database, and choose your combined invoice number/customer name as the field to use as the filename.

This will generate individual documents named with the field contents with a “0” appended. I don’t know how to suppress the trailing “0”, but you could do a bulk rename if it is objectionable.