[Solved] Send email from Calc (LibreOffice Windows 10)

[askehall]

I have written this subroutine (macro) to send an alert email to my usual email address.

Code: Select all

sub SendAlert (wksym as string)
Dim eMailAddress, eSubject as string
Dim eMailer as object
Dim eMailClient as object
Dim eMessage as object

eMailAddress = "xxxxxx@yahoo.co.uk"
eSubject = "Stop Loss sell Alert"
eMailer = createUnoService("com.sun.star.system.SimpleSystemMail")

eMailClient = eMailer.querySimpleMailClient()

eMessage = eMailClient.createSimpleMailMessage()

eMessage.Recipient = eMailAddress
eMessage.Subject = eSubject
eMessage.Body = "wksym"  'temporary whilst testing
'AttachmentURL = convertToUrl("c:\users\fjcc\desktop\99Export.txt")
'eMessage.Attachement = array(AttachmentURL)
eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )
End Sub

It fails with this message on the last line (sendSimpleMailMessage)

BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.Exception
Message: Send email failed.

After warning me with thefollowing message

There is no email program associated to perform the requested action. Please install an email program or, if one is already installed, create an association in the Default Programs control panel

I have checked my associations and "Mail" is set as the default.
I have have looked around the forum and it appears several users have this problem but there doesn't appear to be a simple solution.

[Zizi64]

There is no email program associated to perform the requested action. Please install an email program or, if one is already installed, create an association in the Default Programs control panel

I have checked my associations and "Mail" is set as the default.

What is your default email client software? There is not built in real email client in the Win10: you must buy or download install some email client software like the free and opensource Thunderbird. The LibreOffice and the AOO will not work with the web-based mail services.

[pharmankur]

Using Libreoffice 5.3 Calc Macro on Windows 8 as well as Linux Mint 18.

I run into the same problem in Windows but not in Linux Mint.

In fact I have thunderbird installed as default client in both Windows as well as in Linux Mint.

In windows, Thunderbird fires up when I execute the macro. But fails at the last line, it gives a error saying " Type : com.sun.star.uno.Execption Message: Send Email Failed"

The error happens at line --> eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )

Screenshot enclosed.

Note - This error happens only in windows.

The code I am running is customised to send an pdf attachment with standard mail body , sent to email addresses picked up from a cell present in the spreadsheet itself. The Macro I am running on Windows + Libreoffice Calc is as follows :-

Code: Select all

Sub SendEmail_Windows

dim document as object
dim dispatcher as object
dim eMailaddress1 as string
dim eMailaddress2 as string
dim eMailaddress as string
dim eSubject as string
dim pdfattachment as string
dim partyname as string
dim Reimb as string
dim Transport as string

eMailAddress1 = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("P7").getstring
eMailAddress2 = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("AA2").getstring
eMailaddress = eMailaddress1 + "," + eMailaddress2
eSubject = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("AA1").getstring
pdfattachment = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("Y2").getstring
partyname = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("M3").getstring
Reimb = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("Z1").getstring
Transport = thisComponent.getSheets.getByName("Invoice").getCellRangeByName("Z2").getstring

   eMailer = createUnoService ("com.sun.star.system.SimpleSystemMail")
   eMailClient = eMailer.QuerySimpleMailClient()
   eMessage = eMailClient.createSimpleMailMessage()
   eMessage.Recipient = eMailAddress
   eMessage.body="Dear " + partyname + "," + " TEXT1 " + Transport + " TEXT2 " + Reimb + "  TEXT3 " + " TEXT4 " + " TEXT5 "

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   eMessage.Subject = eSubject
   AttachmentURL = convertToUrl(pdfattachment)
   eMessage.Attachement = Array(AttachmentURL)
   eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )
End Sub

In Linux Mint I am using the same code as in windows except for the line
eMailer = createUnoService ("com.sun.star.system.SimpleSystemMail")
which is replaced by
eMailer = createUnoService ("com.sun.star.system.SimpleCommandMail")

And Libreoffice + Linux Mint works fine with the above mentioned code. Only Libreoffice + Windows gives error

[Zizi64]

Is there installed, and set to default the Thunderbird E-mail client on the Windows?

[Villeroy]

viewtopic.php?f=21&t=90298

[pharmankur]

@ Zizi64
Off course. Thunderbird is the default client.
I am saying , in windows , with this code thunderbird gets OPENED but error occurs at last code line & email is not created.

[pharmankur]

viewtopic.php?f=21&t=90298

Thanks for taking efforts to submit a python code.
But the point is to create a email with attachment. The reference path of the attachment is in one of the cell of Calc.

Shortly, I will submit the the ods file which I am working on so that you can get better idea.

[Villeroy]

https://docs.python.org/2/library/email.html

[pharmankur]

Monthly_Invoice.zip

