How to send Email in Evolution mail using Calc Macro

Creating a macro - Writing a Script - Using the API

How to send Email in Evolution mail using Calc Macro

Postby 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. :crazy:

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   Expand viewCollapse view
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
Last edited by Kenneth08 on Tue Aug 29, 2017 9:30 am, edited 3 times in total.
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

Re: How to send Email in Evolution mail using Calc

Postby 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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5079
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to send Email in Evolution mail using Calc

Postby 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?"?
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

Re: How to send Email in Evolution mail using Calc

Postby 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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to send Email in Evolution mail using Calc

Postby 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?
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

Re: How to send Email in Evolution mail using Calc

Postby acknak » Fri Aug 25, 2017 6:31 am

Sorry, no. I don't know how to do that—or if it's possible.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to send Email in Evolution mail using Calc

Postby 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   Expand viewCollapse view
   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.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7965
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to send Email in Evolution mail using Calc

Postby 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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26712
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to send Email in Evolution mail using Calc

Postby 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   Expand viewCollapse view
   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.
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

Re: How to send Email in Evolution mail using Calc

Postby Zizi64 » Tue Aug 29, 2017 6:59 am

Have you tried the code with a real email client software like the Thunderbird?
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7965
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to send Email in Evolution mail using Calc

Postby 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.
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

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

Postby 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   Expand viewCollapse view
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
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

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

Postby Zizi64 » Tue Aug 29, 2017 9:49 am

https://forum.openoffice.org/en/forum/v ... 13&t=67417

Code: Select all   Expand viewCollapse view
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)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7965
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby 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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2465
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26712
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby 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.
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

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

Postby Kenneth08 » Wed Aug 30, 2017 4:41 am

Zizi64 wrote:https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=67417

Code: Select all   Expand viewCollapse view
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.
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests