How to send Email in Evolution mail using Calc Macro

Kenneth08 · Post by **Kenneth08** » Fri Aug 25, 2017 4:06 am

I am searching forums but unfortunately no suitable matches were found. I do not know if I am not good in searching. I am sorry for that.

I am new in Ubuntu OS and I want to send an email in Evolution mail using Libreoffice calc.
What I am working with is that when I click the "SEND" button, it will send a message automatically to specific recipient and the cell range will be body of the letter.

Hope that you can help me to finish my project.

Thanks to those person who have big heart

UPDATE!!!!!!!!!!!!!!!!!!!!

Hello all

I already found the working code.
My problem now is that the code is just creating a mail but not automatically send the email.
Below is the code, can you help me to revise the code to be able to add the auto-sending.

Code: Select all

Sub SendEmail
eMailAddress = "xxxxxx@xxxx.com"

   eSubject = "Test email"

   eMailer = createUnoService ("com.sun.star.system.SimpleCommandMail")
   eMailClient = eMailer.QuerySimpleMailClient()
   eMessage = eMailClient.createSimpleMailMessage()
   eMessage.Recipient = eMailAddress
   eMessage.Subject = eSubject
   AttachmentURL = convertToUrl("/home/dsm/Downloads/DAGREGISTRATIE SNEL SCHOON ZUID-OOST NIEUWE STIJL 2.0.ods")
   eMessage.Attachement = Array(AttachmentURL)
   eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )
End Sub

Post by **RusselB** » Fri Aug 25, 2017 4:36 am

Welcome to the Forum.
A few quick questions may help you resolve this problem.
1) Is Evolution mail an online mail provider, like Google or Yahoo? If so, then you can stop trying, as neither Open Office nor LibreOffice can be configured to send e-mail using a web based provider.
2) If the above is not true, then does Evolution work for other programs?
3) If it does, then you need to use the LibreOffice settings to configure it to match the settings that Evolution is using.
4) If it does not, then I would suggest reinstalling Evolution and then try steps 2 & 3 (above) again.

Kenneth08 · Post by **Kenneth08** » Fri Aug 25, 2017 4:39 am

RusselB wrote:Welcome to the Forum.
A few quick questions may help you resolve this problem.
1) Is Evolution mail an online mail provider, like Google or Yahoo? If so, then you can stop trying, as neither Open Office nor LibreOffice can be configured to send e-mail using a web based provider.
2) If the above is not true, then does Evolution work for other programs?
3) If it does, then you need to use the LibreOffice settings to configure it to match the settings that Evolution is using.
4) If it does not, then I would suggest reinstalling Evolution and then try steps 2 & 3 (above) again.

Hello RusseB

Thanks for your response and warm welcome.

Evolution is an application mail like Outlook.
What do you mean by "does Evolution work for other programs?"?

Post by **acknak** » Fri Aug 25, 2017 4:42 am

Greetings and welcome to the community forum!

Have you tried a "mailto" link?

If I type in a Calc cell mailto:somone@example.com and press Enter, Calc automatically makes it a clickable hyperlink. Clicking on the link opens my default email program with a new message addressed to somone@example.com.

If you need to do anything more complicated than that (say send the current sheet to someone), you can look at the File > Send ... menu choices.

Kenneth08 · Post by **Kenneth08** » Fri Aug 25, 2017 4:51 am

acknak wrote:Greetings and welcome to the community forum!

Have you tried a "mailto" link?

If I type in a Calc cell mailto:somone@example.com and press Enter, Calc automatically makes it a clickable hyperlink. Clicking on the link opens my default email program with a new message addressed to somone@example.com.

If you need to do anything more complicated than that (say send the current sheet to someone), you can look at the File > Send ... menu choices.

Hello acknak

Thanks for your kind answer.
Actually you are right that I need a little bit more complicated function.
What I am working with is that when I click the "SEND" button, it will send a message automatically to specific recipient and the cell range will be body of the letter.

Do you have any idea?

Post by **acknak** » Fri Aug 25, 2017 6:31 am

Sorry, no. I don't know how to do that—or if it's possible.

Post by **Zizi64** » Fri Aug 25, 2017 6:57 am

What I am working with is that when I click the "SEND" button, it will send a message automatically to specific recipient and the cell range will be body of the letter.

