如何通过SQL连接Outlook，远程查询指定文件夹特定主题邮件并统计数量

Great question! For connecting to a non-local Outlook mailbox, querying specific folders for targeted subject emails, and automating daily counts, Microsoft Graph API is the modern, reliable choice—far better than older approaches like EWS or Outlook COM for cloud/remote mailboxes. Here's a complete, production-ready implementation plan:

为什么优先选择Microsoft Graph API？ #

• 官方支持：微软主推的API，兼容所有Exchange Online、Outlook.com和混合部署的邮箱
• 无本地依赖：不需要安装Outlook客户端，完全基于HTTP请求，适合远程/服务器环境
• 精细权限控制：支持应用级权限（后台自动运行无需用户登录）和委派权限（用户交互场景）
• 性能优化：内置过滤、分页和计数功能，减少不必要的数据传输

Step 1: 注册Azure AD应用获取访问凭据 #

To authenticate your script with Graph API, you'll need to register an app in the Azure Portal:

• 登录Azure Portal，进入Azure Active Directory > App registrations，点击New registration
• 填写应用名称，选择账户类型（企业邮箱选"Accounts in this organizational directory only"，个人邮箱选"Accounts in any organizational directory and personal Microsoft accounts"）
• 注册完成后，记录Client ID和Tenant ID
• 在Certificates & secrets中创建一个新的Client Secret，保存好这个值（只显示一次）
• 前往API permissions > Add a permission，选择Microsoft Graph：
  • 如果需要后台自动运行（无人值守），添加Application Permissions > Mail.Read，然后点击Grant admin consent（需要企业管理员权限）
  • 如果是用户手动触发的场景，添加Delegated Permissions > Mail.Read

Step 2: 编写代码实现邮件统计 #

Below is a Python example (easy to adapt to other languages like C#/PowerShell) that connects to Graph API, queries a specific folder, and counts emails with your target subject:

First, install dependencies:

pip install msgraph-core azure-identity python-dotenv

Create a .env file to store your credentials:

TENANT_ID=your-tenant-id
CLIENT_ID=your-client-id
CLIENT_SECRET=your-client-secret

Then the script:

import os
from datetime import datetime, timezone
from dotenv import load_dotenv
from msgraph_core import GraphClient
from azure.identity import ClientSecretCredential

# Load environment variables
load_dotenv()

# Initialize authentication credential
credential = ClientSecretCredential(
    tenant_id=os.getenv('TENANT_ID'),
    client_id=os.getenv('CLIENT_ID'),
    client_secret=os.getenv('CLIENT_SECRET')
)

# Create Graph API client
graph_client = GraphClient(credential=credential)

def count_target_emails(folder_id, subject_keyword, filter_today=True):
    """
    Count emails in a specific folder that contain the target subject keyword.
    If filter_today is True, only count emails received today (UTC time).
    """
    # Build filter query
    filter_clauses = [f"contains(subject, '{subject_keyword}')"]
    if filter_today:
        today_start = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0).isoformat()
        filter_clauses.append(f"receivedDateTime ge '{today_start}'")
    
    filter_query = " and ".join(filter_clauses)
    # Graph API requires ConsistencyLevel header for $count to work
    headers = {"ConsistencyLevel": "eventual"}
    
    # Send request to Graph API
    response = graph_client.get(
        f"/users/{os.getenv('MAILBOX_USER_EMAIL')}/mailFolders/{folder_id}/messages",
        params={"$filter": filter_query, "$count": "true"},
        headers=headers
    )
    response.raise_for_status()
    data = response.json()
    return data["@odata.count"]

if __name__ == "__main__":
    # Configuration - update these values!
    TARGET_FOLDER_ID = "your-folder-id"  # Get via Graph Explorer (see note below)
    SUBJECT_KEYWORD = "Your Target Subject Phrase"
    MAILBOX_USER_EMAIL = "user@yourdomain.com"  # Required for application permissions
    
    try:
        email_count = count_target_emails(TARGET_FOLDER_ID, SUBJECT_KEYWORD)
        print(f"[{datetime.now()}] Daily count: {email_count} emails with subject containing '{SUBJECT_KEYWORD}'")
        # Optional: Write to log file, database, or send notification (e.g., Teams/email)
        with open("email_stats.log", "a") as f:
            f.write(f"{datetime.now()},{email_count}\n")
    except Exception as e:
        print(f"[{datetime.now()}] Error: {str(e)}")

How to get your folder ID: #

1. Go to Graph Explorer and sign in with your mailbox account
2. Run the query GET /me/mailFolders
3. Find your target folder in the response, copy its id value (looks like a long string of letters/numbers)

Step 3: Automate Daily Execution #

To run this script automatically every day:

• Windows: Use Task Scheduler
  1. Create a new task, set a daily trigger (e.g., 9 AM)
  2. Add an action to start a program: set the program to your Python executable path (e.g., C:\Python311\python.exe) and the argument to your script path (e.g., C:\scripts\email_count.py)
• Linux/macOS: Use cron
  1. Run crontab -e to edit your cron jobs
  2. Add a line like this to run at 9 AM daily:

     0 9 * * * /usr/bin/python3 /path/to/your/email_count.py >> /path/to/email_stats.log 2>&1
     

• Cloud Environment: For higher reliability, deploy to Azure Functions or AWS Lambda with a time trigger (daily schedule). This eliminates the need to manage a local server.

Key Best Practices #

• Limit Permissions: Only request the minimum permissions needed (e.g., Mail.Read instead of full mailbox access)
• Filter Smartly: Add receivedDateTime filter to only count today's emails, reducing API load
• Error Handling: Add retry logic for transient errors (e.g., network timeouts) using libraries like tenacity
• Logging: Always log results and errors to track long-term trends and debug issues
• Quota Awareness: Microsoft Graph API has rate limits—check the docs to avoid hitting thresholds

内容的提问来源于stack exchange，提问作者Alan Pauley