(85.93 KiB) Downloaded 343 times

Find attached the zip file for better understanding of what I am trying to do & where error occurs.
First read carefully about the intended use of the calc file.

1) Extract Monthly_Invoice zip file on desktop which will create a folder "Monthly_Invoice". Inside which one more folder "Inv" with a file "DummyInvformat.ods"

2) Open DummyInvformat.ods ; here you will find some invoicing data already filled up. You will also notice 3 macro buttons 1. Save_Invoice_Create_PDF , 2.Email from Linux 3. Email From Windows

3) You need to click on 1st button Save_Invoice_Create_PDF --> The button will do as its said. It will save a copy of file with new name "Inv090001-Kiran Agencies.ods" , Also a dialog box will open asking to save PDF of the same file. Please browse to Desktop & save the PDF file in folder Monthly_Invoice.

(Very Important) Now CLOSE all the files & go to Monthly_Invoice folder , you will now find 2 files "Inv090001-Kiran Agencies.ods" & "Inv090001-Kiran Agencies.pdf"

4) Now open "Inv090001-Kiran Agencies.ods" file. ... Now if you are running Linux , press the 2nd macro button 'Email from Linux'
You will see Thunderbird compose email box gets opened with the pre filled information like email address of the Bill to Customer , also email is marked to all concerned sales force related to the customer, The subject gets customised with customer name - Invoice Number & Amount ; The body of email will be customised with Customer Name - Shipment details & other important information present in invoice. ALSO Very importantly the generated PDF invoice will also be attached.

5) Now open the same Inv090001-Kiran Agencies.ods file from windows (ensure its PDF file is also in the same folder). Click on the 3rd Macro button "Email From Windows"

Now you will see the problem ... Thunderbird will get opened but the email wont be composed.

Please read all the macros enclosed with the file. We need to use the same file in Linux as well as Windows machines. as I learned that createUnoService ("com.sun.star.system.SimpleCommandMail") wont work in windows ; I have changed it to createUnoService ("com.sun.star.system.SimpleSystemMail") in the email macro for windows. Thus there are 2 different macro button for email each for Linux & Windows separately.

I hope this will help you to get much clarity on what I want to achieve & the problem.

Your help is very much needed !

[Zizi64]

We need to use the same file in Linux as well as Windows machines. as I learned that createUnoService ("com.sun.star.system.SimpleCommandMail") wont work in windows ; I have changed it to createUnoService ("com.sun.star.system.SimpleSystemMail") in the email macro for windows. Thus there are 2 different macro button for email each for Linux & Windows separately.

viewtopic.php?f=45&t=26280

[pharmankur]

Thus there are 2 different macro button for email each for Linux & Windows separately.

viewtopic.php?f=45&t=26280

Thank you,
Will consider this further automation once the basic problem is solved & if its working in windows !

[Villeroy]

You need to set Macro Security to LOW in Libreoffice Tools - Security - Macro

This is surely the last thing I would do.

Your help is very much needed !

No, you need someone for a paid job you can't get finished.

[Zizi64]

You need to set Macro Security to LOW in Libreoffice Tools - Security - Macro

It is enough to set to Medium...

[Villeroy]

You need to set Macro Security to LOW in Libreoffice Tools - Security - Macro

It is enough to set to Medium...

Leave it VERY HIGH.
Open the file and study the code.
Save the file to one of your trusted directories. My favourite is /tmp/test/ which is deleted on reboot.
Call menu:File>Reload after saving.

In this particular case there is not so much to run. The work of a typical copy&paste programmer pressed for time. I'm not willing to impress his big boss.

[Zizi64]

Open the file and study the code.

I agree...

You can to do it with medium setting: Do not enable the macro running first, when the software ask you...

[pharmankur]

Thanks a lot for a discussion on which macro security level we should keep ... OK ... you keep it whatever you like ...There, I was mentioning my settings.

Now can we discuss about the basic issue point here???? Which is , In WIndow how to get it working !

@ Villeroy - Appreciated your comments so far. But I was under impression that this is an open source community and there is nothing wrong in asking for help here. Off course no one has compulsion to provide answers; but I believe its about spirit. So If personally you know the solution to this problem but for some reason if you are not comfortable sharing it, you are still welcome !

[Villeroy]

You do not ask for help. You ask for someone doing your job. Debug your code! It smells fishy.

[pharmankur]

Monthly_Invoice.zip

(84.54 KiB) Downloaded 355 times

Hi,
Found the solution.
1) Identified its a issue related to Thunderbird. Thus Un-installed Thunderbird from windows.
2) Found on forum that OO/LO sends email directly & successfully from Windows Live Mail version 11 (doesn't work with Ver 12 & Latest ones) Thus installed Windows Live Mail version 11 & Configured my email IMAP with WLM11 (& tested if its working well)
3) Changed Last Line of Windows Macro which actually composes mail (As Linux Macro was working, kept it unchanged)
eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )
to
eMailClient.sendSimpleMailMessage ( eMessage, OpenWin )

