How can I send emails of matching filenames to dataframe values

How can I send emails of matching filenames to dataframe values in Python?

I'm interested in sending emails via smtplib package based on the below conditions:-

Emails and names of files are in a spreadsheet. They are as below:-

Name Email
1001 xx@gmail.com   
1002 yy@xxx.com
1003 zz@yyy.com

The "Name" column name should match files in a folder. 2. Main idea is to compare filenames in the folder to the name in the spreadsheet and if a match exists, then the file in the folder is sent to a matching email address. For example, if 1001.csv and 1002.csv exist in the folder, then 1001.csv will be sent to xx@gmail.com and 1002.csv sent to yy@xxx.com.

import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# Spreadsheet with emails and  names
email_list = pd.read_excel(r'xxxxx\Email.xlsx')
files1 = glob.glob('Folder_xxx/*.xlsx') #Folder with files to be sent as attachments

# getting the names and the emails
names = email_list['Name']
emails = email_list['Email']
 
for i in range(len(emails)): # iterate through the records
 
    # for every record get the name and the email addresses
    name = names[i]
    email = emails[i]
   
    #Some  help needed from here I believe
    while name == os.path.xxxx:
         smtp_ssl_host = 'smtp.gmail.com'
         smtp_ssl_port = 465
         email_from = "xxx@xxx.com"
         email_pass = "xxxx"
         email_to = email

         filename = (filename)+.csv #Get the filename  matching the name in the while condition. Help on this.
         msg2 = MIMEMultipart()
         msg2['Subject'] = "Present Record(s)"
         msg2['From'] = email_from
         msg2['To'] = email
         fo=open(filename,'rb')
         attach = email.mime.application.MIMEApplication(fo.read(),_subtype="xlsx")
         fo.close()
         attach.add_header('Content-Disposition','attachment',filename=filename)
         msg.attach(attach)
         s2 = smtplib.SMTP_SSL(smtp_ssl_host, smtp_ssl_port)
         s2.login(email_from, email_pass)  
         s2.send_message(msg)
         s2.quit()

Answer

To send emails based on the conditions you've outlined using the smtplib package, let's break down the requirements and improve the code step-by-step:

Key Points:

Email List: The emails and file names are in a spreadsheet, and you need to map the Name from the spreadsheet to the corresponding filenames in a folder.
Sending Emails: For each Name in the spreadsheet, you should check if a file with the corresponding name exists in the folder. If it does, attach that file to an email and send it to the appropriate recipient.

Step-by-Step Explanation:

Step 1: Read the spreadsheet to get the email list and associated names.
Step 2: Loop through the list and check for corresponding files in the folder.
Step 3: For each file found, send it as an attachment via smtplib.

Updated Code:

import pandas as pd
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
import os
import glob

# Load the spreadsheet with emails and names
email_list = pd.read_excel(r'xxxxx\Email.xlsx')

# Folder where files are stored
folder_path = 'Folder_xxx/'

# Getting the names and the emails
names = email_list['Name']
emails = email_list['Email']

# Define your email credentials
smtp_ssl_host = 'smtp.gmail.com'
smtp_ssl_port = 465
email_from = "xxx@xxx.com"
email_pass = "xxxx"

# Loop through each row in the email list
for i in range(len(emails)):
    name = str(names[i])  # Convert name to string to match filename format
    email = emails[i]
    
    # Construct the filename that should match the name
    filename = folder_path + name + '.csv'  # Assuming the file is a .csv
    
    # Check if the file exists
    if os.path.exists(filename):
        # Create the email message
        msg = MIMEMultipart()
        msg['Subject'] = "Present Record(s)"
        msg['From'] = email_from
        msg['To'] = email
        
        # Attach the file to the email
        with open(filename, 'rb') as fo:
            attach = MIMEApplication(fo.read(), _subtype="csv")  # Assuming .csv files
            attach.add_header('Content-Disposition', 'attachment', filename=os.path.basename(filename))
            msg.attach(attach)
        
        # Send the email
        try:
            with smtplib.SMTP_SSL(smtp_ssl_host, smtp_ssl_port) as server:
                server.login(email_from, email_pass)
                server.send_message(msg)
            print(f"Email sent to {email} with file {filename}")
        except Exception as e:
            print(f"Failed to send email to {email} due to: {e}")
    else:
        print(f"File {filename} not found. Skipping email to {email}")

Explanation:

Reading the Spreadsheet:
- pd.read_excel(r'xxxxx\Email.xlsx') loads the Excel file containing the names and emails.
Folder Path:
- folder_path = 'Folder_xxx/' specifies the directory where the CSV files are stored.
Loop through the Emails:
- We loop through the email list with for i in range(len(emails)) and get the corresponding name and email for each entry.
File Matching:
- filename = folder_path + name + '.csv' constructs the full path to the file, appending .csv to the name from the spreadsheet.
- if os.path.exists(filename) checks whether the file exists in the folder.
Creating the Email:
- MIMEMultipart() creates a message container for the email.
- The file is attached with MIMEApplication after opening it in binary mode ('rb').
Sending the Email:
- smtplib.SMTP_SSL() creates a secure connection to the SMTP server.
- The email is sent with server.send_message(msg).
Error Handling:
- The try...except block ensures that if sending an email fails, an error message is printed.

Additional Notes:

SMTP Login Credentials:
- Replace email_from and email_pass with the actual credentials. For Gmail, if you're using Gmail 2-factor authentication, you may need to generate an "App Password" rather than using your regular Gmail password.
Error Handling:
- If any file is missing or the email fails to send, it will print a message saying so. You can modify this to log the error if needed.
File Formats:
- If your files are not CSVs or have different extensions, you can modify the filename and _subtype="csv" accordingly.
Security:
- Be cautious when hardcoding sensitive information like email passwords. A better approach would be to use environment variables or a secure password manager.
Dependencies:
- Ensure you have the required libraries installed:
```
pip install pandas openpyxl
```

Let me know if you need any further clarifications or modifications!