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

Question

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

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

if files are in the same folder, you can try something like this

import os
from collections import ChainMap
folder_path="." # your folder path
my_files=[{each_file.split(".")[0]:each_file} for each_file in os.listdir(folder_path) if each_file.endswith(".csv")]
my_files_dict = dict(ChainMap(*my_files))

and then in your for loop:

if my_files_dict.get(name):
    print(f"file found:{my_files_dict.get(name)}") # attach this file : my_files_dict.get(name)