mail-contacts

Mail Contacts — Email Directory from Communication History

Query: $ARGUMENTS

Option A: Find a specific person's email address

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"
NAME="<extracted from $ARGUMENTS>"

# Search senders
sqlite3 "$DB" "
SELECT DISTINCT a.address, a.comment as name, COUNT(*) as frequency
FROM messages m
JOIN addresses a ON m.sender = a.ROWID
WHERE (a.comment LIKE '%${NAME}%' OR a.address LIKE '%${NAME}%')
  AND m.deleted=0
GROUP BY a.address ORDER BY frequency DESC LIMIT 10;" 2>/dev/null

# Search recipients (emails sent TO them)
sqlite3 "$DB" "
SELECT DISTINCT a.address, a.comment as name, COUNT(*) as frequency
FROM recipients r
JOIN addresses a ON r.address = a.ROWID
WHERE (a.comment LIKE '%${NAME}%' OR a.address LIKE '%${NAME}%')
GROUP BY a.address ORDER BY frequency DESC LIMIT 10;" 2>/dev/null

Option B: Top contacts directory

sqlite3 "$DB" "
SELECT a.address, a.comment as name,
       COUNT(*) as emails_received,
       MAX(datetime(m.date_received,'unixepoch','localtime')) as last_contact
FROM messages m
JOIN addresses a ON m.sender = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE m.deleted = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Sent%'
  AND m.automated_conversation = 0
  AND m.unsubscribe_type = 0
GROUP BY a.address
ORDER BY emails_received DESC LIMIT 50;" 2>/dev/null

Option C: All contacts from a company/domain

DOMAIN="<extracted from $ARGUMENTS>"
sqlite3 "$DB" "
SELECT DISTINCT a.address, a.comment as name, COUNT(*) as cnt
FROM messages m
JOIN addresses a ON m.sender = a.ROWID
WHERE a.address LIKE '%@${DOMAIN}%' AND m.deleted=0
GROUP BY a.address ORDER BY cnt DESC;" 2>/dev/null

Option D: Recent contacts (last 30 days)

SINCE=$(($(date +%s) - 2592000))
sqlite3 "$DB" "
SELECT DISTINCT a.address, a.comment,
       MAX(datetime(m.date_received,'unixepoch','localtime')) as last_seen
FROM messages m
JOIN addresses a ON m.sender = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE m.date_received >= ${SINCE} AND m.deleted=0
  AND mb.url NOT LIKE '%Spam%'
  AND m.automated_conversation = 0
  AND m.unsubscribe_type = 0
GROUP BY a.address ORDER BY last_seen DESC LIMIT 30;" 2>/dev/null