import json import re from datetime import datetime def execute_send_email(row, settings): '''Send Twin Oaks Capital branded HTML email with inline data tables and Excel attachment''' claude_output = row.get_field("compile_and_generate_excel_cc_output_markdown", "") drive_link = row.get_field("upload_to_drive_link", "") s3_key = row.get_field("upload_to_drive_s3_key", "") file_name = row.get_field("upload_to_drive_file_name", "") recipient = row.get_field("recipient_email", "") report_date = datetime.now().strftime("%B %d, %Y") file_date = datetime.now().strftime("%Y-%m-%d") # Parse exec-summary exec_match = re.search(r'```exec-summary\s*\n(.*?)\n```', claude_output, re.DOTALL) executive_text = exec_match.group(1).strip().replace('&', '&').replace('<', '<').replace('>', '>').replace('\n', '
') if exec_match else 'This report covers the latest ETF market landscape including top performers, fund flows, new launches, and regulatory developments.' # Parse json-data report_data = {} json_match = re.search(r'```json-data\s*\n(.*?)\n```', claude_output, re.DOTALL) if json_match: try: report_data = json.loads(json_match.group(1)) except Exception: report_data = {} top_performers = report_data.get("top_performers", []) if isinstance(report_data, dict) else [] top_inflows = report_data.get("top_inflows", []) if isinstance(report_data, dict) else [] top_outflows = report_data.get("top_outflows", []) if isinstance(report_data, dict) else [] new_launches = report_data.get("new_launches", []) if isinstance(report_data, dict) else [] reg_news = report_data.get("regulatory_news", []) if isinstance(report_data, dict) else [] def esc(v): return str(v).replace('&', '&').replace('<', '<').replace('>', '>') # Styles hdr = 'style="background-color:#1B4332;color:#ffffff;font-size:11px;font-weight:700;padding:10px 8px;border:1px solid #2D6A4F;text-align:left;"' r_even = 'style="background-color:#ffffff;padding:8px;border:1px solid #e0e6ed;font-size:11px;color:#3a4a5c;"' r_odd = 'style="background-color:#F5F5F5;padding:8px;border:1px solid #e0e6ed;font-size:11px;color:#3a4a5c;"' sub_hdr = 'style="background-color:#2D6A4F;color:#ffffff;font-size:12px;font-weight:700;padding:8px;border:1px solid #1B4332;"' # Top Performers Table perf_table = '\n' perf_table += '' for h in ['ETF', 'Ticker', 'Category', 'AUM', 'Exp. Ratio', 'YTD', '1Y', '3Y Ann.']: perf_table += '' perf_table += '\n' for i, etf in enumerate(top_performers[:20]): rs = r_odd if i % 2 == 1 else r_even if isinstance(etf, dict): perf_table += '' for key in ['etf_name', 'ticker', 'category', 'aum', 'expense_ratio', 'ytd_return', 'one_year_return', 'three_year_return']: perf_table += '' perf_table += '\n' perf_table += '

' + h + '

' + esc(etf.get(key, 'N/A')) + '

' # Fund Flows section (compact: top 3 each) flows_html = '' if top_inflows or top_outflows: flows_html = '

Top Inflows

' flows_html += '' flows_html += '' for i, f in enumerate(top_inflows[:3]): rs = r_odd if i % 2 == 1 else r_even if isinstance(f, dict): flows_html += '' flows_html += '

ETF

Ticker

Weekly Flow

AUM

' + esc(f.get('etf_name', '')) + '

' + esc(f.get('ticker', '')) + '

' + esc(f.get('weekly_flow', '')) + '

' + esc(f.get('aum', '')) + '

' flows_html += '

Top Outflows

' flows_html += '' flows_html += '' for i, f in enumerate(top_outflows[:3]): rs = r_odd if i % 2 == 1 else r_even if isinstance(f, dict): flows_html += '' flows_html += '

ETF

Ticker

Weekly Flow

AUM

' + esc(f.get('etf_name', '')) + '

' + esc(f.get('ticker', '')) + '

' + esc(f.get('weekly_flow', '')) + '

' + esc(f.get('aum', '')) + '

' # New launches callout launches_html = '' if new_launches: launches_html = '

New ETF Launches

' + esc(l.get('etf_name', '')) + ' (' + esc(l.get('ticker', '')) + ') — ' + esc(l.get('category', '')) + ' by ' + esc(l.get('issuer', '')) + '

' # Drive link drive_section = '' if drive_link: drive_section = '

The full detailed Excel report is also available in your Google Drive.

' html_body = '\n\n\n\n\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n

Twin Oaks Capital Management

Weekly ETF Market Landscape Report

' + report_date + '

Executive Summary

' + executive_text + '

Top-Performing ETFs by Category

\n' + perf_table + '

Fund Flows Highlights

\n' + flows_html + '

' + launches_html + '

' + drive_section + '

Twin Oaks Capital Management • Weekly ETF Market Landscape Report
This report is for informational purposes only and does not constitute investment advice. Past performance does not guarantee future results.

\n' # Build attachment object attachment = None if s3_key: attachment = { "s3key": s3_key, "mimetype": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "name": file_name or f"Twin_Oaks_ETF_Landscape_Report_{file_date}.xlsx" } email_params = { "recipient_email": recipient, "subject": f"Twin Oaks Capital - Weekly ETF Market Landscape Report ({report_date})", "body": html_body, "is_html": True } if attachment: email_params["attachment"] = attachment result = call_user_tool("__UTOOL_SLOT_gmail_GMAIL_SEND_EMAIL__", email_params) thread_id = result.get("threadId", "") if isinstance(result, dict) else "" message_id = result.get("id", "") if isinstance(result, dict) else "" return { "send_email_status": "sent", "send_email_thread_id": thread_id, "send_email_message_id": message_id, "send_email_recipient": recipient, "send_email_subject": f"Twin Oaks Capital - Weekly ETF Market Landscape Report ({report_date})", "send_email_drive_link": drive_link }

ETF News Summary Agent

ETF News Summary Agent

Ready to Automate?