[Solved] Emailing a group

Creating and using forms
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

[Solved] Emailing a group

Post by Nocton »

I have an email button on a form which picks up the email address from a field on the form and opens the email client. The code I am using is adapted from the thread: http://user.services.openoffice.org/en/ ... 92&p=14885 and is given below.

All works well, but I should now like to add a 'group' email button which will send the email to a group/list of email addresses. So I want to:
1. SQL select the email addresses from the main table for those belong to group 'XXX'
2. In the line 'MailMessage.setRecipient(mTo)' below, I want mTo to be the list of email addresses selected.
Does anyone have any suggestions how best to do this?

Regards
Nocton

Code: Select all

Sub openEmailClient(Event As Object)
On Error Goto HandleError
' code bound to 'Mouse Button Pressed' of a Text Box
    Dim MailClient, MailAgent,MailMessage As Object
   	Dim frm, mTo As String
   	Dim UI As Integer
 	frm=ThisComponent.Drawpage.Forms.getByName("MembersForm") 
	mTo = frm.getByName("txtEmailAddress").currentvalue
   If mTo="" Then
      Exit Sub 'NO EMAIL ADDRESS   
   End If
    MailAgent=CreateUnoService("com.sun.star.system.SimpleSystemMail") 
     MailClient=MailAgent.querySimpleMailClient()
     MailMessage=MailClient.createSimpleMailMessage()
   
     MailMessage.setRecipient(mTo)
     UI=0
' the UI flag indicates if the mail client user interface is to be opened (0) or sent
' w/o opening (1).  If you select 1, your email client may open a confirmation box
' indicating someone is trying to send an email--sometimes it is not the top most window.
' if nothing seems to happen, your email client may be waiting for you to respond you can
' set your email client to always allow an application to send emails - but may be a security issue
     MailClient.sendSimpleMailMessage(MailMessage, UI)
HandleError:
  If err<>0 Then
    Exit Sub
  End If     
End Sub
Last edited by Nocton on Mon Aug 15, 2011 1:02 pm, edited 1 time in total.
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Emailing a group

Post by RPG »

Hello

I think you have to use a loop. For selecting records you have to use the form builtin filter

Code: Select all

Sub MakeSum(oEvent as object)
' Must walk trough table
dim oButton
dim oForm
oButton=oEvent.source.model
oForm=oButton.parent
oForm.beforefirst 
do while oForm.next
wait 500
loop
End Sub
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Emailing a group

Post by QuazzieEvil »

That approach uses a XSimpleMailMessage, which does not support multiple recipients. You will need to loop over the sender for every indented recipient. Something like:

Code: Select all

Sql="SELECT Email FROM USERS WHERE GROUP=1"
rstUsers=Stmt.executeQuery(Sql) 'ASSUME Stmt is a defined statement service
while rstUsers.next()
  sendMail(mSubject,mBody,rstUsers.getString(1)) 
Wend
P.S.: From what I remember, this service does not let you specify a mail body, just an attachment.

Otherwise, try python mailmerge.py: http://documentation.openoffice.org/HOW ... lmerge.pdf I have not used it in several years, so I do not know how well it works now. This python module is usually shipped with OOo
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Emailing a group

Post by Nocton »

Thank you RPG and QuazzieEvil. I think I'll go with QuazzieEvil's approach, but you say that XSimpleMailMessage does not support multiple recipients, which is a pity as I was hoping to use a loop in some way as RPG suggests. This has left me confused as the code given looks too simple.

So in the line:

Code: Select all

rstUsers=Stmt.executeQuery(Sql)

what should Stmt be/how does it become 'a defined statement service'
and how should rstUsers be defined - in a Dim statement?

In the line:

Code: Select all

sendMail(mSubject,mBody,rstUsers.getString(1)) 
is 'sendMail' a command that replaces 'MailClient.sendSimpleMailMessage' in the previous code from you that I used?

Sorry to be asking for more help, but I am not clear what your code is doing.

I had a quick look at the python mailmerge.py alternative suggested, but it looks more complicated as each PC/user has to be set up with the system and perhaps more appropriate for more manual mail merges.
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Emailing a group

Post by RPG »

Hello

