<jupyter_start><jupyter_text>Sending emails with students' results<jupyter_code>import smtplib
import base64
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
def send_mail(address, subject, msg='test', msgHTML = '<p>test</p>'):

    user = '<EMAIL>'
    passwd = base64.b64decode(b'VEUxcjFrLmEuMC5oLmM=')
    pwd = "".join( chr(x) for x in passwd)

    message = MIMEMultipart("alternative")
    message["Subject"] = subject
    message["From"] = user
    message["To"] = address

    # Turn these into plain/html MIMEText objects
    part1 = MIMEText(msg, "plain")
    part2 = MIMEText(msgHTML, "html")

    # Add HTML/plain-text parts to MIMEMultipart message
    # The email client will try to render the last part first
    message.attach(part1)
    message.attach(part2)

    mailserver = smtplib.SMTP('smtp.office365.com',587)
    mailserver.ehlo()
    mailserver.starttls()
    mailserver.login(user, pwd)
    header = 'To:' + address + '\n' 
    header += 'From: ' + user + '\n' 
    header += 'Subject:' + subject +'\n'
    print(header)
    msgbody = header + '\n' + msg + '\n\n'
    mailserver.sendmail(user, address, message.as_string())
    mailserver.quit()
send_mail('<EMAIL>', 'Test igjen', 'Hej')<jupyter_output>To:<EMAIL>
From: <EMAIL>
Subject:Test igjen<jupyter_text>Load excel file extract relevant rows and send in email<jupyter_code>import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
fname = '/home/kjartan/Dropbox/undervisning/tec/MR2007/HT2019/MR2007-grades.xlsx'
fname = '/home/kjartan/tec/mr3028/ht2019/MR3028-grades.xls'
fname = '/home/kjartan/Dropbox/undervisning/tec/TE3045/HT2019/TE3045-grades.xls'
fname = '/home/kjartan/Dropbox/undervisning/tec/MR2015/spring2020/MR2015-2-grades.xls'
fname = '/home/kjartan/Dropbox/undervisning/tec/MR2015/spring2020/MR2015-1-grades.xls'
subject = 'Final grade for Process Automation Laboratory'
df = pd.read_excel(fname, sheet_name='Sheet1')

print("Column headings:")
print(df.columns)
df['Matrícula']
df.loc[(df.Matrícula == "A01376209") | (df.Matrícula == "Weight")]
from IPython.display import HTML
tabellinfo = '<pd><table>'
for key, desc in [('T1', 'Report intro-lab'),
                  ('T2', 'Report lab 2 and 3, circuits and dynamics'),
                  ('T3', 'Report lab 4 and 5, PID control'),
                  ('T4', 'Report lab 6, digital logic'),
                  ('T5', 'Report lab 7, pneumatics'),
                  ('T6', 'Report lab 8, ladder diagrams electro-pneumatics'),
                  ('T7', 'PI & D video assignment')]:
    tabellinfo += '<tr><td>' + key + '</td>' + '<td>' + desc + '</td></tr>'
tabellinfo += '</table></pd>'
display(HTML(tabellinfo))
for index, row in df.iterrows():
    m = row['Matrícula']
    name = row['Nombre']
    if m[0] == 'A':
        address = m + '@itesm.mx'
        #print(address)
        #address = '<EMAIL>'
        html = "<p>Hi,</p> "
        #html += "<p>Sorry for spamming. There was a mistake in the calculation of the final grade. See updated table below.</p>"
        html += "<p>Thanks a lot for your effort during the course this semester!</p>"
        html += "<p>If you want to discuss the grade, please contact me using Remind or email. </p>"
        html += "<p> Take care! Best regards, Kjartan</p><p></p>"
        html += "<p>These are your results. The first row shows the weighting. The final grade is in the second row, last column</p>"
        html += df.loc[(df.Matrícula == m) | (df.Matrícula == "Weight")].to_html()
        html += tabellinfo
        alt = 'Your results'
        send_mail(address, subject, alt, html)<jupyter_output>To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>
From: <EMAIL>
Subject:Final grade for Process Automation Laboratory

To:<EMAIL>[...]<jupyter_text>Test<jupyter_code>html = "<p>Hi,</p> <p>Thanks a lot for your effort in the course this semester!</p>"
html += "<p>These are your results</p>"
html += df.loc[(df.Matrícula == "A01372526") | (df.Matrícula == "Weight")].to_html()
alt = 'Your results'
send_mail('<EMAIL>', 'Final grade', alt, html)<jupyter_output>To:<EMAIL>
From: <EMAIL>
Subject:Final grade
{'fraction_non_alphanumeric': 0.14622543950361944, 'fraction_numerical': 0.02295760082730093, 'mean_word_length': 3.889787664307381, 'pattern_counts': {'":': 0, '<': 71, '<?xml version=': 0, '>': 71, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '90357', 'license': 'MIT', 'n_tokens_mistral': 1744, 'n_tokens_neox': 1626, 'n_words': 452, 'path': 'misc-notebooks/.ipynb_checkpoints/send-mail-MR2015-spring-2020-checkpoint.ipynb', 'repo_name': 'kjartan-at-tec/mr2007-computerized-control', 'size': '5146'}