[Solved] Create an e-mail from Basic Macro, Calc

[drhalter]

Hello:

I'm trying to sort out how to create an e-mail using OpenOffice Basic. I've sorted through what I think are most of the posts on this subject and keep getting some errors and unexpected behaviors. While I wouldn't necessarily expect my OpenOffice basic macro to force my default mail program to do anything it doesn't want to do, I'd at least like to replicate the behavior that I expect when I click a "mailto:" hyperlink within a document. If there is a way to force the body of the email, the recipient, and a list of To: e-mails into a new message as well, I'd like to do that, too! -- Also, I realize this is two questions and may have two entirely separate answers, but, one part of my brain (a naive part, perhaps) would like to think that the two questions are very closely related.

The current behavior of a "mailto:" hyperlink on my system: When I create a hyperlink, I can also specify the subject line (this is through the normal GUI). Once I click it, the default mail client opens a new message to the specified e-mail address and drops the subject line in place. I have multiple accounts in my mail client, but the mail client seems to pick the "From:" line on its own with no direction from OpenOffice. I assume this is the normal behavior.

The code below is how I've modified other similar snippets that are present in the forums. This one is closest to ZenLord's, but similar to many others.

This code runs without errors on my system. The only actions that occur, however, are that it causes the mail client to start and become the active window in the OS or else just become the active window in the OS if it is already running. It does not create any new mail message, unless somehow they are being created but are 'hidden' from the GUI of the OS or hidden from the GUI of the mail client.

Code: Select all

Sub Main
   DIM MailClient, MailAgent, MailMessage AS Object
   MailAgent = CreateUnoService("com.sun.star.system.SystemMailProvider")
   MailClient = MailAgent.queryMailClient()   
      MailMessage = MailClient.createMailMessage()
      MailMessage.Recipient = "someone@mailservice.net"
      MailMessage.Subject = "This is a test Subject"
      MailMessage.Originator = "me@mailservice.net"
      MailMessage.Body = "This is a string placeholder for the text of the body of the email ... Signed, Dave"

   MailClient.sendMailMessage(MailMessage, 0)

End Sub

