Is there a way of getting embedded images from base into a mail merge or spreadsheet?

[OS1]

Hi, I’m working with a charity based in two countries. They need a membership database, with an image of the person, and a way of doing a mail merge with these images. As the database needs to be portable having directories full of images is not possible. Base allows images to be embedded, however exporting is proving impossible. They’d like to be able to do a mail merge, but failing that exporting to a spreadsheet and using this as the basis for a mail merge would also work.

Is there a way of doing a mail merge direct from Base, or somewhere else in LO which will include the images? I’ve tried registering the database, but it seems LO cannot register one of its own databases!

Is there a way of exporting to a spreadsheet that will include the images?

[Ratslinger]

Hello,

Mail merge does not work with images. As for other methods using images, have only seen forms work for images from a database.
.
Edit: there is an open request tdf#87195

[Wanderer]

Not a tested way to solve this, but base-reports can also carry images. But as I always refer to external pictures, I didn’t try embedded pictures.

As “somebody” already created a macro to create individual pdf and mail them it might be a possible (and not to long) route to solve this…

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

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.

[Wanderer]

@OS1

Is there a way of exporting to a spreadsheet

Is there a reason to ask for Calc here? Usually Mail-Merge is started with a Writer-Template and also a typical base-report is generated for writer.

[OS1]

Hi @Wanderer, no not really, just trying to think of a way to get non techie users a way of easily exporting the database and its images. Reading these answers the answer looks to be no!

[Ratslinger]

@Wanderer

as I always refer to external pictures, I didn’t try embedded pictures.

Did take a bit of time to try this. Used Firebird embedded DB (HSQLDB v1.8 embedded not recommended for this) and tried with both embedded and linked images. Code needed a bit of adjusting only for selection purposes. Report with images was sent correctly.
.
Did not recommend originally as it was not tested for this and being created for a charity (not the creator of the Base file) it may present issues if code need modification in future. Unfortunately, LO has this as an often requested open item for many years.

[RobertG]

Not without macro code, but this will give you a hint:

oField = oForm.getByName("GraphicalControl")
oField2 = oForm.getByName("FileName")
stName = oField2.Text
IF stName = "" THEN
   stName = "Imagefile"
END IF
oStream = oField.BoundField.getBinaryStream
oPath = createUnoService("com.sun.star.util.PathSettings")
stPath = oPath.Temp & "/" & stName
oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")
oSimpleFileAccess.writeFile(stPath, oStream)

Code is started form a form. There are two fields, one shows the internal image, the other shows the name of the file. With getBinaryStream the image is read from the database, with write file the image is written to the temporary folder.

Now the part to send this image:

DIM arAttach(0)
DIM arCC(0)
DIM arBCC(0)
arAttach(0) = stPath
IF GetGuiType() = 1 THEN
	oMailer = createUnoService("com.sun.star.system.SimpleSystemMail")
	' if not: Linux/Mac
ELSE
	oMailer = createUnoService("com.sun.star.system.SimpleCommandMail")
END IF
oMailProgram = oMailer.querySimpleMailClient()
oNewMessage = oMailProgram.createSimpleMailMessage()
oNewMessage.Recipient = stMail	
oNewMessage.CcRecipient = arCC()
oNewMessage.BccRecipient = arBCC()
oNewMessage.Subject = stSubject
oNewMessage.Body = stBody
oNewMessage.setAttachement(arAttach())
oMailProgram.sendSimpleMailMessage(oNewMessage, 0 )

If you put this together you will extract the file and set is as attachment to your local connected mail program. You have to define in Tools → Options → Internet → EMail your EMail program.

Will also be possible to insert this image into a Calc file, save this file to pdf and create the attachment for the mail this way …

[OS1]

@RobertG we don’t have the LibreOffice skills that you do. Also, having half the team in West Africa, it’s not going to be easy for me to develop the macro and then educate the field offices how to use it.

[RobertG]

Here is an example for reading the content of a binary field and display this content with an external viewer. Works like the first part of the macro without sending this as mail: External Files. Haven’t translated this to English.

If you need help for extracting the images from a database: Send a database without data content (forms and tables must be there) and I will insert the macro so you could export the inserted images. My private mail address you will get here: EMail Robert