Zapier Automation: Weekly Pipeline Status Email Drafts

For Mortgage Loan Processors

Tools: Zapier, Google Sheets, Gmail | Time to build: 60-90 minutes | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using ChatGPT for status email drafting. see Level 1 guide: "Write a Weekly Status Email to Borrower and Agent"

What This Builds

Every Monday morning, this automation reads your pipeline spreadsheet, generates a personalized status email draft for each active file, and drops them into your Gmail Drafts folder. You spend 20-30 minutes reviewing and sending 50 pre-drafted emails instead of 3-4 hours writing them from scratch. Your borrowers get consistent, professional weekly updates, and your real estate agents stop emailing you asking for status because you've already sent it.

Prerequisites

• Comfortable writing status emails with ChatGPT for individual files (Level 1)
• Your pipeline tracked in a Google Sheets spreadsheet (or willing to move it there)
• Gmail account (or modify for Outlook. Zapier supports both)
• Zapier account. free tier has limited runs; Zapier Starter ($29/month) recommended for weekly automation
• OpenAI API access for the AI step. requires OpenAI account with API key (~$5-15/month usage)

The Concept

An automation is like a scheduled employee who runs the same routine every week without being asked. You set up the instructions once: "Every Monday at 8am, look at my spreadsheet, read each row, have AI write a status email for that row, and put it in my drafts." After setup, it just runs.

The automation has four steps:

1. Trigger: Monday at 8am (schedule trigger)
2. Read: Pull all rows from your pipeline spreadsheet
3. Generate: Send each row's data to OpenAI → get a draft email back
4. Deliver: Put each draft into your Gmail Drafts folder

Build It Step by Step

Part 1: Set Up Your Pipeline Spreadsheet

Your spreadsheet needs consistent column headers for the automation to read correctly. Use these exact column names (or map them later):

Add a row for each active file. Mark closed files as "Closed" so the automation skips them.

Part 2: Create a Zapier Account

1. Go to zapier.com
2. Click "Sign up free". the free tier lets you build and test the zap
3. For production use with weekly runs, upgrade to Starter ($29/month)

Part 3: Build the Zap

Step 3a: Create a new Zap Click "Create Zap" from your Zapier dashboard.

Step 3b: Set the Trigger. Schedule

1. Search for and select "Schedule by Zapier" as the trigger
2. Choose "Every Week"
3. Set it to trigger on Monday at 8:00 AM your time zone
4. Click Continue

Step 3c: Add an Action. Google Sheets "Lookup Spreadsheet Rows"

1. Click the + button to add an action
2. Search for "Google Sheets" and select it
3. Choose action: "Lookup Spreadsheet Row(s)". this returns multiple rows
4. Connect your Google account if prompted
5. Select your pipeline spreadsheet and the correct sheet tab
6. Under "Lookup Column": select "Status"
7. Under "Lookup Value": type "Active". this filters to only active files
8. Click Continue and test

What you should see: Zapier returns a sample of your active rows from the spreadsheet.

Step 3d: Add an Action: "Looping by Zapier" (to process each row)

1. Add action: search for "Looping by Zapier"
2. Select "Create Loop from Line Items"
3. Map your spreadsheet columns to the loop's line item fields (borrower name → line item 1, email → line item 2, etc.)
4. This creates a loop that will run the next steps once for each active file row

Step 3e: Add an Action. OpenAI "Send Prompt"

1. Add action: search for "OpenAI" and select
2. You'll need to connect your OpenAI API key (get one at platform.openai.com → API Keys)
3. Choose action: "Send Prompt"
4. In the Prompt field, build your AI instruction using the loop's variables:

Write a professional weekly status email for a mortgage loan processor to send to a borrower.

Borrower names: [MAP: Borrower Names from loop]
Current loan stage: [MAP: Current Stage from loop]
Outstanding items: [MAP: Outstanding Items from loop]
Next milestone: [MAP: Next Milestone from loop]
Target closing date: [MAP: Target Closing Date from loop]

Write the email:
- Addressed to the borrower(s) by name
- Warm, professional tone
- Under 150 words
- End with: [YOUR SIGNATURE BLOCK]
- Subject line included at the top formatted as: "Subject: Your Loan Update — [Borrower Last Name]"

1. Under Model, select "gpt-4o-mini" (fast and cheap. ideal for this task)

Step 3f: Add an Action. Gmail "Create Draft"

1. Add action: search for "Gmail" and select
2. Choose action: "Create Draft"
3. Connect your Gmail account
4. Map fields:

• To: Map to the borrower email from the loop
• Subject: Extract from the OpenAI output (the first line starting with "Subject:")
• Body: Map to the full OpenAI response text

1. Click Continue and test

Part 4: Test the Full Zap

1. Click "Test Zap". this will run through all steps with your sample data
2. Check your Gmail Drafts folder. you should see draft emails for each active file
3. Review 2-3 drafts for accuracy and tone

What you should see: Professional status email drafts in your Gmail Drafts folder, one per active file, with accurate borrower names and status information from your spreadsheet. Troubleshooting: If emails are in the wrong format, edit the OpenAI prompt in the Zapier step. If Gmail auth fails, reconnect your Google account.

Part 5: Turn the Zap On

1. Click the toggle to "On" (top right of the Zap editor)
2. The Zap will now run automatically every Monday at 8am
3. Your job: spend 20-30 min Monday morning reviewing and sending drafts

Real Example

Setup: The zap is running. You have 47 active files.

Monday 8:00am: Zap triggers, reads 47 active rows from your Google Sheet, sends each to OpenAI, receives 47 draft emails, creates 47 drafts in your Gmail Drafts folder.

Monday 8:12am: You open Gmail Drafts. 47 emails are waiting. You scan each one for accuracy. Most look good, you click Send. A few need a small edit (update an outstanding item, fix a closing date). You edit and send. Total time: 22 minutes.

Without automation: 47 emails × ~5 min each (being generous) = 3.9 hours. Most processors write maybe 10-15 per week, leaving 30+ borrowers without updates.

Time saved: 3+ hours per week, every week.

What to Do When It Breaks

• Zap runs but no drafts appear → Check the Zapier run history (Tasks tab) for error messages. Usually a Gmail auth issue, reconnect your Google account.
• AI drafts have wrong borrower names → Check that your Looping step is correctly mapping the "Borrower Names" column. Test with one row first.
• OpenAI API errors → Check your API credit balance at platform.openai.com. Add $10 to your OpenAI account, the weekly runs cost pennies.
• Spreadsheet columns don't match the Zap → The Zap is mapped to specific column headers. If you rename a column in the spreadsheet, update the Zapier mapping.
• Too many emails generated (closed files) → Make sure the Lookup step is filtering for "Active" status. Check that closed files have "Closed" in the Status column.

Variations

• Simpler version: Don't use Looping or OpenAI. just set up a weekly reminder task (Google Tasks or Outlook reminder) to prompt you to write status emails manually, and paste them into ChatGPT. Lower setup, but you still do the writing.
• Extended version: Add a second Zap branch that sends the agent version of the status email to the real estate agent's email address simultaneously. Doubles the output; same effort after setup.

What to Do Next

• This week: Build and test the Zap with 3-5 test rows from your pipeline
• This month: Go live with the full pipeline and track how your Monday mornings change
• Advanced: Connect your Zap to a Slack channel that posts a daily summary of files with no recent contact. pair with the Excel Copilot pipeline analysis guide

Advanced guide for Mortgage Loan Processor professionals. Requires Zapier Starter ($29/month) and OpenAI API access (usage-based, typically $5-15/month for this workflow). Zapier interfaces and step names may change, use current Zapier documentation for setup details.