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)
Input:
0 [email protected] 116651 Ticket Discription 1
1 [email protected] 116650 Ticket Discription 2
2 NaN 116649 Ticket Discription 3
3 NaN 116648 Ticket Discription 4
4 NaN 116647 Ticket Discription 5
5 NaN 116646 Ticket Discription 6
6 NaN 116645 Ticket Discription 7
7 NaN 116644 Ticket Discription 8
8 NaN 116642 Ticket Discription 9
9 NaN 116641 Ticket Discription 10
10 NaN 116640 Ticket Discription 11
11 NaN 116639 Ticket Discription 12
What I tried with the help of @Pablo Gutierrez Marques
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] is 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()
The 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['[email protected]'].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)):
# if the email is not valid (i.e. NaN), skip the sending for this ticket
if emails[i] is 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()
# 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()
