Email Processing MCP Server

This is a complex task involving several steps and technologies. Here's a breakdown of the process and a conceptual outline of how you might implement it, along with considerations for each step: **1. Outlook Email Extraction and Date Filtering:** * **Technology:** Python with libraries like `win32com.client` (for Windows) or `imaplib` (cross-platform, but requires IMAP access enabled in Outlook). * **Process:** 1. **Connect to Outlook:** * **`win32com.client` (Windows):** This is the most direct way to access Outlook on Windows. ```python import win32com.client outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") inbox = outlook.GetDefaultFolder(6) # 6 represents the Inbox messages = inbox.Items ``` * **`imaplib` (Cross-Platform):** Requires IMAP to be enabled in your Outlook account settings. This is more complex to set up but works on macOS and Linux. ```python import imaplib import email # Replace with your Outlook IMAP server and credentials imap_server = "outlook.office365.com" imap_user = "your_email@outlook.com" imap_password = "your_password" mail = imaplib.IMAP4_SSL(imap_server) mail.login(imap_user, imap_password) mail.select("inbox") result, data = mail.search(None, "ALL") # Or use specific search criteria for num in data[0].split(): result, data = mail.fetch(num, "(RFC822)") raw_email = data[0][1] email_message = email.message_from_bytes(raw_email) # Process email_message mail.close() mail.logout() ``` 2. **Date Filtering:** Iterate through the emails and check their sent/received dates. * **`win32com.client`:** ```python import datetime start_date = datetime.datetime(2023, 1, 1) # Example start date end_date = datetime.datetime(2023, 12, 31) # Example end date for message in messages: try: sent_date = message.SentOn.replace(tzinfo=None) # Remove timezone info for comparison if start_date <= sent_date <= end_date: # Process the email print(f"Subject: {message.Subject}, Sent: {sent_date}") except Exception as e: print(f"Error processing email: {e}") ``` * **`imaplib`:** You can use IMAP search criteria for date filtering *before* fetching the emails, which is more efficient. ```python from datetime import date, timedelta today = date.today() yesterday = today - timedelta(days=1) date_str = yesterday.strftime("%d-%b-%Y") # Format required by IMAP result, data = mail.search(None, f'SINCE "{date_str}"') # Search for emails since yesterday ``` 3. **Email Data Extraction:** Extract relevant information from each email: * Subject * Sender * Recipient(s) * Sent/Received Date * Body (plain text and/or HTML) * Attachments (optional) **2. SQLite Database Storage:** * **Technology:** Python's `sqlite3` library. * **Process:** 1. **Connect to SQLite:** ```python import sqlite3 conn = sqlite3.connect('emails.db') cursor = conn.cursor() ``` 2. **Create a Table:** ```python cursor.execute(''' CREATE TABLE IF NOT EXISTS emails ( id INTEGER PRIMARY KEY AUTOINCREMENT, subject TEXT, sender TEXT, recipient TEXT, sent_date DATETIME, body TEXT ) ''') conn.commit() ``` 3. **Insert Email Data:** ```python def insert_email(subject, sender, recipient, sent_date, body): cursor.execute(''' INSERT INTO emails (subject, sender, recipient, sent_date, body) VALUES (?, ?, ?, ?, ?) ''', (subject, sender, recipient, sent_date, body)) conn.commit() # Example usage: insert_email(message.Subject, message.SenderEmailAddress, message.To, message.SentOn, message.Body) ``` 4. **Close the Connection:** ```python conn.close() ``` **3. Vector Embedding Generation:** * **Technology:** A suitable embedding model (e.g., Sentence Transformers, OpenAI's embeddings API, Hugging Face Transformers) and a library to use it. * **Process:** 1. **Choose an Embedding Model:** * **Sentence Transformers:** Good for general-purpose semantic similarity. Easy to use and can run locally. ```python from sentence_transformers import SentenceTransformer model = SentenceTransformer('all-mpnet-base-v2') # Choose a model ``` * **OpenAI Embeddings API:** High-quality embeddings, but requires an OpenAI API key and incurs costs. ```python import openai openai.api_key = "YOUR_OPENAI_API_KEY" def get_embedding(text, model="text-embedding-ada-002"): text = text.replace("\n", " ") return openai.Embedding.create(input = [text], model=model)['data'][0]['embedding'] ``` * **Hugging Face Transformers:** Offers a wide range of models, but can be more complex to set up. 2. **Generate Embeddings:** Create embeddings for the email body (or subject + body). ```python # Using Sentence Transformers email_body = message.Body # Or message.HTMLBody, but clean it first embedding = model.encode(email_body) # Using OpenAI email_body = message.Body embedding = get_embedding(email_body) ``` **4. MongoDB Storage with Vector Search:** * **Technology:** MongoDB with Atlas Vector Search. * **Process:** 1. **Connect to MongoDB:** ```python from pymongo import MongoClient # Replace with your MongoDB connection string client = MongoClient("mongodb+srv://<username>:<password>@<cluster>.mongodb.net/?retryWrites=true&w=majority") db = client["email_database"] collection = db["emails"] ``` 2. **Create a Vector Search Index (if it doesn't exist):** This is crucial for efficient vector search. You'll need to do this in the MongoDB Atlas UI or using the MongoDB shell. The index definition will depend on the embedding dimension of your chosen model. Example: ```javascript // MongoDB Shell db.emails.createIndex( { embedding: "vectorSearch", "indexConfig": { "dimensions": 768, // Replace with the dimension of your embedding model "similarity": "cosine", // Or "euclidean", "dotProduct" "vectorOptions": { "dataType": "float16", // Or "float32" "normalize": true } } }, { "name": "vectorSearchIndex" } ) ``` 3. **Store Email Data and Embeddings:** ```python def insert_email_mongo(subject, sender, recipient, sent_date, body, embedding): email_document = { "subject": subject, "sender": sender, "recipient": recipient, "sent_date": sent_date, "body": body, "embedding": embedding.tolist() # Convert NumPy array to list for MongoDB } collection.insert_one(email_document) # Example usage: insert_email_mongo(message.Subject, message.SenderEmailAddress, message.To, message.SentOn, message.Body, embedding) ``` **5. Semantic Search:** * **Process:** 1. **Get the Search Query:** The user enters a search query. 2. **Generate Embedding for the Query:** Use the *same* embedding model you used for the emails to generate an embedding for the search query. 3. **Perform Vector Search in MongoDB:** Use the `$vectorSearch` aggregation pipeline stage. ```python def search_emails(query, limit=10): query_embedding = model.encode(query).tolist() # Or get_embedding(query) if using OpenAI pipeline = [ { "$vectorSearch": { "index": "vectorSearchIndex", # The name of your vector search index "path": "embedding", "queryVector": query_embedding, "numCandidates": 100, # Adjust as needed "limit": limit } }, { "$project": { "_id": 0, "subject": 1, "body": 1, "score": { "$meta": "vectorSearchScore" } } } ] results = list(collection.aggregate(pipeline)) return results # Example usage: search_results = search_emails("meeting about project updates") for result in search_results: print(f"Subject: {result['subject']}, Score: {result['score']}") print(f"Body: {result['body'][:200]}...") # Print first 200 characters of the body ``` **Code Example (Illustrative - Requires Adaptation):** ```python import win32com.client import sqlite3 from sentence_transformers import SentenceTransformer from pymongo import MongoClient import datetime # --- Configuration --- SQLITE_DB_NAME = 'emails.db' MONGODB_CONNECTION_STRING = "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/?retryWrites=true&w=majority" MONGODB_DATABASE_NAME = "email_database" MONGODB_COLLECTION_NAME = "emails" EMBEDDING_MODEL_NAME = 'all-mpnet-base-v2' # Or 'text-embedding-ada-002' if using OpenAI # --- Initialize Components --- outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") inbox = outlook.GetDefaultFolder(6) messages = inbox.Items sqlite_conn = sqlite3.connect(SQLITE_DB_NAME) sqlite_cursor = sqlite_conn.cursor() model = SentenceTransformer(EMBEDDING_MODEL_NAME) # Or initialize OpenAI mongo_client = MongoClient(MONGODB_CONNECTION_STRING) mongo_db = mongo_client[MONGODB_DATABASE_NAME] mongo_collection = mongo_db[MONGODB_COLLECTION_NAME] # --- Database Setup --- sqlite_cursor.execute(''' CREATE TABLE IF NOT EXISTS emails ( id INTEGER PRIMARY KEY AUTOINCREMENT, subject TEXT, sender TEXT, recipient TEXT, sent_date DATETIME, body TEXT ) ''') sqlite_conn.commit() # --- Email Processing --- start_date = datetime.datetime(2023, 1, 1) end_date = datetime.datetime(2023, 12, 31) for message in messages: try: sent_date = message.SentOn.replace(tzinfo=None) if start_date <= sent_date <= end_date: subject = message.Subject sender = message.SenderEmailAddress recipient = message.To body = message.Body # 1. Store in SQLite sqlite_cursor.execute(''' INSERT INTO emails (subject, sender, recipient, sent_date, body) VALUES (?, ?, ?, ?, ?) ''', (subject, sender, recipient, sent_date, body)) sqlite_conn.commit() # 2. Generate Embedding embedding = model.encode(body) # Or use OpenAI # 3. Store in MongoDB email_document = { "subject": subject, "sender": sender, "recipient": recipient, "sent_date": sent_date, "body": body, "embedding": embedding.tolist() } mongo_collection.insert_one(email_document) print(f"Processed email: {subject}") except Exception as e: print(f"Error processing email: {e}") # --- Cleanup --- sqlite_conn.close() mongo_client.close() print("Email processing complete.") ``` **Key Considerations and Improvements:** * **Error Handling:** Robust error handling is crucial, especially when dealing with external APIs and data sources. Use `try...except` blocks extensively. * **Rate Limiting:** Be mindful of rate limits for the OpenAI API and other services. Implement delays or batch processing to avoid exceeding limits. * **Authentication:** Securely store and manage API keys and database credentials. Avoid hardcoding them directly in your script. Use environment variables or a configuration file. * **Data Cleaning:** Clean the email body before generating embeddings. Remove HTML tags, special characters, and irrelevant content. Consider using libraries like `BeautifulSoup` for HTML parsing. * **Scalability:** For large email datasets, consider using batch processing and asynchronous tasks to improve performance. Libraries like `Celery` or `Dask` can help. * **Incremental Updates:** Implement a mechanism to periodically check for new emails and update the databases and embeddings. You could use a scheduled task or a background process. * **Vector Search Index Optimization:** Experiment with different vector search index configurations (e.g., `numCandidates`, `similarity metric`) to optimize search performance. * **User Interface:** Create a user interface (e.g., using Flask, Django, or Streamlit) to allow users to enter search queries and view the results. * **Security:** If you're handling sensitive email data, implement appropriate security measures to protect the data at rest and in transit. * **Time Zones:** Pay close attention to time zones when filtering emails by date. Ensure that all dates are in the same time zone before comparing them. * **Attachment Handling:** If you need to process attachments, you'll need to extract them from the emails and store them separately. You could also generate embeddings for the attachment content. * **IMAP vs. Exchange Web Services (EWS):** For more advanced Outlook integration, consider using Exchange Web Services (EWS) instead of IMAP. EWS provides more features and better performance, but it's also more complex to set up. There are Python libraries like `exchangelib` that can help. This comprehensive outline should give you a solid foundation for building your email processing and semantic search application. Remember to adapt the code examples to your specific needs and environment. Good luck! **Simplified Chinese Translation of the Key Concepts:** Here's a translation of the key concepts into Simplified Chinese: * **Outlook Email Extraction:** Outlook 邮件提取 (Outlook yóujiàn tíqǔ) * **Date Filtering:** 日期过滤 (rìqí guòlǜ) * **SQLite Database:** SQLite 数据库 (SQLite shùjùkù) * **Vector Embeddings:** 向量嵌入 (xiàngliàng qiànrù) * **Semantic Search:** 语义搜索 (yǔyì sōusuǒ) * **MongoDB:** MongoDB * **Atlas Vector Search:** Atlas 向量搜索 (Atlas xiàngliàng sōusuǒ) * **Sentence Transformers:** 句子转换器 (jùzi zhuǎnhuànqì) * **OpenAI Embeddings API:** OpenAI 嵌入 API (OpenAI qiànrù API) * **Hugging Face Transformers:** Hugging Face 转换器 (Hugging Face zhuǎnhuànqì) * **Email Body:** 邮件正文 (yóujiàn zhèngwén) * **Search Query:** 搜索查询 (sōusuǒ cháxún) * **Aggregation Pipeline:** 聚合管道 (jùhé guǎndào) * **Index:** 索引 (suǒyǐn) * **Connection String:** 连接字符串 (liánjiē zìfúchuàn) * **Rate Limiting:** 速率限制 (sùlǜ xiànzhì) * **Data Cleaning:** 数据清洗 (shùjù qīngxǐ) * **Scalability:** 可扩展性 (kě kuòzhǎn xìng) * **Incremental Updates:** 增量更新 (zēngliàng gēngxīn) * **User Interface:** 用户界面 (yònghù jièmiàn) * **Security:** 安全性 (ānquán xìng) * **Time Zones:** 时区 (shíqū) * **Attachment Handling:** 附件处理 (fùjiàn chǔlǐ) * **Exchange Web Services (EWS):** Exchange Web 服务 (EWS)