How to configure MailMerge to use E-mail server?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
AndrewDavison2557
Posts: 46
Joined: Sun Oct 05, 2014 7:42 pm

How to configure MailMerge to use E-mail server?

Post by AndrewDavison2557 »

Dear All,

I'm having problems writing a mail merge program that produces
e-mail messages. I'm using the "com.sun.star.text.MailMerge" service,
coding in Java.

How can I tell MailMerge details about my mail server such as its
address, port, protocol (e.g. SSL), and my user name?

MailMerge does have a "OutServerPassword" property for
supplying the server's password, but nothing for the other
bits of information.

Some partial code (using some of my support classes):

Code: Select all

XJob job = Lo.createInstanceMCF(XJob.class, "com.sun.star.text.MailMerge");

XPropertySet props = Lo.qi(XPropertySet.class, job); 

props.setPropertyValue("DataSourceName", DATA_SOURCE_NAME);  
props.setPropertyValue("Command", TABLE_NAME);
props.setPropertyValue("CommandType", CommandType.TABLE); 

props.setPropertyValue("DocumentURL", FileIO.fnmToURL(TEMPLATE_FNM)); 
props.setPropertyValue("OutputType", MailMergeType.MAIL);
props.setPropertyValue("OutServerPassword", "XXXX");

props.setPropertyValue("AddressFromColumn", "E-mail");
props.setPropertyValue("Subject", "Hello from Andrew ");
props.setPropertyValue("MailBody", "Please read the attached message.");

props.setPropertyValue("SendAsAttachment", true);
props.setPropertyValue("AttachmentName", "GS-Thanks.pdf");
props.setPropertyValue("AttachmentFilter", "writer_pdf_Export");

job.execute(new NamedValue[0]); 
I don't think this is a problem with mailmerge.py since I've been able
to send e-mail using the com.sun.star.mail.MailServiceProvider service
directly.

A solution in any language would be much appreciated.
- Andrew
OpenOffice 4.1.1 on Windows 7
User avatar
RoryOF
Moderator
Posts: 34571
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to configure MailMerge to use E-mail server?

Post by RoryOF »

I'm beyond my knowledge on this, but found the following;

https://svn.apache.org/repos/asf/openof ... Merge.snip

I also found a note on an old MIT document which said that from OO 2 OO uses a python component in place of a Java component for mailmerge.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
AndrewDavison2557
Posts: 46
Joined: Sun Oct 05, 2014 7:42 pm

Re: How to configure MailMerge to use E-mail server?

Post by AndrewDavison2557 »

I've looked at that MailMerge snip, which has some good stuff in it, but sends mail to a printer, which isn't what I'm trying to do.

If I set the Mail Merge E-mail options in Tools > Options > Writer through the GUI, then my program works -- it sends mail merged e-mail. But I want to set those options inside the program not through Office's GUI.

- Andrew
OpenOffice 4.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to configure MailMerge to use E-mail server?

Post by Villeroy »

A snippet of pseudo-code copied out of a working Python script.
This one prepares a database connection and assigns this object to the ActiveConnection property of a MailMerge service http://www.openoffice.org/api/docs/comm ... Merge.html.

Code: Select all

    dba = ofc.createUnoService("com.sun.star.sdb.DatabaseContext")
    reg = dba.getByName("TestMM")
    uname = "DB User Name"
    pwd = "Top Secret"
    conn = reg.getConnection(uname, pwd)
[...]
        mm.DocumentURL = doc.getURL()
        mm = ofc.createUnoService("com.sun.star.text.MailMerge")
        mm.DataSourceName = "TestMM" 
        mm.CommandType = com.sun.star.sdb.CommandType.QUERY
        mm.Command = "qryMM"
        mm.ActiveConnection = conn
        mm.OutputType = com.sun.star.text.MailMergeType.MAIL
        mm.InServerPassword = "POP_password"
        mm.OutServerPassword = "SMTP_password"
        mm.Subject = "---Friendly ODF Spam---"
        mm.MailBody = "Right in your face"
        mm.AddressFromColumn = "DB_Email_Column"
        mm.SendAsAttachment = True
        mm.SendAsHTML = False
#            mm.BlindCopiesTo = <string sequence>
#            mm.CopiesTo = <string sequence>
#            mm.AttachmentFilter = <some export filter name for doc, pdf>

        mm.execute(())
Notice that passords are stored in clear text within registrymodifications.xcu
https://bugs.documentfoundation.org/sho ... i?id=96672
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndrewDavison2557
Posts: 46
Joined: Sun Oct 05, 2014 7:42 pm

Re: How to configure MailMerge to use E-mail server?

Post by AndrewDavison2557 »

Villeroy,

Isn't this code supplying a login and password for a database?

I'm after the login and password for the mail server, which is implicit in that code. It will default to using the values set in the Tools -- Option -- Writer -- Mail Merge E-mail dialog.

I've found the data in registrymodifications.xcu, and so I guess I could write some XML code to modify that file. Very hacky though.

I'm amazed that the password is in plain text!

