Need a macro for automatic sending out emails

[eeigor]

Please give me a macro example of emailing from Calc to Thunderbird. I need to do a mass mailing of automatically generated emails according to a list of recipients.

I need an automatic mailing. Letters will be generated according to a template, including attachment of files (png - grafics; odt - a text document; .odp - a presentation file) and sent to recipients according to the mailing list. Anyway, I have a working application written by me, but the task is to get away from MS Office.
I use Linux
The mailing list is generated dynamically and is contained in the source application that runs the mailing macro.

---

I created a separate topic.

How to send document via email from libreoffice

[Villeroy]

You have files, pics, media, office documents, whatever.
You are running Linux.
You can programm.
You don’t need any office macro at all. Just write a script stitching together all that mail with attachments.

[eeigor]

The source application is Calc (building fairly complex charts, including overlapping ones*), and I would like the master code to run in Calc. All files will also be created/exported from Calc. Basically they are charts, and the text document is the report that contains those charts, and the presentation is the same data, but in a more presentable way (probably a PDF if I can’t handle Impress). Sending files uploaded to a specified folder is required.
It’s not a database.

We are talking about a graphical way to analyze the raw data. There is little data, but the accumulation goes on throughout the year by adding a column on the sheet. All the data fits on one screen. But there are a lot of charts. An example of when Calc is the perfect app for this task.
For each object of analysis - a separate file. In order not to complicate things. There are not many objects.

---

* Chart overlay is required to build a quartile diagram (box-and-whisker plot ), which is not in the list of available.

[Villeroy]

In cases like this I use to recommend MS Excel. Really. That program turns an arithmetic calculator into any application you can imagine. With LibreOffice on Linux, I would avoid any solution like this in favour of a simple solution.
I can offer a macro performing a mail merge with a single click: https://forum.openoffice.org/en/forum/download/file.php?id=28169
I can offer a most simple Python snippet sending mail: [Calc, Python] Send Mail (View topic) • Apache OpenOffice Community Forum
But I can not offer anything for a source application named “Calc”.

[eeigor]

Already have a working application in Excel VBA (+Word, +PowerPoint, +Outlook). The task is to move away from using Microsoft Office.
I know Python, but I haven’t used it with LO before.

@Villeroy

But I can not offer anything for a source application named “Calc”.

Is it as bad as you say?

Edit:
The thing is, if you go to Python, then you don’t need Calc at all. Theoretically. Python has its own libraries that include the same quartile diagrams out-of-the-box. But the graphical interface would be lost, and all the work of plotting charts would have to be programmed and done not by Calc (by me) .

[Villeroy]

@eeigor

I know Python, but I haven’t used it with LO before. Is it as bad as you say?

Better than Basic anyway. Python is a mature and modern programmiing language with batteries included. smtplib is included in the Python runtime that is shipped with LibreOffice. If you want to send mails with attachments, you have to deal with mime types. This was the one and only slightly complicated issue and the reason why my mail snippet sends out text only.I just wanted to demonstrate how 50 lines of Python code can read some list of addresses and send emails with or without any office suite being installed.
https://docs.python.org/3/library/smtplib.html

[eeigor]

@Villeroy, thanks.
In MS Office, I used OLE Automation. How do I access the object model of the external application (e.g. Thunderbird) here? With MS Outlook this is not a problem.

[Villeroy]

Thunderbird is a mail client as a fully interactive desktop application. More precisely, it is a mail user agent (MUA) with its own message submission agent (MSA). Python’s smtplib is just the same thing without GUI. It does not even require any desktop environment.

Message submission agent

[eeigor]

@Villeroy

Python’s smtplib is just the same thing without GUI. It does not even require any desktop environment.

Yes, but I want to see all sent messages in Thunderbird afterwards.

[eeigor]

I have to figure it out. But I think I found something that works…
Unpack the ZIP archive. Open the attached file and press the button “Email from Linux”.
Link

See example:

To be honest, I don’t really understand the problem with the mailing list yet.
But I see that all fields of the letter are filled in. In this example, only the attachments are missing (RU: Вложить). But the corresponding code is present:
AttachmentURL = ConvertToURL(pdfattachment)
eMessage.Attachement = Array(AttachmentURL)

Edit:
That’s kind of what’s needed… Was the task that difficult? This is a rhetorical question.
The “com.sun.star.system.SimpleCommandMail” service was used (for Linux, “com.sun.star.system.SimpleSystemMail” for Windows).

...
   eMailer = createUnoService ("com.sun.star.system.SimpleCommandMail")
   eMailClient = eMailer.QuerySimpleMailClient()
   eMessage = eMailClient.createSimpleMailMessage()
   eMessage.Recipient = eMailAddress
   eMessage.body="Dear " + partyname + "," + " Find Attached Your invoice. " + Transport + "      " + Reimb + "  Thank you." + " - Regards," + " MYCOMPANY LTD"

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

Apparently, the dispatcher is not used.

True, I send a lot of emails at a time (about 30), and I do not need that every time some windows opened, and I had to confirm something.

[Villeroy]

@eeigor

Yes, but I want to see all sent messages in Thunderbird afterwards.

Use an SMTP server (provider) that is set up in TB. SMTP shows all outgoing Mails from all devices.

[eeigor]

Link from @Zizi64

But I have to try it all before I consider it a solution.

[Villeroy]

@Villeroy

Use an SMTP server (provider) that is set up in TB. SMTP shows all outgoing Mails from all devices.

Save or link my little script to the Script/python/ directory of your profile.
Fill in the connection data on top of the script. You find these data in the connection settings of your mail client under “Outgoing servers (SMTP)”.
Fill out 3 spreadsheet columns with address, subject and message text, select the 3 columns without any column header and call the macro. The sent mails appear in the sent folder of your mail client.
Without any spreadsheet you could read the message data from text files as well, even easier. No office suite, no database connection, no mail merge. Just plain text data sent via SMTP over the internet. Things becomme less trivial with attachments.