[Calc, Python] Send Mail

[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.

1a. Extract the Python module to <profile>/Scripts/python/
1b. Edit the SMTP settings on top of the module. You can open Python modules with Writer but do not change the line indentation. Just edit the double-quoted values between the hash fences.
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.

[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

[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.

[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   Expand viewCollapse view

#!/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,


[balmerhevi]

Sometimes when you send email 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 .