I think there is not much different between my solution and of QuazzieEvil. I think you can do the selection in the form filters. With the solution of QuazzieEvil you have to make a resultset. You can find all tutorials in the sign of QuazzieEvil.

Explanation
When you use the form search builtin then you can select the group or person who you want send an email. Then you can use even one button no more. The result set of a form is the same as you see in your form. You can easy switch between the selected group and the complete resultset.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Emailing a group

Post by Villeroy »

Don't know if it helps but I found out that I can trigger an URL button with a comma separated list of mail addresses like:

mailto:carl@host.org,steven@host.org,mathew@host.com
Tested with Thunderbird as mail client.

So you can simply join the mail addresses of a (invisble) subform which reflects the stored mail addresses for the main form's selected group, add the "mailto:" prefix and assign that URL to a mail button.
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Emailing a group

Post by Nocton »

Thank you, Villeroy. I don't think I can use 'mailto' within my basic code attached to a Base form can I? But prompted by your suggestion, I now find that if I put commas between the email addresses, as in your example, then my original code lines:

Code: Select all

     MailAgent=CreateUnoService("com.sun.star.system.SimpleSystemMail")
     MailClient=MailAgent.querySimpleMailClient()
     MailMessage=MailClient.createSimpleMailMessage()
     MailMessage.setRecipient(mTo)
     MailClient.sendSimpleMailMessage(MailMessage, UI)
work fine with mTo now set to the string of email addresses, separated by commas. This is contrary to what QuazzieEvil said - "That approach uses a XSimpleMailMessage, which does not support multiple recipients." - but is a nice solution because I can use the same code.

So it seems that all I have to do is select the required email addresses from the table, adding a comma between each one when constructing the string. I am not quite sure how to do this yet, but will look at it next week.

Regards, Nocton
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Emailing a group

Post by RPG »

Hello

Villeroy give me the idea to search for the protocol mailto
I did found two links
http://msdn.microsoft.com/en-us/library ... 85%29.aspx
http://www.ietf.org/rfc/rfc2368.txt

This give you can do more with the idea of Villeroy

I found one limit there. It seems you cannot use BCC. As I understand in the second link it is not permitted
I think it is not a good idea to send a great lot of emails and every one can see all the adresses.

When I search in the API I found this
http://api.openoffice.org/docs/common/r ... cRecipient

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Emailing a group

Post by Villeroy »

Nocton wrote:Thank you, Villeroy. I don't think I can use 'mailto' within my basic code attached to a Base form can I?
You referred and linked to my tiny macro solution which reads one mail address from a field and assigns a "mailto:address@field.value" to a button. You said that this works well for you.

All I suggest is the exact same solution with a special URL that sends a new mail to a comma-separated list of receipients.
I tested this special URL with a text hyperlink in Writer and Thunderbird as mail client.
mailto:user@domain.com,boy@test.org,girl@eval.de [copy this into OOo menu:Insert>Hyperlink...]
When I click such a hyperlink I get a new Thunderbird mail addressed to each receipients seperately (not in CC nor BCC).

All you've got to do is adding a subform with all the related mail addresses plus a little bit of extra code to concatenate the subform values with commas and slam the resulting URL into the send button just like I used to do in that other macro. All the SMPT is done automatically by the system software.
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Emailing a group

Post by Nocton »

Thank you for this further guidance, Romke.
I agree with you about the desirability of using bcc when emailing many addressees.
So, following the API link and the list of parameters and commands given, I tried replacing my
MailMessage.setRecipient(mTo) line with
MailMessage.setBccRecipient(mTo)

but it produced the error 'Object variable not set'.
Any ideas why? The code seems identical to that in thread http://www.oooforum.org/forum/viewtopic.phtml?t=73442
OpenOffice 4.1.12 on Windows 10
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Emailing a group

Post by Nocton »

Thank you,Villeroy. In fact I have been using the basic code as given (from QuazzieEvil originally I think) which uses SimpleSystemMail. However, MailTo: may also be attractive, except for the limitation mentioned by Romke that one cannot use bcc.
I am quite familiar with a button hyperlinked to the MailTo: string, but I am not sure how to set the URL string using code.Thus if I had code as:

Code: Select all