The predefined built -in features can send the file as attachment.
In my opinion you need a macro for your task.
You can put TEXTS into the mail body from the cells by these API commands:

Code: Select all

	eMailer = createUnoService( "com.sun.star.system.SimpleSystemMail" )
	     
		eMailClient = eMailer.querySimpleMailClient()     
		eMessage = eMailClient.createSimpleMailMessage()
     
		eMessage.setRecipient(eMailAddress) ' eMailAddress): a passed string variable with the mail address
		eMessage.setSubject(eSubject) ' eSubject): a passed string variable with subject

		eMessage.setAttachement (Array(AttachmentUrl)) 
' if you want attach one or more file/s/
'xray eMessage
		eMessage.body="Dear " + Recipient + "," + Chr(10) + Chr(13) +  Chr(10) + Chr(13) 
' Recipient: a passed string variable  with the name of the person
		eMessage.body= eMessage.body + "some constant text" + Chr(10) + Chr(13) + TheStrings_from_a_Cellrange
' TheStrings_from_a_Cellrange: a passed string (array) from the desired cellrange

	eMailClient.sendSimpleMailMessage( eMessage, 0 ) 
'If you want handle the Email client software manualy
'	eMailClient.sendSimpleMailMessage ( eMessage,com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE ) 
'Silent sending

It works with my LibreOffice and the ThunderBird mail client software.

edit this topic's initial post · Post by **Villeroy** » Fri Aug 25, 2017 8:59 pm

You must not use the calculator. Simply use the database component. Since you are using Ubuntu, you should have a working data source linked to your Evolution address book.
A macro to loop through any record set of any registered data source. The do..loop part is specific to the Bibliography demo. The way how to fetch a record set is the same for all types of databases. Use this snippet together with the mail code by Zizi64

Code: Select all

REM  *****  BASIC  *****

REM Connection parameters
Const cDB = "Bibliography" 'registered name
Const cSrcType = 0 '0=table, 1=query, 2=SQL
Const cSrc = "biblio" 'name of table, query or SQL
REM real databases require user name and password 
Const cUname = ""
Const cPwd = ""

Sub LoopRecordset
	mri = createUnoService("mytools.Mri")
	dbc = createUnoService("com.sun.star.sdb.DatabaseContext")
	dbSource = dbc.getByName(cDB)
	conn = dbSource.getConnection(cUname, cPwd)
	pc = conn.prepareCommand(cSrc, cSrcType)
	rs = pc.executeQuery()
	'mri.inspect(rs)
	if rs.first() then
		do
			REM column indices are 1-based
			id = rs.getString(1)
			auth = rs.getString(5)
			isbn = rs.getString(31)
			title = rs.getString(21)
			x = msgbox( _
				"Identifier: "& id _
				&Chr(10)&"Author: "& auth _
				&Chr(10)&"Title: "& title _
				&Chr(10)&"ISBN: "& isbn, 65, "macro:LoopRecordset")
			if x = 2 then exit sub
		loop while rs.next()
	else
		msgbox "Can't fetch first record.", 16, "macro:LoopRecordset"
		exit sub
	endif
End Sub

if you don't have a registered data source for your Evolution addresses in the data source window or in Tools>Options>Base>Databases...
File>New>Database...
Connect to existing
Type: Evolution
[X] Register the databsae
Save and close the database.

Notice that you can define queries to fetch only relevant rows (e.g. those with a mail address) and columns. Having such a query you set the cSrcType constant to 1 and cSrc to the query name. cDB is the registered name of your Evolution database. User name and password may be required if your Evolution-DB is served by LDAP.
The macro loops from row to row.
rs.getString(1) gets the first column's string of the current record.

Kenneth08 · Post by **Kenneth08** » Tue Aug 29, 2017 4:22 am

Zizi64 wrote:

What I am working with is that when I click the "SEND" button, it will send a message automatically to specific recipient and the cell range will be body of the letter.

The predefined built -in features can send the file as attachment.
In my opinion you need a macro for your task.
You can put TEXTS into the mail body from the cells by these API commands:

Code: Select all

	eMailer = createUnoService( "com.sun.star.system.SimpleSystemMail" )
	     
		eMailClient = eMailer.querySimpleMailClient()     
		eMessage = eMailClient.createSimpleMailMessage()
     
		eMessage.setRecipient(eMailAddress) ' eMailAddress): a passed string variable with the mail address
		eMessage.setSubject(eSubject) ' eSubject): a passed string variable with subject

		eMessage.setAttachement (Array(AttachmentUrl)) 
