Click here to Skip to main content
14,696,277 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have the below input, where I have limited email Ids, but many ticket IDs. I would like to know how to write a condition that would enable me to send tickets to whatever email IDs are available. Plus would also want to install a condition that would say that if email IDs are more than tickets, then the recipients would get a "No tickets available" message.

I get the following error. "com_error: (-2147352567, 'Exception occurred.', (4096, 'Microsoft Outlook', 'Outlook does not recognize one or more names. ', None, 0, -2147467259), None).


What I have tried:

What I tried:

    e = pd.read_csv('combo.csv')
    emails = e['1'].values
    ticket_number = e['2'].values
    desc_value = e['3'].values 
    for i in range(len(emails)):
    # if the email is not valid (i.e. NaN), skip the sending for this ticket
      if emails[i] == None:
        continue
        outlook = win32com.client.Dispatch("Outlook.Application")
        message = f'Hello the ticket number {ticket_number[i]} with the Description {desc_value[i]} has been assigned to ' \
                  f'you. '
        mail = outlook.CreateItem(0)
        mail.To = emails[i]
        mail.Subject = 'This is a test'
        mail.HTMLBody = message
        mail.Send()


Entire Code:

    import pandas as pd
    from selenium import webdriver
    from getpass import getpass
    from openpyxl import load_workbook
    import re
    import os
    import fnmatch
    from win32com.client import Dispatch
    import getpass
    import datetime
    import time
    import datetime as dt
    import csv
    import win32com.client
    import shutil
    
    # login to the website
    
    user_name = "username"
    password = "password"
    
    chrome_options = webdriver.ChromeOptions()
    chrome_options.add_argument("--incognito")
    
    driver = webdriver.Chrome(".\chromedriver", options=chrome_options)
    driver.get("http://drtracker.roc.com/")
    
    user_name_textbox = driver.find_element_by_id("user")
    user_name_textbox.send_keys(user_name)
    
    password_textbox = driver.find_element_by_id("pw")
    password_textbox.send_keys(password)
    
    login_button = driver.find_element_by_class_name("btn-lg")
    login_button.submit()
    
    download_csv = driver.find_element_by_link_text("export to excel")
    download_csv.click()
    
    driver.close()
    
    # move files from downloads to the working folder
    
    source = os.listdir("C:/Users/u0167154/Downloads")
    destination = "C:/Users/u0167154/PycharmProjects/Scraping"
    for files in source:
        if files.endswith(".csv"):
            shutil.move(files, destination)
    
    # Extract emails from folder in outlook
    
    Todays_Mail = dt.datetime.now() - dt.timedelta(hours=24)
    Todays_Mail = Todays_Mail.strftime('%m/%d/%Y %H:%M %p')
    # Connect to Outlook inbox
    outlook = Dispatch("Outlook.Application")
    mapi = outlook.GetNamespace("MAPI")
    
    your_folder = mapi.Folders['sunith.kokku@email.com'].Folders['Blah']
    
    blah_inbox = your_folder.Items
    
    blah_inbox = blah_inbox.Restrict("[ReceivedTime] >= '" + Todays_Mail + "'")
    
    f = open("email.csv", "w")
    f.write('Index,Emails\n')
    
    index = 0
    for message in blah_inbox:
        if message.Class == 43:
            if message.SenderEmailType == 'EX':
                print(message.Sender.GetExchangeUser().PrimarySmtpAddress)
                f.write(str(index) + ',' + message.Sender.GetExchangeUser().PrimarySmtpAddress + '\n')
                index = index + 1
    
            else:
                print(message.SenderEmailAddress)
    f.close()
    
    # pandas code to import the data, extract the IDs and Email and then combine into one csv.
    
    for file in os.listdir('./'):
        if fnmatch.fnmatch(file, '*.csv'):
            os.rename(file, 'data.csv')
            print(file)
    
    df = pd.read_csv('data.csv')
    print(df[['id', 'desc', 'assigned to']])
    df = df.fillna("NA")
    cols = ['id', 'desc', 'assigned to']
    df_list = df[cols]
    F = df_list[0:49]
    F.to_csv('extracted_ids.csv')
    
    # joining the emails and the extracted ticket IDs together into a combo file
    
    email_ids = pd.read_csv('email.csv')
    tickets = pd.read_csv('extracted_ids.csv')
    
    df1 = pd.DataFrame(email_ids)
    df2 = pd.DataFrame(tickets)
    df2.drop(df2.columns[[0]], axis=1, inplace=True)
    
    combo = df1.join(df2)
    combo.drop(combo.columns[[0]], axis=1, inplace=True)
    # nan_value = float("NaN")
    combo.to_csv('combo.csv')
    e = pd.read_csv('combo.csv')
    
    # send the mails
    
    e = pd.read_csv('combo.csv')
    emails = e['1'].values
    ticket_number = e['2'].values
    desc_value = e['3'].values
    for i in range(len(emails)):
        outlook = win32com.client.Dispatch("Outlook.Application")
        message = f'Hello the ticket number {ticket_number[i]} with the Description {desc_value[i]} has been assigned to ' \
                  f'you. '
        mail = outlook.CreateItem(0)
        mail.To = emails[i]
        mail.Subject = 'This is a test'
        mail.HTMLBody = message
        mail.Send()
    
    # save to master file
    
    df3 = pd.read_csv('combo.csv')
    writer = pd.ExcelWriter('master.xlsx', engine='openpyxl')
    # try to open an existing workbook
    writer.book = load_workbook('master.xlsx')
    # copy existing sheets
    writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
    # read existing file
    reader = pd.read_excel(r'master.xlsx')
    # write out the new sheet
    df3.to_excel(writer, index=False, header=False, startrow=len(reader) + 1)
    
    writer.close()
    
    # delete all CSV files
    
    dir_name = "C:/Users/u0167154/PycharmProjects/Scraping"
    delete_all = os.listdir(dir_name)
    
    for item in delete_all:
        if item.endswith(".csv"):
            os.remove(os.path.join(dir_name, item))
    
    f.close()
Posted
Comments
Richard MacCutchan 21-Feb-20 10:05am
   
The error message has nothing to do with your question. It relates to information you are using in your call to the Outlook interface.
sunith kumar 21-Feb-20 11:53am
   
The error message is due to the fact that there are only two emails for 10 tickets, if there were 10 emails and 10 tickets then there would not be any error thrown by Outlook. I want to know if there is a way to loop over the tickets irrespective of the number of email values available.
Richard MacCutchan 21-Feb-20 12:16pm
   
Yes, of course, there is. It is about creating the decision set for your program. You need to decide which set has precedence and loop over that, selecting from the other set until the loop terminates. But the relationship between tickets and emails is not clear.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900