Code examples (mail, excel) for using Python to automate office work

pip install openpyxl pandas python-dotenv

import smtplib
from  import MIMEText
from  import MIMEMultipart
 
def send_email(sender_email, sender_password, recipient_email, subject, body):
    # Create a mail object    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = recipient_email
    msg['Subject'] = subject
    (MIMEText(body, 'plain'))
 
    # Connect to the SMTP server and send emails    try:
        with ('', 587) as server:
            ()
            (sender_email, sender_password)
            server.send_message(msg)
        print("The email was sent successfully!")
    except Exception as e:
        print(f"Email sending failed：{e}")
 
# Call examplesend_email(
    sender_email='your_email@',
    sender_password='your_app_password',
    recipient_email='recipient@',
    subject='Test email',
    body='This is a test email sent through Python.  '
)

import imaplib
import email
 
def fetch_emails(email_address, password):
    try:
        # Connect to the IMAP server        with imaplib.IMAP4_SSL('') as mail:
            (email_address, password)
            ('inbox')  # Select Inbox 
            # Search for unread emails            status, messages = (None, 'UNSEEN')
            for num in messages[0].split():
                status, msg_data = (num, '(RFC822)')
                for response_part in msg_data:
                    if isinstance(response_part, tuple):
                        msg = email.message_from_bytes(response_part[1])
                        print(f"From: {msg['from']}")
                        print(f"theme: {msg['subject']}")
                        if msg.is_multipart():
                            for part in ():
                                if part.get_content_type() == 'text/plain':
                                    print(f"content: {part.get_payload(decode=True).decode()}")
                        else:
                            print(f"content: {msg.get_payload(decode=True).decode()}")
    except Exception as e:
        print(f"Mail reading failed：{e}")
 
# Call examplefetch_emails('your_email@', 'your_app_password')

import openpyxl
 
# Open Excel fileworkbook = openpyxl.load_workbook('')
sheet = 
 
# Read datafor row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
    print([ for cell in row])
 
# Write datasheet['A6'] = 'New Data'
('example_updated.xlsx')
print("Excel file updated!")

import pandas as pd
 
# Read Excel filesdata = pd.read_excel('')
 
# Print the first five elements of dataprint(())
 
# Data Processingdata['Total Score'] = data['math'] + data['English'] + data['science']
print(data)
 
# Save the resultsdata.to_excel('', index=False)
print("The data processing is completed and saved!")

import os
import imaplib
import email
import pandas as pd
from  import MIMEText
from  import MIMEMultipart
import smtplib
 
def fetch_and_process_email(email_address, password):
    # Connect to IMAP    with imaplib.IMAP4_SSL('') as mail:
        (email_address, password)
        ('inbox')
 
        # Search for emails with attachments        status, messages = (None, 'ALL')
        for num in messages[0].split():
            status, msg_data = (num, '(RFC822)')
            for response_part in msg_data:
                if isinstance(response_part, tuple):
                    msg = email.message_from_bytes(response_part[1])
                    if msg.is_multipart():
                        for part in ():
                            if part.get_filename():  # Find the attachment                                file_path = ((), part.get_filename())
                                with open(file_path, 'wb') as f:
                                    (part.get_payload(decode=True))
                                print(f"Attachment saved: {file_path}")
 
                                # Process attachment data                                data = pd.read_excel(file_path)
                                data['Total Score'] = (axis=1)
                                processed_path = ''
                                data.to_excel(processed_path, index=False)
                                print("Data processing is completed")
 
                                # Return processing results                                send_email(
                                    sender_email=email_address,
                                    sender_password=password,
                                    recipient_email=msg['from'],
                                    subject='Data processing results',
                                    body='The attachment has been processed, please check it.  ',
                                    attachment_path=processed_path
                                )
 
def send_email(sender_email, sender_password, recipient_email, subject, body, attachment_path):
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = recipient_email
    msg['Subject'] = subject
    (MIMEText(body, 'plain'))
 
    # Add attachment    with open(attachment_path, 'rb') as f:
        attachment = ('application', 'octet-stream')
        attachment.set_payload(())
        .encode_base64(attachment)
        attachment.add_header('Content-Disposition', f'attachment; filename={(attachment_path)}')
        (attachment)
 
    # Send email    with ('', 587) as server:
        ()
        (sender_email, sender_password)
        server.send_message(msg)
    print("The email has been sent!")
 
# Call examplefetch_and_process_email('your_email@', 'your_app_password')