dim frm,oButton
frm=ThisComponent.Drawpage.Forms.getByName("MainForm") 
oButton=frm.getByName("EmailButton1") 
oButton.URL="mailto: fred@abc1.com"
what should the last line be?
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Emailing a group

Post by Villeroy »

See attachment.
Main form's record change event does not work here.
The macro is called when the sub-form gets loaded and on reload.
Attachments
mailconcat.odb
send multiple mails
(16.01 KiB) Downloaded 553 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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Emailing a group

Post by Nocton »

Thanks for that useful example. But we still can't get bcc with MailTo: - or can we?
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Emailing a group

Post by Villeroy »

Nocton wrote:Thanks for that useful example. But we still can't get bcc with MailTo: - or can we?
May be impossible or not. I would ask in a Thunderbird forum how to fabricate such a mailto-URL.

You can also use method addBccRecipient from http://api.openoffice.org/docs/common/r ... ssage.html to add the contents of the email fields.
I demonstrated how to get an array with all the mail addresses which may be the main problem in this context.
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: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Emailing a group

Post by Villeroy »

Using the array a() from my Main macro, this snippet creates a message to receipient a(0) and the others in the BCC.

Code: Select all

	msg = createUnoService("com.sun.star.mail.MailMessage")
	msg.addRecipient(a(0))
	for i = 1 to uBound(a())
		msg.addBccRecipient(a(i))
	next
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Emailing a group

Post by Nocton »

Thank you Villeroy for your help and especially the example in mailconcat.odb. As my form already has a grid/table control with List Box to select members of each group from the members table, all I needed to do was to select the email address too into the table control and use your code - which I could never have devised myself, especially your 'getFieldStrings' and 'bas_PushArray' routines.

I have found the solution to getting bcc with MailTo:. One just adds ?bcc= so that my code to set the URL becomes:

Code: Select all

oBtn.TargetURL = "mailto:"& "ToEmail@address.com?bcc=" & s & "?subject=" & cSubject & " Group"
where I set the subject header too.

Using MailTo: seems a much simpler procedure than the com.sun.star.system.SimpleSystemMail method I have been using, as in the code first given. However, I still do not know why, as mentioned in an earlier post, adding the line
MailMessage.setBccRecipient('bcc emailaddresses') as given at:
http://api.openoffice.org/docs/common/r ... odsSummary does not work

I shall set the thread as [Solved] now.

Thank you to all who helped find a workable solution, Nocton
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Emailing a group

Post by Villeroy »

Wow! Well done.
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
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Emailing a group

Post by QuazzieEvil »

Nocton wrote: work fine with mTo now set to the string of email addresses, separated by commas. This is contrary to what QuazzieEvil said - "That approach uses a XSimpleMailMessage, which does not support multiple recipients." - but is a nice solution because I can use the same code.
Learned something new today. I saw the lack of 'addRecipient(...)' and gave up. Never occurred to me to try a comma delimited string--obvious in retrospect.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] Emailing a group

Post by RPG »

Hello

I did try the solution of Norton for the button. It did not work for me in combination with Thunderbird. In earlier versions I have read there are bugs with this.

I have tried to find a solution with the information given by Villeroy, Norton and QuazzieEvil. I found the next solution

Code: Select all

Sub openEmailClient(Event As Object)
	Dim MailClient, MailAgent,MailMessage As Object
	Dim oForm,oGrid, mTo
	dim sBCCRecipients
	oForm=ThisComponent.Drawpage.Forms.getByName("UpdateGridForm")  
	oGrid=oForm.getbyname("TableToChange")
	mTo = oGrid.getByName("Email")
    MailAgent=CreateUnoService("com.sun.star.system.SimpleCommandMail")
    MailClient=MailAgent.querySimpleMailClient
    MailMessage=MailClient.createSimpleMailMessage

	oForm.beforefirst 
	do while oForm.next
		sBCCRecipients= sBCCRecipients & mTo.text & ";"
	loop
	oForm.first ' We need this for pointing to the first
	' Thre different lines.
	' look special for the difference between the first line and the two other
	sBCCRecipients=left(sBCCRecipients,len(sBCCRecipients)-1)
    'MailMessage.setRecipient(sBCCRecipients) 
	'MailMessage.setCcRecipient(array(sBCCRecipients,";"))
	MailMessage.setBCcRecipient(array(sBCCRecipients,";"))
	MailAgent.sendSimpleMailMessage(MailMessage,false)
    'true for sending without asking