' if you want attach one or more file/s/
'xray eMessage
		eMessage.body="Dear " + Recipient + "," + Chr(10) + Chr(13) +  Chr(10) + Chr(13) 
' Recipient: a passed string variable  with the name of the person
		eMessage.body= eMessage.body + "some constant text" + Chr(10) + Chr(13) + TheStrings_from_a_Cellrange
' TheStrings_from_a_Cellrange: a passed string (array) from the desired cellrange

	eMailClient.sendSimpleMailMessage( eMessage, 0 ) 
'If you want handle the Email client software manualy
'	eMailClient.sendSimpleMailMessage ( eMessage,com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE ) 
'Silent sending

It works with my LibreOffice and the ThunderBird mail client software.

@Zizi64, Thanks to this, however, I encountered error at "eMailClient = eMailer.querySimpleMail.Client()" step.
Error Message is "BASIC runtime error. Object variable not set">

Sorry, I have knowledge in VBA but not in calc macro.

Post by **Zizi64** » Tue Aug 29, 2017 6:59 am

Have you tried the code with a real email client software like the Thunderbird?

Kenneth08 · Post by **Kenneth08** » Tue Aug 29, 2017 7:42 am

Zizi64 wrote:Have you tried the code with a real email client software like the Thunderbird?

@Zizi64, not yet. But unfortunately, I should use the Evolution mail as required on my project.

Kenneth08 · Post by **Kenneth08** » Tue Aug 29, 2017 9:28 am

Hello all

I already found the working code.
My problem now is that the code is just creating a mail but not automatically send the email.
Below is the code, can you help me to revise the code to be able to add the auto-sending.

Code: Select all

Sub SendEmail
eMailAddress = "xxxxxx@xxxx.com"

   eSubject = "Test email"

   eMailer = createUnoService ("com.sun.star.system.SimpleCommandMail")
   eMailClient = eMailer.QuerySimpleMailClient()
   eMessage = eMailClient.createSimpleMailMessage()
   eMessage.Recipient = eMailAddress
   eMessage.Subject = eSubject
   AttachmentURL = convertToUrl("/home/dsm/Downloads/DAGREGISTRATIE SNEL SCHOON ZUID-OOST NIEUWE STIJL 2.0.ods")
   eMessage.Attachement = Array(AttachmentURL)
   eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )
End Sub

Post by **Zizi64** » Tue Aug 29, 2017 9:49 am

viewtopic.php?f=13&t=67417

Code: Select all

 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
     MailAgent.sendSimpleMailMessage(MailMessage, UI)

Lupp · Post by **Lupp** » Tue Aug 29, 2017 11:19 am

This topic was also posted as https://ask.libreoffice.org/en/question ... alc-macro/ in another forum.
To the Original Questioner: If you post the same (or similar) question in more than one forum, please cross-link the posts. The reasons should be self-evident.

edit this topic's initial post · Post by **Villeroy** » Tue Aug 29, 2017 11:49 am

When I switch my Thunderbird client into offline mode it collects all outgoing mails in an outbox folder.

Kenneth08 · Post by **Kenneth08** » Tue Aug 29, 2017 12:56 pm

Lupp wrote:This topic was also posted as https://ask.libreoffice.org/en/question ... alc-macro/ in another forum.
To the Original Questioner: If you post the same (or similar) question in more than one forum, please cross-link the posts. The reasons should be self-evident.

@Lupp, NOted on this. Sorry for multiple posting.

Kenneth08 · Post by **Kenneth08** » Wed Aug 30, 2017 4:41 am

Zizi64 wrote:viewtopic.php?f=13&t=67417

Code: Select all

 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
     MailAgent.sendSimpleMailMessage(MailMessage, UI)

Thanks @Zizi64 for your patience of helping me. I tried above code and still not sending automatically.
I just appearing new message.

pharmankur · Post by **pharmankur** » Wed Feb 16, 2022 2:22 pm

I had similar issue where email generated from macro was creating a popup for user interaction while using setting of Linux Mint + Libreoffice + Evolution (and Thunderbird) where, any of following makes no difference in the Evolution / thunderbird behaviour

MailAgent.sendSimpleMailMessage(MailMessage, 0 ) OR MailAgent.sendSimpleMailMessage(MailMessage, 1 ) OR
eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )

