Hi,
I have been trying and search online yet couldnt solve this issue so please someone point out the issue or illuminate me for the below code noting that i tried simpler download code it worked but the idea is i provided hardcoded absolute path while in this code dynamic used os lib and then moved to pathlib i know they are kind of similar in some ways but just was trying since in logs said path doesn’t exist which was blowing my mind thinking
kindly advice
import win32com.client
import openpyxl
import datetime
import os
import re
import logging
from pathlib import Path
#Get the current date and time once
now = datetime.datetime.now()
# Generate the filename dynamically
current_month_year = now.strftime("%B_%Y")
logging.info(f"current_month_year: {current_month_year}")
current_month_folder = now.strftime("%b_follow_up_Folder")
logging.info(f"current_month_folder: {current_month_folder}")
base_path = Path(current_month_folder)
logging.info(f"base_path: {base_path}")
if not base_path.exists():
logging.info(f"Creating directory: {base_path}")
base_path.mkdir(parents=True, exist_ok=True)
filename = base_path / f"Follow_Up_{current_month_year}.xlsx"
logging.info(f"Filename: {filename}")
@log_function_call
def sanitize_file(filename):
return re.sub(r'[<>:"/\\|?*]', '-', filename)
@log_function_call
def sanitize_sub(filename):
return re.sub(r'[<>"/\\|?*]', '', filename)
@log_function_call
def connect_to_outlook(folder_name):
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
if folder_name == "inbox":
inbox = outlook.Folders[my_address].Folders["Inbox"]
else:
inbox = outlook.Folders[my_address].Folders["Inbox"].Folders[folder_name]
print(f"\nConnected to folder: {folder_name} in {my_address}\n")
return inbox
@log_function_call
def filter_emails_by_sender(inbox, sender_email):
now = datetime.datetime.now()
start_of_day = datetime.datetime(now.year, now.month, now.day)
filter_string = f"[SenderEmailAddress]='{sender_email}' AND [ReceivedTime] >= '{start_of_day.strftime('%m/%d/%Y %H:%M %p')}' AND [ReceivedTime] <= '{now.strftime('%m/%d/%Y %H:%M %p')}'"
messages = inbox.Items
filtered_messages = messages.Restrict(filter_string)
filtered_messages.Sort("[ReceivedTime]", True)
print(f"Filtered {len(filtered_messages)} emails from {sender_email}\n")
return filtered_messages
@log_function_call
def download_attachment(attachment, output_dir):
try:
if not output_dir.exists():
output_dir.mkdir(parents=True, exist_ok=True)
logging.info(f"Created directory: {output_dir}")
attachment_file_path = output_dir / sanitize_file(attachment.FileName)
attachment.SaveAsFile(str(attachment_file_path))
logging.info(f"Downloaded attachment: {attachment.FileName} to {attachment_file_path}")
except Exception as e:
logging.error(f"Error downloading attachment: {attachment.FileName} - {e}")
@log_function_call
def extract_email_data(message):
body = message.Body.strip()
sent_on = message.SentOn.replace(tzinfo=None)
subject = sanitize_sub(message.Subject.strip())
found = "No"
attachment_path = ""
try:
if message.Attachments.Count > 0:
found = "Yes"
sanitized_subject = sanitize_file(subject)
attachment_dir = base_path / f"attach_{sanitized_subject}"
if not attachment_dir.exists():
attachment_dir.mkdir(parents=True, exist_ok=True)
logging.info(f"Created directory: {attachment_dir}")
logging.info(f"Attachment directory: {attachment_dir}")
for attachment in message.Attachments:
attachment_filename = sanitize_file(attachment.FileName)
attachment_path = attachment_dir / attachment_filename
logging.info(f"Attempting to save attachment: {attachment_filename} to {attachment_path}")
try:
download_attachment(attachment, attachment_path)
logging.info(f"Successfully saved attachment to: {attachment_path}")
except Exception as e:
logging.error(f"Failed to save attachment: {attachment_filename} to {attachment_path}. Error: {e}")
except Exception as e:
logging.error(f"Error saving email attachment: {e}")
return body, sent_on, subject, found, attachment_path
@log_function_call
def create_or_append_to_excel(filename, data):
try:
# file_path = Path(filename)
# workbook = openpyxl.load_workbook(filename) if file_path.exists() else openpyxl.Workbook()
workbook = openpyxl.load_workbook(filename) if os.path.exists(filename) else openpyxl.Workbook()
except Exception as e:
print(f"Error loading workbook: {e}. Creating a new workbook."'\n')
workbook = openpyxl.Workbook()
today_str = datetime.datetime.now().strftime("%d-%m-%Y")
sheet_name = f"Work_{today_str}"
if sheet_name not in workbook.sheetnames:
if "Sheet" in workbook.sheetnames:
del workbook["Sheet"]
sheet = workbook.create_sheet(title=sheet_name)
header_row = ["Subject", "Sent On", "Body","Found_Attachment" ,"Attachment_Path"]
for col_num, header_text in enumerate(header_row, 1):
sheet.cell(row=1, column=col_num).value = header_text
sheet.cell(row=1, column=col_num).font = openpyxl.styles.Font(bold=True)
else:
sheet = workbook[sheet_name]
for row_data in data:
row_data = [str(item) if isinstance(item, Path) else item for item in row_data]
sheet.append(row_data)
for col in sheet.columns:
max_length = max(len(str(cell.value)) for cell in col)
sheet.column_dimensions[col[0].column_letter].width = max_length + 2
workbook.save(filename)
2024-11-18 22:36:18,338 - root - ERROR - Error downloading attachment: Screenshot 2024-11-18 114215.jpg - (-2147352567, ‘Exception occurred.’, (4096, ‘Microsoft Outlook’, ‘Cannot save the attachment. Path does not exist. Verify the path is correct.’, None, 0, -2147024893), None)