使用Python递归向有限的电子邮件发送多个值？

Question

我有下面的输入，其中我有有限的电子邮件ID，但许多票证ID。我想知道如何写一个条件，使我能够发送门票到任何电子邮件ID可用。Plus还希望安装一个条件，即如果电子邮件ID超过票证，则收件人将收到“无可用票证”消息

输入：

0   sunith.kokku@email.com  116651  Ticket Discription 1
1   sunith.kokku@email.com  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

我尝试的是：

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()

完整代码：

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()

Answer 1

简单的解决方案是跳过NAN的循环：

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] == 'NaN':
        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()

如您在评论中所述，如果要将未分配的项目分配给现有电子邮件地址，您可以先检查所有可用的地址，然后随机分配必要的地址

import random

e = pd.read_csv('combo.csv')
emails = e['1'].values
ticket_number = e['2'].values
desc_value = e['3'].values

# gather the existing email addresses
valid_emails = {email for email in emails if email != 'NaN'}

# replace the NaNs with a random valid email address
assigned_emails = [(email if email != 'NaN' else random.choice(list(valid_emails)))
    for email in emails]
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 = assigned_emails[i]  # Here you used the assigned emails, not the original ones
    mail.Subject = 'This is a test'
    mail.HTMLBody = message
    mail.Send()

我希望这有帮助