And tested as I explained in my previous post & IT WORKED ... WLM email compose window opened & sent mail with all predefined customized fields as its work with Linux & Thunderbird !

My Config -
Set 1) Windows 7 + WLM11 + Libreoffice 5.3
Set 2) Windows XP + Outlook Express + Libreoffice 4.2 (tested & it works as well)
Set 3) Linux Mint 18 + Thunderbird + Libreoffice 5.3

Attaching my working file.

Thank you Villeroy & Zizi. You can mark it solved if it passes your test.

[RPG]

I was writing a macro with use the SimpleCommandMail service. this service is nearly the same as SystemMailProvider. It seems to me that LibreOffice does have expand a little the service and give it an other name. I have the idea it does not matter if you use Windows or Linux. When you use LibreOffice 4.2 and higher then use SimpleCommandMail. For openOffice use I'm sorry that the comments is more in Dutch but it was original for the Dutch forum. I have test the code below now for LibreOffice and ApacheOffice both only with linux.

Code: Select all

REM  *****  BASIC  *****
option explicit
dim oMessage
dim oeMailClient

sub MakeEmailService_poging
dim oServiceManager
dim oMailservice
oServiceManager = GetProcessServiceManager()
dim sServices,sServiceName
sServices="com.sun.star.system.SimpleCommandMail|com.sun.star.system.SystemMailProvider"
dim iets
for each sServiceName in split(sServices,"|")
	oMailservice= oServiceManager.createInstance(sServiceName)
	if  isnull(oMailservice)=false then exit for
next
if isnull(oMailservice) then
	print "We weten niet welke service er gebruikt kan worden. We stoppen."
	 end
end if

dim sCreateClient
dim sCreateEmailMessage
select case sServiceName
	case "com.sun.star.system.SimpleCommandMail"
		oeMailClient = oMailservice.querySimpleMailClient()  ' Feitelijk overbodig of er moet nog goed getest worden
		oMessage = oeMailClient.createSimpleMailMessage()

	case "com.sun.star.system.SystemMailProvider"
		oeMailClient = oMailservice.queryMailClient()  ' Feitelijk overbodig of er moet nog goed getest worden
		oMessage = oeMailClient.createMailMessage()
end select
oMessage.Recipient="doesnotmatter@water.com"
oMessage.subject="see the sub ject"


select case sServiceName
	case "com.sun.star.system.SimpleCommandMail"
		oeMailClient.sendSimpleMailMessage( oMessage,CreateUnoValue("long",2) )

	case "com.sun.star.system.SystemMailProvider"
		oeMailClient.sendMailMessage( oMessage,CreateUnoValue("long",2) )
end select

end sub

[pharmankur]

My Config -
Set 1) Windows 7 + WLM11 + Libreoffice 5.3
Set 2) Windows XP + Outlook Express + Libreoffice 4.2 (tested & it works as well)
Set 3) Linux Mint 18 + Thunderbird + Libreoffice 5.3

Issues found in Set 3 in above mentioned configuration (no problems found in Set 1 & Set 2 of the configuration ) --->
After upgrading to Linux Mint 19 + Thunderbird 60 + Libreoffice 6.1 ; Emailing through the macro suddenly started behaving strangely. The email body text generated through macro started appearing in Recipient email boxes in thunderbird and is not usable.
Setting up Thunderbird as email client [ Libreoffice --> TOOLS --> OPTIONS --> INTERNET --> E-MAIL --> EMAIL PROGRAM --> browse & set to --> /usr/bin/thunderbird ] in libreoffice is also not working; as it is no more firing up thunderbird.

I do not know what is going wrong , Also I dont know whether bug is in Libreoffice Or Thunderbird Or somewhere else.

But here is the finely working workaround ---
Solution is to use geary email client, Set GEARY as default email client in linux mint. Also set geary in libreoffice [ Libreoffice --> TOOLS --> OPTIONS --> INTERNET --> E-MAIL --> EMAIL PROGRAM --> browse & set to --> /usr/bin/geary ]

--- Cheers

[Zizi64]

I do not know what is going wrong , Also I dont know whether bug is in LibreOffice Or Thunderbird Or somewhere else.

Try it with older and newer versions of the LibreOffice...

[pharmankur]

Try it with older and newer versions of the LibreOffice...

Ohh ... Tried with Libreoffice 6.0.7.3 in Linux Mint 19 & with Thunderbird 60.2.1 (64-bit) --- And it worked charmingly as before !!!

So definitely Libreoffice 6.1 has the bug, strangely specific to THUNDERBIRD only ... as GEARY works in both version of libreoffice.
Thank you !!!