python script that checks column value in excel sheet and sends mail to that particular person in outlook by the name of distribution list(DL)

Question

I want to write a python script that checks a column value in excel sheet and sends mail to that particular person in outlook by the name of distribution list(DL). I am unable to fetch DL with the following code. Please help me on this.

More details: The sender should be the DL name i.e., DL IQWDL SSDBU NEC Global [email protected] The receiver should an individual name i.e., [email protected]

Code:

    import win32com.client as win32 
    import pandas as pd
    
    #Read the excel file 
    xl = pd.read_excel('C:\\Users\\rahulk\\Downloads\\corepy\\excel_report.xlsx')
    
    #Select the email addresses from a specific column in the Excel file 
    email_addresses = xl['Email'].tolist()
    
    #Connect to Outlook and get the distribution list your text 
    outlook = win32.gencache.EnsureDispatch("Outlook.Application") 
    dl = outlook.Session.GetDefaultFolder(win32.constants.olFolderContacts).GetDistributionListFromID("[email protected]")
  
    #Loop through the email addresses and send the email your text
    for email_address in email_addresses: 
        email = outlook.CreateItem(win32.constants.olMailItem)
        email.To = email_address 
        email.Subject = "Email Subject" 
        email.Body = "Email Body" 
        email.SentOnBehalfOfName = dl.DLName 
        email.Send()
    
    #Release the Outlook instance 
    outlook = None

Answer 1

The Outlook object model doesn't provide the GetDistributionListFromID method for the Folder class. To find an item in the folder you need to use the Find/FindNext or Restrict methods of the Items class. Read more about these methods in the articles I wrote for the technical blog:

• How To: Retrieve Outlook Contact items using Restrict method
• How To: Use Find and FindNext to retrieve Outlook Contact items

Be aware, the To property expects a semicolon-delimited string list of display names for the To recipients for the Outlook item. Otherwise, I'd suggest using the Recipeints.Add method for adding recipients. See How To: Fill TO,CC and BCC fields in Outlook programmatically for more information.