End Sub
I have not test this for a great number of post.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Emailing a group

Post by Nocton »

Thank you, Romke, for this alternative solution. It may be useful for me in other applications.

However, I am using also Thunderbird (3.1.11) and Mailto. It works fine for me.
Curiously, when making the final implementation and distribution of the new version of the database, I found that the Mailto code given above, which worked perfectly well under test, failed to show the subject line, but instead concatenated it with the last email address. After a very frustrating time, when I was inclined to drop the subject - in both senses! - I found that the revised code below works OK.

Code: Select all

    oBtn.TargetURL = "mailto:"& "ToEmail@address.com?bcc=" & s & "&subject=" & cSubject & " Group"
that is with the second ? replaced with &

Nocton
OpenOffice 4.1.12 on Windows 10
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Emailing a group

Post by dreamquartz »

Villeroy wrote:See attachment.
Main form's record change event does not work here.
The macro is called when the sub-form gets loaded and on reload.
Hi Villeroy,

Kind of an old thread, but need to ask you for further assistance.
I tried to manipulate your example, but cannot find the right answer. I am looking for separate emails, instead of one email to many.
I have to add a separate attachment to all, and that is where I am looking for a solution, other than the one you suggested back then.

Do you by any chance have info about what type of coding or where to find info I have to use for creating something like this.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] Emailing a group

Post by RPG »

Hello

When I understand the question of dreamquartz correct then he want send an email to different persons. Each person does get two different attachments. I think it is better to use the SimpleCommandMail service. Maybe better is use your own database for making a list of the data you need and use that list for a script in the email client on your computer.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Emailing a group

Post by dreamquartz »

RPG wrote:Hello

When I understand the question of dreamquartz correct then he want send an email to different persons. Each person does get two different attachments. I think it is better to use the SimpleCommandMail service. Maybe better is use your own database for making a list of the data you need and use that list for a script in the email client on your computer.

Romke
Hi Romke,

It is a group of people, so there is a relationship, and the problem is that everyone needs to get a separate attachment.
I know how to create a group email, but no separate emails.

I am hoping to find a solution that creates a separate email for all to be send via Thunderbird (that is what we're using).
The "," (= Comma) separation between individual email addresses leads to a solution where all are receiving the same email.
It would be nice that some kind of method like a specific separation character between individual email addresses would lead to separate/individual emails.

If a different route needs to be chosen, ie a CVS-file export or something, that will be fine too, but then still how to create a bulk of separate/individual emails?
For a solution I would also like to stay within the database, rather than incorporate a "third party" method.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] Emailing a group

Post by RPG »

Hello

There seems to be addons for thunderbird. I have never used it.

https://addons.mozilla.org/nl/thunderbi ... ail-merge/

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Emailing a group

Post by dreamquartz »

RPG wrote:Hello

There seems to be addons for thunderbird. I have never used it.

https://addons.mozilla.org/nl/thunderbi ... ail-merge/

Romke
I was looking into this one already. At this point the Add-on is showing an error, I do not quite understand. Am in contact with developer.

It is an principle approach that is quite nice. The Add-on creates a template and seem to be continuing from there, but due to an error it stops there.
The combination Ubuntu 16.04, Thunderbird 45.8.0, and IMAP setup, seem having some issues with each other. Will try to check under Windows 7 PRO to see what is happening there, later.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Emailing a group

Post by dreamquartz »

Am hoping to find how to send a group of people separate emails still, other than https://addons.mozilla.org/nl/thunderbi ... ail-merge/.
If a mailing/group-list can be created, it should be simple to send a separate email.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] Emailing a group

Post by RPG »

Hello

Is it possible to send all information with a commandline instruction to Thunderbird. Possible it must be a command for each mail.

I think also this is a little off topic in this thread continue in the old thread

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Emailing a group

Post by dreamquartz »

RPG wrote:Hello

Is it possible to send all information with a commandline instruction to Thunderbird. Possible it must be a command for each mail.

I think also this is a little off topic in this thread continue in the old thread

Romke
I agree.
Thanks for the tip.
Will start a new one.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply