[Calc, Python] Send Mail

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Calc, Python] Send Mail

Post by Villeroy »

To all the dumb spammers and legitimate info brokers out there, this is the most simple code snippet I could write in affordable time.

0.
1a. Extract the Python module to <profile>/Scripts/python Download the attachment and move it to a trusted directory where macros are allowed.
1b. Open the document and edit the SMTP settings on top of the module. Just edit the double-quoted values between the hash fences.
1c. Click the install button.
2. Select a 3-column list of spreadsheet cells: (1)mail addresses, (2)subject line, (3)multi-line body text
The selection must not be a filtered range. Copy any filtered range to another area and select that adjacent range. The selection should not include any header row (column labels). Any columns beyond #3 will be ignored.
3. menu:Tools>Macros>Python... sendMailsFromRangeSelection

You can use Calc's concatenation methods and text functions to compose the messages on the sheet. If you can't even do that, there is not the faintest reason to use a spreadsheet anyway.
2021-08-05: Updated "old style" error handler to work with recent Python versions. Added the 5 seconds sleep by robleyd.
Attachments
range2smtp.odt
Python module to send mail from 3-column range selection
(24.98 KiB) Downloaded 201 times
Last edited by Villeroy on Sat Aug 07, 2021 4:40 pm, edited 3 times in total.
thefixer
Posts: 1
Joined: Fri Oct 06, 2017 7:18 pm

Re: [Calc, Python] Send Mail

Post by thefixer »

Good evening Villeroy,

Thank you for posting your work on OO Forum.
I have used your work and found it very useful. I am not an expert in Python script and module so it has been a bit of a learning curve for me.

I would like your help to modify your script if possible.
I have a LibreOffice spreadsheet with 3 columns (TO, SUBJECT and BODY) with hundreds of rows and would like to send an email out one after the other every 5-10 seconds.

Thank you for your support in advance.

Best, thefixer
LibreOffice 5.4.1.2 MAC OSSierra 10.12
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Python] Send Mail

Post by Villeroy »

This is just a demo how trivial it is to write a Python SMTP mailer and feed it with a DataArray from a sheet, text table, database connection or anything else IF you are familiar with the most fundamental basics of Python, the GUI office and the UNO office. It could be even easier without any office suite, e.g. just from plain text, but any simple UNO object that provides a DataArray or RowSet will work in similar ways.

Most "VBA experts" on this forum do fail because they do not know any Basic language beyond macro recorders and copy&pasting other peoples code. Because they never understood how to fabricate a serial email letter in the GUI of this office suite they can not understand how to do the same with UNO even when they have some example code. They always want other people to write full solutions where they only need to fill in their SMTP credentials.

If you are not interested in this office suite and if you are not familiar with an outdated lingo from the 90ies (Basic) and if you think that UNO is just an organization in New York, you are still able to do fairly complex stuff if you only know a real programming language like Python or Java or anything that uses COM on a Window machine and you need some of the most trivial UNO snippets that get you a DataArray. But you need to know something.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Calc, Python] Send Mail

Post by robleyd »

I've not worked with python before, but a few moments with Mr Duck gave me info about time.sleep() function.

A couple of added lines to Villeroy's python script should do what you need; any errors are not intended but you may keep them :) I've commented where the additions are.

Code: Select all

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import smtplib
## Added to allow use of sleep() ##
import time
## End added for sleep() ##
from email.mime.text import MIMEText

########### SMTP-Settings ##############################
gSMTP = "securesmtp.example.com"
gSMTPport = 587
gSender = "myself@example.com"
gUser = gSender #or whatever log-in name
gPasswort = "secret"
########################################################

def getServer():
    server = smtplib.SMTP(gSMTP, gSMTPport)
    server.ehlo()
    server.starttls()
    server.ehlo()
    server.login(gUser, gPasswort)
    return server

def composeMail(addr, subj, body):
    mail = MIMEText(body)
    mail['From'] = gSender
    mail['Subject'] = subj
    mail['To'] = addr
    return mail
    

def sendMailsFromRangeSelection():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.getCurrentSelection()
    try:
        da = sel.getDataArray()
    except:
        raise('Select a sheet cell range with 3 columns for receipient, subject body in that order')
        return
    try:
        server = getServer()
    except:
        raise('Sever connection failed')
    else:
        for arow in da:
            mail = composeMail(arow[0], arow[1], arow[2])
            server.sendmail(gSender, arow[0], mail.as_string())
            sleep(5)  ## Add 5 second delay between emails ##
    server.quit()

g_exportedScripts = sendMailsFromRangeSelection,
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
balmerhevi
Posts: 2
Joined: Sat Nov 12, 2016 7:58 am

Re: [Calc, Python] Send Mail

Post by balmerhevi »

Sometimes when you send [url=http://net-informations.com/python/net/email.htm]email[/url] using gmail address you can see some error message like this:

raise SMTPAuthenticationError(code, resp)
smtplib.SMTPAuthenticationError.....

It is because Google blocks sign-in attempts from apps which do not use modern security standards (mentioned on their support page). You can however, turn on/off this safety feature by going to the link below:

Go to this link and select Turn On

https://www.google.com/settings/security/lesssecureapps

Moreover google block an ip when you try to send a email since a unusual location, so, you can unblock in the next link

https://support.google.com/accounts/answer/6009563

and clicked in

accounts.google.com/DisplayUnlockCaptcha .
OpenOffice 3.1 on Windows Vista
Post Reply