Thanks for ideas to these people: lodovi, Zizi64, ZenLord (and of course I'm sure I missed a few of you).

If it is relevant: Apache OpenOffice 4.1.3, MacOS 10.12.4, Mail.app
--Please, I have seen that some responses to similar prior questions have asked the OP to change their default mail app. While that option may work, please note that my question above is two parts: first, can the behavior of clicking a "mailto:" hyperlink be duplicated with a Basic macro. It seems that this should be possible no matter the mail client, as long as a default mail client is specified in the OS. Second, if possible, can it be extended to include other basic parts of the message, like the body of the message.

Above is the gist of the question, but for more information about the project:
I'm not trying to spam anyone, but am monitoring a bunch of employees as to whether they are completing their work in a timely fashion. I would send this e-mail out after every week or so. So far the workbook and its programming can display a table (cells, cellrange) of who is overdue and by how much they are overdue but hides all the people who are up to date. After it provides that filtered table (cells, cellrange), I copy/paste that into an e-mail, then go back to Calc, select the list of associated e-mail addresses and then copy/paste those into the To: field. Works well, but as long as I'm writing a macros to make this process easier, I'd really like to see how easy I can make it!

Therefore, eventually, I'd like the body of the e-mail to automatically populate with a short message "Get your work done on time!" as well as the relevant table (cells/cellrange) containing how far overdue each person has become.

Thanks so much!

Dave

[Villeroy]

Your office suite comes with a fully functional Python runtime which can be used as a better macro language or completely without the office suite.
https://docs.python.org/2/library/smtplib.html
The following does not require any office suite at all.

Code: Select all

import smtplib

mail_User = "user_name"
mail_Passwort = "Secret"
mail_SMTP = "securesmtp.example.com"
mail_SMTPport = 587
mail_Sender = "me@example.com"
mail_Receipient = "someone@elsewhere.com"

mail = MIMEText("Mail Body Text" )
mail['To'] = mail_Receipient
mail['From'] = mail_Sender
mail['Subject'] = "Subject Line"

mail_server = smtplib.SMTP(mail_SMTP, mail_SMTPport)
mail_server.ehlo()
mail_server.starttls()
mail_server.ehlo()
mail_server.login(mail_User, mail_Passwort)

mail_server.sendmail(mail_Absender, mail_Empfaenger, mail.as_string())    
mail_server.quit()

[drhalter]

Hi Villeroy
That seems like an interesting solution and I think it would indeed solve the problem I posed in the question, that is, trying to take a table created in Calc and share it via e-mail to a list of e-mail addresses stored in Calc. I did see these python solutions in the forums but didn't think they would be good because of some security concerns, and the fact that I haven't learned Python enough to use them effectively (my side job, apart from some programming, is fixing the plumbing inside of people, I'm a doc). Unfortunately, it looks like I would be storing my e-mail username and password in clear text, or at least in an easily accessible string, and therefore this looks like a quite un-secure way of sending an e-mail. Well, actually, perhaps a secure way of sending an e-mail, but appears to be an insecure way of securing the relevant information. I would prefer to use the default mail client in my computer (or in others, perhaps Thunderbird or Outlook) because I believe, mistakenly or not, that they take better care of the usernames and passwords than storing them in cleartext.

I'm also unlikely to share my encrypted computer with its password-protected log-in that protects my data, default mail client, usernames, and passwords whereas I may want to easily share this file with someone else, say, a supervisor or underling who may need to take over this task while I'm out on vacation. I'm not interested in storing my username and password inside the file and then passing it along when sharing the file. Of course, someone can steal my computer and hack it probably easily enough, but I'm not intending to share my computer with a hacker the same way I might end up sharing a file with this information in it, even incidentally.

So, two, maybe three questions come out of this: first, the obvious one: is there another way to use OpenOffice (perhaps using its ability in using a hyperlinks) to call open the user's default mail client, create a new message, and fill it with at least a list of recipient email addresses, subject line, and perhaps even the body of the e-mail? The second question: if you must use Python to do this, is there a way to use Python without opening up such a security hole as described above? Or else, perhaps, the third related question: Perhaps my perception of those security risks is not quite accurate?

[Villeroy]

On a decent operating system you could make the script executable but not readable by normal users. I don't know how to do such things on Windows.
StarBasic libraries can be encrypted with a password so you can store confidential data in them.

Mail address in A1
=HYPERLINK("mailto:"&A1)

[drhalter]

Hmm, that still is not quite what I was looking for. Since my OP has to do with the expected behavior of OO after clicking a hyperlinked cell, I thought it obvious that I already knew how to create a hyperlinked cell. I'll admit, I'd never thought of using a formula to create hyperlinks, which I suppose could be useful, though it really doesn't address the OP nor most of my response to the security issues in using Python.

So, I can ask the question again, but I suppose I need to modify the last paragraph of my second post so that Villeroy may understand better. Modifications in italics: "is there another way to use OpenOffice (perhaps using its ability in using a hyperlinks) to call open the user's default mail client, create a new message, and fill it with at least a list of recipient email addresses, subject line, and perhaps even the body of the e-mail using OpenOffice Basic or another of the available programming languages within OpenOffice with OpenOffice Basic being the preferred language?

Thanks!

NOTE: after posting this, Villeroy kindly responded by editing his prior post regarding the security problem I mentioned and again in his following post.

[Villeroy]

viewtopic.php?f=13&t=67417&p=300115&hil ... er#p300448
Store the password in the Basic module and encrypt the Basic library.

[Villeroy]

Oh, and I just tested this one:
=HYPERLINK("mailto:"&A15&"?subject="&B15&"&body="&C15)
with address in A15, subject in B15 and body text in C15. On my system this hyperlink starts a Thunderbird mail ready to send.

The URL concatenation evaluates to mailto:vileroy@example.com?subject=Test Hyperlink Mail&body=Body Text
? starts a sequence of hyperlink parameters. Subsequent parameters are separated by & and the parameters are name=value pairs.
It does even work with line breaks and = in the body text. But the text must not contain ? and &

[drhalter]

Alright Villeroy! Now we are getting there. Thanks for the responses about security but also particularly for the link to viewtopic.php?f=13&t=67417&p=300115&hil ... er#p300448. That put me onto some different ideas. I gotta get some sleep now though.

I think I'll probably end up somewhere along the lines of using the "mailto:" hyperlink within the code somehow. Like I said, haven't sorted it out yet, but I think that will work out. I had a similar brainstorm about using that while taking a break earlier. I'll post again if I get code that is working and (if/when?) I run into more problems!

[drhalter]

Hi

Thanks for your interest. I've come up with a solution. I think it's the best I'm going to get, at least for now, but I'm satisfied.

It looks like what I was really wanting was just the mailto: functionality, I just didn’t know exactly what I was asking and, mea culpa, didn’t think to search for “mailto” but had been only searching for e-mail or email… Anyways, this is the code I came up with, significantly shortened and simplified for demonstration purposes. For my final product, I have used some For-Next loops to write a comma separated string of email addresses for eAddress (that is "email1@somewhere.com, email2@somewhere.com, email3@somewhere.com" and so on) and then used the longer string in the email. Here's the code:

Code: Select all

Sub SimpleMailTo  
Dim launcher as object
Dim eAddress, eSubject, eBody, CCeAddr, BCCeAddr, aHTMLanchor as string

launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")

eAddress = "name@email.com"
CCeAddr = "CCeName@email.com"
BCCeAddr = "BCCname@email.com"
eSubject = "This is the Subject"
ebody = "This is the body"


aHTMLanchor = "mailto:" & eAddress & "?cc=" & CCeAddr & "&&bcc=" & BCCeAddr & "&&subject=" & eSubject & "&&body=" & eBody

launcher.execute(aHTMLanchor, "", 0)

End Sub

A few notes on troubleshooting: the mailto: html anchor appears to care if there is a ? within the eAddress string or an & within any of the remaining strings. At least on my system it did not appear to care about spaces within the Subject line or the body. My default mail client is Mail.App on MacOS

And no, I don't have any error checking on the e-mail addresses... That would be nice, but for now, I'm trusting the user (which will usually be me) can input them correctly. The nice thing there, however, is that the default mail client seems to recognize gobbledegook when it sees it and makes it obvious, so, there's less need for error checking on that problem.

Thanks to Villeroy who responded to me directly and helped me get on the right track and thanks to Nocton, B Marcelly, Andrew Pitonyak, and tutorialspark.com (and probably some others of you all) for guiding me through the right information from your other posts.

I didn't get all the way to where I wanted, but, using some code from Andrew Pitonyak's shared book, I was able to get the cells I wanted placed onto the clipboard. From there it was just a matter of using the keyboard shortcut to paste in the cells once the e-mail popped open and was filled in.