Send calc range as a table in a body of an email

[marus_b]

Hello everyone,

My final objective:
I’d like to automate the process of selecting a range of cells, choosing “copy” command, and subsequently pasting it, as a table, into a body of a mail message. In my case it’s Thunderbird.

Here is what I’ve done so far:

1. I tried to avoid Thunderbird all together, by first trying to utilize SimpleSystemMail service. But the “body” argument only accepts string values.

2. Then I attempted to utilize services from com.sun.star.mail module. The “create” function from the MailMessage service accepts data in XTransferable structure, which is the same as XSystemClipboard. However, I cannot get the mail service to work (I simply do not know where am I going wrong). Would someone point me in the right direction, please? This is what I have gotten so far:

Sub testMail
Dim mailSP As Object: mailSP = createUnoService(“com.sun.star.mail.MailServiceProvider”)
Dim mailS As Object: mailS = mailSP.create(“com.sun.star.mail.MailServiceType.IMAP”)
Dim oAuth As Object: oAuth = createUnoService(“com.sun.star.mail.XAuthenticator”)
Dim oCurCont As Object: oCurCont = createUnoService(“com.sun.star.uno.XCurrentContext”)

oCurCont.getValueByName(“ServerName”) = “imap.gmail.com” ← * this is where I’m getting stuck *

mailS.connect(oCurCont, oAuth)
End Sub

In all honesty, I don’t even know if I’m going the right way about any of this, but I’ve been searching information on similar topic, and cannot seem to find anything with a similar problem.

Thank you very much for any help and/or suggestions.

[Zizi64]

Gmail uses two-factor authentication. I suppose that a simple authentication will not work in Gmail.

[marus_b]

Thank you for pointing this out. That’s at least one reason I should not be pursuing this course of action.

[Lupp]

Did I miss substantial content of any answer or comment on this thread?

@marus_b

I’d like to automate the process of selecting a range of cells, choosing “copy” command, and subsequently pasting it, as a table, into a body of a mail message.

As I see it, any “table” taken from a spreadsheet range that might be reasonably “pasted” into the body of a mail message must be seriously restricted in size, and the user must be aware of the fact that she (m/f) always will paste text (most likely of a restricted character set), no formats (including NF), and bound to represent field (column) delimiters by some ordinary character. In short: A “table” pasted into a string (what the message body technically is) must be small and use a restricted CSV structure.
Lacking sufficient experience I won’t discuss here the alternative of using HTML for the purpose.

The composition of the csv-like string for insertion is simple also with Basic:

colDelim = "¢" REM Should be a charater otherwisw unused in the table.
REM HT may be replaced with spaces when pasting the result into a mail body. 
LF = Chr(10)
Dim bodyTableLines(u)
For j = 0 To u
  bodyTableLines(j) = Join(da(j), colDelim)
Next j
bodyTableString = Join(bodyTableLines, LF)
bodyString = "Now comes the table: " & LF & LF & bodyTableString & LF & "Roger."

Concerning the API: The SimpleMailService and SimpleMailClient services are outdated IMO, but basically still usable. The method createSimpleMailMessage and the interface it returns are OK. In specific the message object you get this way will accept attachments, and attachments are the appropriate way to send “tables”, whether as little spreadsheet files or as csv-shaped text files, or whatever (except images).

[Villeroy]

LibreOffice comes with Python including smtp library. smtplib — SMTP protocol client — Python 3.10.4 documentation

[marus_b]

Thank you to you as well.

Thus, aside from what @Zizi64 has mentioned about the lack of authentication protocol capability, does this mean that the com.sun.star.mail module exists only to support the Python library? This is probably a trivial question, but I am not a professional programmer, and Basic is the first language I’m learning.

[Villeroy]

Stop learning Basic. It is a dead language of the past century. Too clumsy, too difficult, still used in office macros but useless otherwise.

[marus_b]

I just looked up your other conversations on precisely the same topic, and I am going to refrain from asking the same questions. Nonetheless, I’m pretty sure that by now it’s more of the UNO component that I’m familiarizing myself with than Basic. Although, I’m going to take your advise seriously and start learning an actual programming language. My idea was to learn Javascript next, as I could utilize knowledge of that to automate a different piece of software that I use for work. However, if I may ask just one more question. From the perspective of Libreoffice automation, are both Python and Javascript equally capable, of is one better suited than the other?

[Zizi64]

The real difficulty is not to learn a programming language, but learning about API of the LO. API: Application Programming Interface. You can call the API functions from many supported programming languages and environments.

[mikekaganski]

@marus_b

From the perspective of Libreoffice automation, are both Python and Javascript equally capable, of is one better suited than the other?

