win32com.client to send different attachments instead of fixed file path

Question

I am able to send email to different recipients on outlook with below script for single attachment, but if I try to send different attachments to each user using for loop, then it fails.

Currently the script is using attachment = r'C:\Users\roy\Royfile.csv'. But I want attachment = file, so that the attachment changes in each for loop for different users. This part is not working.

Different files for different users, example Royfile.csv below. But there are 50 more such files.

Folder        FolderOwner    EmailAddress      AttachmentPath
C:\folder1\   Roy            [email protected]     Royfile.csv
D:\folder2\   Roy            [email protected]     Royfile.csv

2nd file in same folder Jackfile.csv:

Folder        FolderOwner    EmailAddress      AttachmentPath  
C:\folder3\   Jack            [email protected]   Jackfile.csv
D:\folder4\   Jack            [email protected]   Jackfile.csv 

3rd file for example Mandyfile.csv. And same way total 50 files for 50 users in same folder.

Folder        FolderOwner    EmailAddress        AttachmentPath
C:\folder5\   Mandy            [email protected]   Mandyfile.csv
D:\folder6\   Mandy            [email protected]   Mandyfile.csv

Python Script

import glob, as
import win32com.client as win32
import pandas as pd

for file in glob.glob("*file.csv"):
    print(file)

    email_list = pd.read_csv(file)
    names = email_list['FolderOwner']
    emails = email_list['EmailAddress']
    attachments = email_list['AttachmentPath']

    for i in range(len(emails)):
       print(file)
       name = names[i]
       email = emails[i]
       attachment = r'{}.csv'.format(attachments)
       with open(attachment, 'r') as my_attachment:
          myfile = my_attachment.read()
    
       outlook = win32.Dispatch('outlook.application')
       mail = outlook.CreateItem(0)
       mail.To = email
       mail.Subject = 'Message subject'
       mail.Body = 'Hello ' + name
       mail.Attachments.Add(attachment)
       mail.Send()
       break

Current output of the script if I remove the attachment part:

Royfile.csv
Royfile.csv
Jackfile.csv
Jackfile.csv
Mandyfile.csv
Mandyfile.csv
...
..
.

Struggling now with what needs to be for attachment = ???. So that each file gets sent to 50 users.

Answer 1

Found answer for my question finally, below is full code. The error was coming, as there was PATH missing. win32com lib need full path even if the script is running in same folder as the attachments. works perfectly now. :)

import glob, as
import win32com.client as win32
import pandas as pd

for file in glob.glob("*file.csv"):
    print(file)

    email_list = pd.read_csv(file)
    names = email_list['FolderOwner']
    emails = email_list['EmailAddress']
    attachments = email_list['AttachmentPath']
    PATH = "C:\\Users\\roy\\myfolder\\"

    for i in range(len(emails)):
       print("Sending email with " + file)
       name = names[i]
       email = emails[i]
       attachment = attachments[i]
       attachment1 = PATH + attachment 
       with open(attachment1, 'r') as my_attachment:
          myfile = my_attachment.read()
    
       outlook = win32.Dispatch('outlook.application')
       mail = outlook.CreateItem(0)
       mail.To = email
       mail.Subject = 'Message subject'
       mail.Body = 'Hello ' + name
       mail.Attachments.Add(attachment1)
       mail.Send()
       break