Scanning Payment Related Emails
Scan the inbox for payment-related emails, extract key fields, and output a prioritized report:
Search terms: invoice, invoices, pay app, pay application, lien waiver,
ACH, payment, remittance, check, wire
For each match:
1. Identify email type (Invoice / Pay App / Lien Waiver / ACH / Payment / Check / Remittance)
2. Check for attachments (PDF, Excel, image)
3. Open attachment and extract: vendor/payer, amount, invoice #, due date, PO #
4. Flag as HIGH PRIORITY if amount >= $4,000
5. Flag as MANUAL REVIEW if: no attachment, amount unreadable, missing invoice #,
mismatched vendor info, or ambiguous email type
6. Append row to results table
Export to Excel/PDF with HIGH PRIORITY items sorted to top,
MANUAL REVIEW items grouped at bottom.
Progress:
- Step 1: Define search scope (date range, folders, sender domains if known)
- Step 2: Search inbox using keyword list (case-insensitive, include plurals/variants)
- Step 3: For each matched email, check for attachments
- Step 4: If attachment exists, open and extract amount, vendor, invoice/PO #, dates
- Step 5: If no attachment, extract same fields from email body
- Step 6: Classify type (Invoice, Pay App, Lien Waiver, ACH, Payment, Check, Remittance, Other)
- Step 7: Apply priority rule — amount >= $4,000 → HIGH PRIORITY
- Step 8: Apply manual review rule — missing data, unreadable attachment, or ambiguous classification → MANUAL REVIEW
- Step 9: Build results table, sort: HIGH PRIORITY first, then normal, then MANUAL REVIEW at bottom
- Step 10: Export to Excel (.xlsx) and/or PDF with clear section headers and color coding
Keyword list (expand as needed)
invoice, invoices, pay app, pay application, payment application, lien waiver, conditional waiver, unconditional waiver, ACH, ACH payment, wire transfer, payment, remittance, remittance advice, check, cheque, draft
Output columns
| Priority | Type | Sender | Vendor/Payer | Invoice # | Amount | Due Date | Attachment | Status | Notes |
Sorting logic
- HIGH PRIORITY (amount ≥ $4,000, fully readable) — sorted by amount descending
- Standard (amount < $4,000, fully readable)
- MANUAL REVIEW (missing/unclear data) — grouped at bottom with reason noted in Notes column
Example 1: Input: Email from "AP@steelsupplyco.com" subject "Invoice #4521 - Net 30" with attached PDF showing Amount Due: $7,200, Invoice #4521, Due 11/15. Output:
| Priority | Type | Vendor | Invoice # | Amount | Due Date | Attachment | Status | Notes |
|---|---|---|---|---|---|---|---|---|
| HIGH | Invoice | Steel Supply Co | 4521 | $7,200.00 | 11/15 | Yes (PDF) | Ready | Amount exceeds $4,000 threshold |
Example 2: Input: Email subject "Lien Waiver Needed" with no attachment, body only says "please send waiver for last payment." Output:
| Priority | Type | Vendor | Invoice # | Amount | Due Date | Attachment | Status | Notes |
|---|---|---|---|---|---|---|---|---|
| MANUAL REVIEW | Lien Waiver | Unknown | — | — | — | No | Needs Review | No attachment, no amount specified — requires follow-up |
Example 3: Input: Email "ACH Remittance Advice" with attached Excel showing two payments: $2,100 and $5,400. Output: Split into two line items — the $5,400 goes to HIGH PRIORITY, the $2,100 stays in Standard section, both referencing the same source email.
- Always open attachments before classifying — subject lines are often misleading (e.g., "Invoice" email that's actually a remittance advice).
- Normalize vendor names (e.g., "Steel Supply Co." vs "Steel Supply Company") to avoid duplicate-looking entries.
- When an email contains multiple payments/invoices, split into separate rows rather than summing amounts.
- Cross-check amount against the $4,000 threshold using the total invoice/payment amount, not line-item subtotals unless specified otherwise.
- Note the email date vs. attachment date if they differ — use attachment date for due date calculations.
- For recurring vendors, keep a short reference list of known names/domains to speed up classification.
- Always include a "Notes" column explaining why something is flagged for manual review — don't just mark it.
- Don't skip emails just because the subject line doesn't contain an exact keyword — scan body text too (e.g., "please remit payment" without the word "invoice").
- Don't guess amounts from unclear/scanned attachments — flag for manual review instead of estimating.
- Don't merge multiple distinct invoices from one email into a single total — this hides individual high-priority items.
- Don't place manual-review items in the middle of the report — they must be clearly separated at the bottom so they aren't missed.
- Don't treat "payment" and "pay app" (pay application, common in construction billing) as the same category — they have different data fields (pay apps include schedule of values, % complete, retainage).
- Don't forget to check for lien waiver type (conditional vs. unconditional, partial vs. final) — this affects downstream processing.