How to send Email in Evolution mail using Calc Macro

English
, ,
#1

I am searching forums but unfortunately I can not find the exact code that I need. 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

Inserted by @Lupp: This topic was also posted as How to send Email in Evolution mail using Calc Macro (View topic) • Apache OpenOffice Community Forum 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.

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.

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
Need a macro for automatic sending out emails
How to trigger email notification by date in Calc
LibreOffice Writer Not talking to Thunderbird or sending merged emails
Why to Close a topic and how to post reply to such Closed topic?
#12

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.

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
#13

Hello @Kenneth1,

I think this is possible only if your operating system supports MAPI.

In some E-mail clients you can allow other applications to “blindly” send E-mails from your account, for example in Thunderbird using the option “mapi.blind-send.enabled”.

#14

@librebel,my OS is Ubuntu 15.04. Do you know where to find or how to know if it has blind MAPI?
Thanks

#15

This is a bit out of my league, but i found this article which says you need to install evolution-mapi plugin.
Good luck!

#2

I use Ubuntu. LibreOffice and Evolution and they all work well together so I guess it will be fine for you.

First of all make sure Evolution is your default mail client. You can check it at
System Settings > Details > Default applications

Then in Calc under the File menu choose Send and the appropriate item there. It should work just like that.

If this answers your question please remember to mark it so for the benefit of all the community. Thanks

#3

Hello @PaoloPelloni

Your help is really appreciated.
Is it possible to put it into macro? 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.

#4

Good question @Kenneth1.
Unfortunately my macro knowledge is … none !
My suggestion is to post it as a new main question on ask and see if someone is able to help you. I hope so! :slight_smile:

#5

@PaoloPelloni, understood. Thanks for your help. This is really appreciated. :slight_smile:

#6

Hello @Kenneth1,

If you want LibreOffice to send out your documents by E-mail using your own favourite E-mail program, LibreOffice must first know the location of your E-mail program.

Select the menu "Tools / Options... / Internet / E-mail / E-mail Program:" and click on the Browse button to locate your E-mail program.

For Evolution the path should look something like "/usr/bin/evolution" ( not sure, i haven’t installed Evolution ).
Btw. the standard and recommended E-mail program on Ubuntu is Mozilla Thunderbird, its path is "/usr/bin/thunderbird".

Now back in your Calc spreadsheet, you can click the menu "File / Send / E-mail Document ..." ( or "E-mail as OpenDocument Spreadsheet...", or "E-mail as Microsoft Excel ...", or "E-mail as PDF ..." ), and your selected E-mail program will automatically be opened with the document attached.

EDIT 20170825-1

The following macro can be used to send out the current document by E-mail:
( The complete current document is attached to the E-mail )

Sub Send_As_Document( Optional strRecipient As String )
REM Example Call: Send_As_Document( "myemail@mydomain.com" )
REM Can be called without argument, then the       🡳  Default Recipient  🡳      will be used.
	If IsMissing( strRecipient ) Then strRecipient = "myemail@mydomain.com"		REM Default Recipient.
	Dim oFrame As Object, oDispatch As Object
	Dim aProps(0) As New com.sun.star.beans.PropertyValue
	aProps(0).Name = "Recipient"
	aProps(0).Value = strRecipient
	oFrame = ThisComponent.CurrentController.Frame
	oDispatch = createUnoService( "com.sun.star.frame.DispatchHelper" )
	oDispatch.executeDispatch( oFrame, ".uno:SendMail", "", 0, aProps() )
End Sub

It does not write anything in the body message.

Send As PDF, with Settings dialog, supports sending only the current selection of your Sheet:

Sub Send_As_PDF( Optional strRecipient As String )
REM Send ( a selection inside ) the current document as a PDF attachment to your E-mail.
REM Opens the PDF Export Options dialog.
REM Example Call: Send_As_PDF( "myemail@mydomain.com" )
REM Can be called without argument, then the       ?  Default Recipient  ?      will be used.
	If IsMissing( strRecipient ) Then strRecipient = "myemail@mydomain.com"		REM Default Recipient.
	Dim oFrame As Object, oDispatch As Object
	Dim aProps(0) As New com.sun.star.beans.PropertyValue
	aProps(0).Name = "Recipient"
	aProps(0).Value = strRecipient
	oFrame = ThisComponent.CurrentController.Frame
	oDispatch = createUnoService( "com.sun.star.frame.DispatchHelper" )
	oDispatch.executeDispatch( oFrame, ".uno:SendMailDocAsPDF", "", 0, aProps() )
End Sub
#7

Hello @Librebel

Thanks for your kind help.
Is it possible to put it into macro?
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.

#8

I added a sample macro in my answer, please see if that works.

#9

Hello @lubrebel, thanks for the codes. Yes it is working. However, it just creating the Email and attach the file. Is it possible to also automatically send?

#10

Hope that you can help me to this. :slight_smile:

#11

The “Send_As_PDF” works fine, but is it possible to choose only the “selected cells/sheets” and send it automatically without opening the PDF export window ?

#16
#17
#18

From my post in - How to send Email in Evolution mail using Calc Macro (View topic) • Apache OpenOffice Community Forum

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

Update the email macro like mentioned below (change your declared variable names accordingly) → 

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.odt 2. Demo_Send_Silent_Email_Linux_Using_Mutt.ods
Demo_Send_Silent_Email_Linux_Using_Mutt.ods (43.6 KB)
Install_Configure_mutt.odt (4.5 KB)

Read following instructions carefully and follow it precisely for emailing silently from Linux + Libreoffice + mutt

Attaching script Install_Configure_mutt.odt
Install_Configure_mutt.odt (4.5 KB)

  1. save and RENAME file as -
~/Install_Configure_mutt.sh
  1. make executable -
chmod 755 ~/Install_Configure_mutt.sh
  1. run file -
bash ~/Install_Configure_mutt.sh

Opens terminal and provides instructions to follow.

  1. 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.

  2. Now open enclosed file Demo_Send_Silent_Email_Linux_Using_Mutt.ods
    Demo_Send_Silent_Email_Linux_Using_Mutt.ods (43.6 KB)

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 )

  1. 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 ! Pls revert if any issue is faced or clarification required .
Happy Silent Emailing …

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.