I’m creating a Python-based mail merge tool that uses Apple Mail drafts as templates. The goal is to duplicate the drafts, replace placeholders like [Name] with data from an Excel file, and retain the original formatting (bold, italics, etc.).

What’s going wrong:

The issue is that when the draft is duplicated and modified using AppleScript, the formatting of the original draft is lost. Instead, the new drafts appear in plain text.

When I run the script, I get the error:

AppleScript execution failed: XYZ. The generated drafts are either empty or contain unformatted text.

import pandas as pd
import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import subprocess
import os

def list_drafts():
    """Fetches all drafts' subjects from Apple Mail using AppleScript."""
    script = """
    tell application "Mail"
        set draftSubjects to {}
        repeat with aMessage in (messages of drafts mailbox)
            set end of draftSubjects to subject of aMessage
        end repeat
        return draftSubjects
    end tell
    """
    try:
        result = subprocess.check_output(["osascript", "-e", script]).decode().strip()
        return result.split(", ") if result else []
    except subprocess.CalledProcessError as e:
        messagebox.showerror("Error", f"Failed to fetch drafts:n{e.output.decode().strip()}")
        return []

def generate_csv(excel_path):
    """Reads an Excel file and converts it to a DataFrame."""
    try:
        df = pd.read_excel(excel_path)
        if df.empty or "Name" not in df.columns or "Email" not in df.columns:
            raise ValueError("The Excel file must have at least two columns: 'Name' and 'Email'.")
        return df
    except Exception as e:
        messagebox.showerror("Error", f"Failed to process Excel file: {e}")
        raise e

def create_and_run_applescript(selected_draft, df):
    """Generates and runs AppleScript to create email drafts in Apple Mail, preserving formatting."""
    script_header = f"""
    tell application "Mail"
        set selectedDraft to (first message of drafts mailbox whose subject is "{selected_draft}")
    """
    script_body = ""

    # Build the AppleScript dynamically for each row in the Excel file
    for _, row in df.iterrows():
        recipient_name = row['Name']
        recipient_email = row['Email']
        script_body += f"""
        set draftBody to content of selectedDraft -- Preserve the original formatting
        set newBody to my replace_text(draftBody, "[Name]", "{recipient_name}") -- Replace only the placeholder

        set newMessage to make new outgoing message with properties {{subject:"{selected_draft}", content:newBody, visible:false, sender:"[email protected]"}}
        tell newMessage
            make new to recipient at end of to recipients with properties {{address:"{recipient_email}"}}
            save
        end tell
        """

    script_footer = """
    end tell

    on replace_text(theText, theSearch, theReplace)
        -- Replace text carefully while preserving formatting
        set AppleScript's text item delimiters to theSearch
        set theItems to every text item of theText
        set AppleScript's text item delimiters to theReplace
        set theText to theItems as text
        set AppleScript's text item delimiters to ""
        return theText
    end replace_text
    """

    full_script = script_header + script_body + script_footer

    # Save and execute AppleScript
    try:
        applescript_path = os.path.join(os.getcwd(), "mail_merge_preserve_formatting.scpt")
        with open(applescript_path, "w") as f:
            f.write(full_script)
        subprocess.check_output(["osascript", applescript_path])
        messagebox.showinfo("Success", "Drafts created successfully in Apple Mail!")
    except subprocess.CalledProcessError as e:
        messagebox.showerror("Error", f"AppleScript execution failed:n{e.output.decode().strip()}")

def browse_excel_file():
    """Opens a file dialog to select an Excel file."""
    file_path = filedialog.askopenfilename(title="Select Excel File", filetypes=[("Excel files", "*.xlsx")])
    if file_path:
        excel_path_var.set(file_path)

def generate_drafts():
    """Processes the selected draft and Excel file to generate drafts."""
    excel_path = excel_path_var.get()
    selected_draft = draft_var.get()
    if not excel_path or not selected_draft:
        messagebox.showerror("Error", "Please select an Excel file and a draft!")
        return

    try:
        df = generate_csv(excel_path)
        create_and_run_applescript(selected_draft, df)
    except Exception as e:
        messagebox.showerror("Error", f"Failed to generate drafts: {e}")

def refresh_drafts():
    """Refreshes the list of drafts in the dropdown menu."""
    drafts = list_drafts()
    draft_dropdown['values'] = drafts
    if drafts:
        draft_var.set(drafts[0])
    else:
        draft_var.set("No drafts found")

# GUI Setup
root = tk.Tk()
root.title("Mail Merge App")

# Dropdown for Draft Selection
tk.Label(root, text="Select Draft from Apple Mail:").pack(pady=5)
draft_var = tk.StringVar()
draft_dropdown = ttk.Combobox(root, textvariable=draft_var, width=50, state="readonly")
draft_dropdown.pack(pady=5)
refresh_button = tk.Button(root, text="Refresh Drafts", command=refresh_drafts)
refresh_button.pack(pady=5)

# Excel File Selection
tk.Label(root, text="Excel File (with 'Name' and 'Email' columns):").pack(pady=5)
excel_path_var = tk.StringVar()
tk.Entry(root, textvariable=excel_path_var, width=50).pack(pady=5)
browse_button = tk.Button(root, text="Browse", command=browse_excel_file)
browse_button.pack(pady=5)

# Generate Button
generate_button = tk.Button(root, text="Generate Drafts", command=generate_drafts)
generate_button.pack(pady=20)

# Initial Load of Drafts
refresh_drafts()

root.mainloop()