- Andrew
OpenOffice 4.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to configure MailMerge to use E-mail server?

Post by Villeroy »

Code: Select all

    uname = "DB User Name"
    pwd = "Top Secret"
    conn = reg.getConnection(uname, pwd)
establishes a connection to some registered database document connected to a database server or a spreadsheet (spreadsheets can be encrypted). For many types of database connection (csv, dBase, embedded HSQL) you use empty strings as login.

Code: Select all

        mm.InServerPassword = "POP_password"
        mm.OutServerPassword = "SMTP_password"
passes 2 passwords to my mail merge service. The InServerPassword assumes that your server does https://en.wikipedia.org/wiki/POP_before_SMTP. Normally you only use the OutServerPassword.

When you follow the mail merge wizard on the email path, everything gets written to registrymodifications.xcu.
I can provide an excerpt of relevant mail merge via email nodes from that configuration file:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<oor:items xmlns:oor="http://openoffice.org/2001/registry" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailUserName" oor:op="fuse">
            <value>villeroy@provider.de</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="InServerPort" oor:op="fuse">
            <value>110</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="InServerPassword" oor:op="fuse">
            <value></value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard/DataSource">
        <prop oor:name="DataCommandType" oor:op="fuse">
            <value>0</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard/DataSource">
        <prop oor:name="DataSourceName" oor:op="fuse">
            <value>Adressen</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard/DataSource">
        <prop oor:name="DataTableName" oor:op="fuse">
            <value>MyMail</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailServer" oor:op="fuse">
            <value>securesmtp.provider.de</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="IsSecureConnection" oor:op="fuse">
            <value>true</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="Filter" oor:op="fuse">
            <value></value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailPassword" oor:op="fuse">
            <value>TOP SECRET</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="InServerName" oor:op="fuse">
            <value></value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="IsMailReplyTo" oor:op="fuse">
            <value>false</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailAddress" oor:op="fuse">
            <value>villeroy@provider.de</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="IsSMPTAfterPOP" oor:op="fuse">
            <value>false</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="IsAuthentication" oor:op="fuse">
            <value>true</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="InServerUserName" oor:op="fuse">
            <value></value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailPort" oor:op="fuse">
            <value>587</value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailReplyTo" oor:op="fuse">
            <value></value>
        </prop>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard/AddressDataAssignments">
        <node oor:name="_0" oor:op="replace">
            <prop oor:name="DBColumnAssignments" oor:op="fuse">
                <value>
                    <it></it><it></it><it>NAME</it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it>NAME</it>
                </value>
            </prop>
            <node oor:name="DataSource">
                <prop oor:name="DataCommandType" oor:op="fuse">
                    <value>0</value>
                </prop>
                <prop oor:name="DataSourceName" oor:op="fuse">
                    <value>Adressen</value>
                </prop>
                <prop oor:name="DataTableName" oor:op="fuse">
                    <value>MyMail</value>
                </prop>
            </node>
        </node>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard/AddressDataAssignments">
        <node oor:name="_1" oor:op="replace">
            <prop oor:name="DBColumnAssignments" oor:op="fuse">
                <value>
                    <it></it><it></it><it>NAME</it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it></it><it>NAME</it>
                </value>
            </prop>
            <node oor:name="DataSource">
                <prop oor:name="DataCommandType" oor:op="fuse">
                    <value>0</value>
                </prop>
                <prop oor:name="DataSourceName" oor:op="fuse">
                    <value>Adressen</value>
                </prop>
                <prop oor:name="DataTableName" oor:op="fuse">
                    <value>MyMail</value>
                </prop>
            </node>
        </node>
    </item>
    <item oor:path="/org.openoffice.Office.Writer/MailMergeWizard">
        <prop oor:name="MailDisplayName" oor:op="fuse">
            <value>Villeroy</value>
        </prop>
    </item>
</oor:items>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to configure MailMerge to use E-mail server?

Post by Villeroy »

I think, a simple way to reimplement this rubbish could utilize the built-in mail merge service and print to file(s) or to some dummy print device. During the process, the mail merge service creates one temporary document for each receipient. This file can be catched by a mail merge listener and you can attach it to your own email implementation.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to configure MailMerge to use E-mail server?

Post by Villeroy »

Save the attached document to a dedicated directory with macro permission.
It comes with a small toolbar and a few lines of StarBasic code embedded.

The minimalistic mail merge is set up to merge the shipped dBase table "Bibliography.biblio" into a single output document. Without further config, everything goes to the same directory as the source document with a numeric file name suffix for each subsequent output file.

To keep it simple, I stored a minimum of setup parameters in the document's custom properties container. You can easily adjust them to some table or query of your own database and exchange the mail merge fields accordingly.

I added a c.s.s.text.XMailMergeListener which catches each output document and saves it as an additional PDF copy named after the unique "Identifier" field. This is where your own Java mailer may come into play.
Attachments
merge_biblio.odt
StarBasic mail merge with custom properties and XMailMergeListener
(18.19 KiB) Downloaded 221 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply