[Calc, Python] Send Mail

Creating Extension - 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 forum is not for asking questions about writing your own macros.

[Calc, Python] Send Mail

Postby Villeroy » Sat Sep 09, 2017 5:32 pm

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.
Attachments
send_mail.py.zip
(757 Bytes) Downloaded 37 times
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Python] Send Mail

Postby thefixer » Fri Oct 06, 2017 7:29 pm

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
thefixer
 
Posts: 1
Joined: Fri Oct 06, 2017 7:18 pm

Re: [Calc, Python] Send Mail

Postby Villeroy » Sat Oct 07, 2017 1:14 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Python] Send Mail

Postby robleyd » Sat Oct 07, 2017 1:46 pm

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,
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.4 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1094
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 3 guests