.... All opens window where we have to manually click SEND button

------ I find a solution which works for Linux Mint + Libreoffice -----
As I understand normal email clients on Linux does not allow to send email silently thus we need to install terminal email client " mutt " , then modify libreoffice macro to access Linux SHELL and send email using Bash Shell and Mutt
Ensure you have installed mutt set your user login authentication in ~/.mutt/muttrc
-----------------------------------------------------------------

Code: Select all

REM  eMailClient.sendSimpleMailMessage ( eMessage, 0 )  ' Disabled As Its doesn't send silently in any setting anyways !
   
REM - Automatic Email without user interaction (silent email ONLY FROM LINUX USING MUTT)

Shell ("bash -c 'echo " & CHR(34) & eMailDraft & CHR(34) &_
+ " | mutt -s "& CHR(34) & eSubject & CHR(34)& " " &_
+ eMailTO_Linux & " -c " & eMailCC_Linux & " -b " & eMailBCC_Linux &_
+ " -a " & CHR(34) & Attachment_1 & CHR(34) & " " & CHR(34) & Attachment_2 & CHR(34) & " " & CHR(34) & Attachment_3 & CHR(34) &_
+ " && notify-send "& CHR(34) &" Email Sent - "& eSubject & CHR(34) &" || notify-send ""ERROR - Mail not sent"" && exit 0 '")

----------------------------------------------------------
Note all variables [ eMailDraft , eSubject, eMailTO_Linux , eMailCC_Linux, eMailBCC_Linux , Attachment_1 , Attachment_2, Attachment_3 ] are dim as string
Also note that ; Attachment_1 , Attachment_2, Attachment_3 are declared / fetched as ABSOLUTE PATH and not as URL
e.g. of absolute path :- Attachment_1 = "/home/kenneth/Documents/Test Sales Data.pdf"
If you are not using any of the mentioned variables please remove it appropriately from the shell option setting. Any BLANK variable will throw a bash shell error.

----------------------------------
For everyone's convenience I am attaching 2 files 1. Install_Configure_mutt.txt 2. Demo_Send_Silent_Email_Linux_Using_Mutt.ods

Install_Configure_mutt.txt: Install and Configure MUTT mail client for linux terminal; (4.53 KiB) Downloaded 115 times

Demo_Send_Silent_Email_Linux_Using_Mutt.ods: Demo Email Send File with Mutt; (33.99 KiB) Downloaded 112 times

Read following instructions carefully and follow it precisely for emailing silently from Linux + Libreoffice + mutt
-------------------------------------
Attaching script Install_Configure_mutt.txt

Install_Configure_mutt.txt: Install and Configure MUTT mail client for linux terminal; (4.53 KiB) Downloaded 115 times

1) save and RENAME file as -

Code: Select all

~/Install_Configure_mutt.sh

2) make executable -

Code: Select all

chmod 755 ~/Install_Configure_mutt.sh

3) run file -

Code: Select all

bash ~/Install_Configure_mutt.sh

Opens terminal and provides instructions to follow.

4) Configure email credentials in muttrc (basic template already provided for AOL / YAHOO / GMAIL )
This configuration file is stored in location ~/.mutt/muttrc
You can open it anytime to update your email credentials.

5) Now open enclosed file Demo_Send_Silent_Email_Linux_Using_Mutt.ods

Demo_Send_Silent_Email_Linux_Using_Mutt.ods: Demo Email Send File with Mutt; (33.99 KiB) Downloaded 112 times

Here you can provide email address , attachment path and email draft in provided calc sheet cells (you can also derive the same with your suitable convenient formula )

6) There are 2 buttons "Send Email Linux Silent" will use mutt and "Send Email Linux User Interaction" will use your GUI email client to send your email
Silent Sending of email works only with Linux / OS where mutt is available.
In this demo file mutt flags like -c (for Cc email) -b (For Bcc email) -a (For Attachment path) are derived in the reference cell using the formula. This ready formatted strings prepared to work with mutt syntax are then used as variable "eMailTO_Linux" and "Attachment_1" in macro "SendEmail_Linux_Silent"

Give it a try !
Happy Silent Emailing ...

How to send Email in Evolution mail using Calc Macro

How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc

Re: How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc Macro

Re: How to send Email in Evolution mail using Calc Macro