JavaScript is not used much to automate LibreOffice; it’s (awkwardly) implemented using a Java-based library, and it is hoped to be re-implemented using some different engine not having that requirement at some stage … I’m afraid that using JavaScript for the task is for now not a wise decision.

[marus_b]

@Zizi64 That’s precisely what I am doing. I am going through Mr. Pytoniak’s books, and familiarizing myself with UNO and its capabilities. Since I begun my programming hobby with VBA earlier, I thought that going with Basic will be the easiest approach, at least for beginning. Nevertheless, I reached the point where I find myself fairly comfortable reading the api.libreoffice.org site, although I wish that some descriptions of interfaces would be a little less cryptic. Thanks to @Ratslinger pointing it out, I have learned about the MVC concept, which I see is utilized in a lot of cases in the UNO package. Seems like moving towards a different programming language is going to be the right course of action. Although, I want to go through AndrewBase first, as I do not know much about databases and I feel like it may be a fairly decent starting point. Once that is done then it’ll either be Python or Javascript. Did not make a decision which one first.

[marus_b]

@mikekaganski Thank you. Looks like Python it is then.

[Villeroy]

Even the Pitonyak book is 20 years old but still relevant for understanding UNO from the view point of a script coder. However, Pitonyak assumes that you are familiar with some kind of Basic language. He does not explain the Basic language as such. it really does not make a huge difference if you talk to UNO via Basic or Python. The main obstacle is UNO and if you want to program anything beyond the scope of this office sutie (e.g. anything related to the internet), StarBasic turns out to be useless. Any Python coder can learn a lot from Pitonyaks book. Reproducing any of his example code in Python means just a small translation effort.
You are learning to master something highly complex (UNO) with an outdated and inadequate tool (StarBasic), and the tool serves you nowhere outside UNO. For a beginner, learning a true up-to-date programming language is much more rewarding.

[marus_b]

@Villeroy I completely understand and agree with you. Although with my limited time availability I thought that sticking to learning one “concept” (such as UNO), at a time, would be enough of an undertaking. And even that is too diversified to learn completely. Whereas, a fair knowledge of Basic syntax and the general features that it offers, seemed like a decent basis to start somewhere. However, it was definitely not the ultimate goal :).

[Villeroy]

@marus_b

learning one “concept” (such as UNO), at a time, would be enough of an undertaking.

You are doing the second step (if not the third) before the first step. In the end it will be more time consuming. With a little bit of practical experience in object oriented programming, mastering the UNO monster will be less time consuming.

The example code in Python’s smtplib documentation lets you send an email with 10 lines of code, without office suite, without mail program being installed. You can read table data from a spreasheet (with UNO), or from a plain text file (without UNO) or from some database (with or without UNO), write html mail with a table (Python ships with libraries for that) and send out the message(s). I’m a lousy hobbyist in Python prgramming but I know enough of it that I would always decide against UNO for things that have no relation to an office suite. Storing text data in spreadsheets is the worst misunderstanding in the history of personal computing. If storing text in a calculator is a must have, 5 lines of code scrape the required data from sheet.

[marus_b]

@Villeroy Python 3.10.4 being installer right now
I understand this is completely off topic, in this thread, but just to speed things up: from what I understand there are plenty of IDE environments which Python can be coded in. Any personal recommendations? I’m on Windows.

[mikekaganski]

Note the “LibreOffice Python Guides” at Information and resources for LibreOffice macros - The Document Foundation Wiki

[Villeroy]

I use nothing but a plain text editor with syntax highlighting. This is the most simple and best suited tool for a beginner who does not deal with dozends of interdependent libraries from dozends of co-workers. Your projects are single python fles for now or may be 2 or 3 when you start loading code from other files.

[marus_b]

@mikekaganski thank you, I’m on it

[marus_b]

Gentlemen, thank you all! I feel like I’m standing at the edge of a huge swimming pool, afraid to jump in. All of you just gave me the push that I needed!

Edit - same day evening.

The devil is not as black as they paint him… I’ve installed Python and Notepad++. Looked up on Youtube how to install console. I am still not certain if Notepad++ can have a debugger, as it would be a great option.

Nevertheless, looks like my learning of VBA and LO Basic didn’t go in vein. Although not all concepts were incorporated into the aforementioned languages, I still read about them here and there in the past. It was a great primer for what I’m learning right now. After a few hours on and off throughout the day, I’m already overriding constructors in classes with inheritance. Although I am perfectly aware of the fact that this is still a tip of the iceberg, but so far it’s mostly syntax and a lot of new commands. It’s actually really fun :). @Villeroy it was a great call! Thanks again!