Validate Email Lists Weekly with Google Sheets, Verifiemail and Gmail Reports

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "ZquoLLFP4qaudR5P",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Email List Hygiene Automation",
  "tags": [],
  "nodes": [
    {
      "id": "8a19fa06-c280-4907-a551-3b34ca0f993c",
      "name": "Calculate Statistics",
      "type": "n8n-nodes-base.code",
      "position": [
        656,
        -320
      ],
      "parameters": {
        "jsCode": "// Get all processed items from the loop\nconst items = $input.all();\n\n// Log for debugging\nconsole.log(`Processing ${items.length} items for statistics`);\n\n// Initialize counters\nconst stats = {\n  total: items.length,\n  valid: 0,\n  invalid: 0,\n  risky: 0,\n  unknown: 0,\n  processed_at: new Date().toLocaleString('en-IN', {\n    dateStyle: 'full',\n    timeStyle: 'short'\n  }),\n  sheet_url: $('Read Email List').params?.documentId?.__rl?.cachedResultUrl || 'YOUR_GOOGLE_SHEET_URL'\n};\n\n// Count each category\nitems.forEach(item => {\n  const status = item.json.status;\n  \n  if (status === 'valid') {\n    stats.valid++;\n  } else if (status === 'invalid') {\n    stats.invalid++;\n  } else if (status === 'risky') {\n    stats.risky++;\n  } else {\n    stats.unknown++;\n  }\n});\n\n// Calculate percentages (handle division by zero)\nif (stats.total > 0) {\n  stats.valid_percentage = ((stats.valid / stats.total) * 100).toFixed(1);\n  stats.invalid_percentage = ((stats.invalid / stats.total) * 100).toFixed(1);\n  stats.risky_percentage = ((stats.risky / stats.total) * 100).toFixed(1);\n  stats.unknown_percentage = ((stats.unknown / stats.total) * 100).toFixed(1);\n} else {\n  stats.valid_percentage = '0.0';\n  stats.invalid_percentage = '0.0';\n  stats.risky_percentage = '0.0';\n  stats.unknown_percentage = '0.0';\n}\n\n// Calculate list health score (0-100)\n// Formula: \n// - Valid emails: +100 points per email (percentage of total)\n// - Invalid emails: -20 points per email (penalty)\n// - Risky emails: -10 points per email (smaller penalty)\nstats.health_score = Math.max(0, Math.min(100, Math.round(\n  (stats.valid / stats.total) * 100 - \n  (stats.invalid / stats.total) * 20 - \n  (stats.risky / stats.total) * 10\n)));\n\n// Determine health status\nif (stats.health_score >= 80) {\n  stats.health_status = 'Excellent \u2705';\n  stats.health_color = '#4CAF50';\n} else if (stats.health_score >= 60) {\n  stats.health_status = 'Good \u26a0\ufe0f';\n  stats.health_color = '#ff9800';\n} else {\n  stats.health_status = 'Needs Attention \u274c';\n  stats.health_color = '#f44336';\n}\n\n// Create summary text for notifications\nstats.summary = `\ud83d\udcca Email List Validation Complete\n\n\u2705 Valid: ${stats.valid} (${stats.valid_percentage}%)\n\u274c Invalid: ${stats.invalid} (${stats.invalid_percentage}%)\n\u26a0\ufe0f Risky: ${stats.risky} (${stats.risky_percentage}%)\n\u2753 Unknown: ${stats.unknown} (${stats.unknown_percentage}%)\n\n\ud83d\udcc8 List Health Score: ${stats.health_score}/100 - ${stats.health_status}\n\ud83d\udce7 Total Processed: ${stats.total} emails\n\ud83d\udd50 Completed: ${stats.processed_at}`;\n\n// Create detailed lists for the report\nstats.email_details = {\n  valid_emails: items\n    .filter(item => item.json.status === 'valid')\n    .map(item => item.json.email),\n  \n  invalid_emails: items\n    .filter(item => item.json.status === 'invalid')\n    .map(item => ({\n      email: item.json.email,\n      reason: item.json.notes\n    })),\n  \n  risky_emails: items\n    .filter(item => item.json.status === 'risky')\n    .map(item => ({\n      email: item.json.email,\n      reason: item.json.notes\n    }))\n};\n\n// Log summary for debugging\nconsole.log(stats.summary);\n\n// Return the complete statistics object\nreturn stats;"
      },
      "typeVersion": 2
    },
    {
      "id": "d6827158-4e8d-4806-a04b-7f9f35384532",
      "name": "Weekly Schedule (Friday 5PM)",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        0,
        0
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 17 * * 5"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "0084d5ab-cdf7-4f5e-969e-711eee3bbcd7",
      "name": "Read Email List",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        208,
        0
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HjIuSDqm2e8k0TUZuFqOdm4MZ4vzvnLtKRSFsiKVtYk/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "6d5bacd8-88cc-4979-a445-eba35f7b60f6",
      "name": "Process Each Email",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        416,
        0
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "e2bf73b2-46f6-4661-8c74-5297b37318ad",
      "name": "Validate Email Address",
      "type": "n8n-nodes-verifiemail.verifiEmail",
      "position": [
        672,
        -96
      ],
      "parameters": {
        "email": "={{ $json.email }}"
      },
      "credentials": {
        "verifiEmailApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "63fdab5f-ce90-4700-a343-f5823f17a236",
      "name": "Check Validation Result",
      "type": "n8n-nodes-base.if",
      "position": [
        896,
        -96
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "a3f7a8d6-9219-44b7-be4b-0f8d5d5aa5c5",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.valid }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "e71190c0-fdc1-49ac-ae76-742dbea0782d",
      "name": "Process Valid Email",
      "type": "n8n-nodes-base.code",
      "position": [
        1248,
        -240
      ],
      "parameters": {
        "jsCode": "// Get the validation result from current input\nconst validation = $input.first().json;\n\n// Get the ORIGINAL data from Loop Over Items node\nconst loopData = $('Process Each Email').item.json;\n\n// Process valid email\nlet status = 'valid';\nlet notes = 'Email verified successfully';\n\n// Check for any warnings even if valid\nif (validation.details?.disposable === true) {\n  status = 'risky';\n  notes = 'Valid but disposable email address';\n}\n\nconst checkedAt = new Date().toLocaleString('en-IN', { \n  year: 'numeric',\n  month: '2-digit',\n  day: '2-digit',\n  hour: '2-digit',\n  minute: '2-digit',\n  hour12: true\n});\n\n// Return merged data - combining Loop data + validation results\nreturn {\n  row_number: loopData.row_number,\n  name: loopData.name,\n  email: loopData.email,\n  status: status,\n  checked_at: checkedAt,\n  notes: notes,\n  is_valid: true\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "08f26195-97eb-4cf0-989e-2b686c7e4bd7",
      "name": "Process Invalid Email",
      "type": "n8n-nodes-base.code",
      "position": [
        1232,
        -48
      ],
      "parameters": {
        "jsCode": "// Get the validation result\nconst validation = $input.first().json;\n\n// Get original data from Loop Over Items\nconst loopData = $('Process Each Email').item.json;\n\n// Process invalid email\nlet status = 'invalid';\nlet notes = 'Email validation failed';\n\n// Get more specific reason from validation details\nif (validation.details) {\n  if (validation.details.validMxRecord === false) {\n    notes = 'Invalid - No mail server found';\n  } else if (validation.details.rfcCompliant === false) {\n    notes = 'Invalid - Incorrect email format';\n  } else if (validation.details.spoofFree === false) {\n    notes = 'Invalid - Possible spoof/fake email';\n  }\n}\n\nconst checkedAt = new Date().toLocaleString('en-IN', { \n  year: 'numeric',\n  month: '2-digit',\n  day: '2-digit',\n  hour: '2-digit',\n  minute: '2-digit',\n  hour12: true\n});\n\n// Return merged data\nreturn {\n  row_number: loopData.row_number,\n  name: loopData.name,\n  email: loopData.email,\n  status: status,\n  checked_at: checkedAt,\n  notes: notes,\n  is_valid: false\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "5e98a36d-31c7-446b-a1ed-9988d3cb3ef3",
      "name": "Update Valid Status",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1520,
        -240
      ],
      "parameters": {
        "columns": {
          "value": {
            "name": "={{ $json.name }}",
            "email": "={{ $json.email }}",
            "notes": "={{ $json.notes }}",
            "status": "={{ $json.status }}",
            "checked_at": "={{ $json.checked_at }}",
            "row_number": "={{ $json.row_number }}"
          },
          "schema": [
            {
              "id": "name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "checked_at",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "checked_at",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "notes",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "notes",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "row_number",
              "type": "number",
              "display": true,
              "removed": false,
              "readOnly": true,
              "required": false,
              "displayName": "row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "row_number"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HjIuSDqm2e8k0TUZuFqOdm4MZ4vzvnLtKRSFsiKVtYk/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "790a9728-2083-402d-8334-8254924c2e29",
      "name": "Update Invalid Status",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1520,
        -48
      ],
      "parameters": {
        "columns": {
          "value": {
            "name": "={{ $json.name }}",
            "email": "={{ $json.email }}",
            "notes": "={{ $json.notes }}",
            "status": "={{ $json.status }}",
            "checked_at": "={{ $json.checked_at }}",
            "row_number": "={{ $json.row_number }}"
          },
          "schema": [
            {
              "id": "name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "checked_at",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "checked_at",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "notes",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "notes",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "row_number",
              "type": "number",
              "display": true,
              "removed": false,
              "readOnly": true,
              "required": false,
              "displayName": "row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "row_number"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HjIuSDqm2e8k0TUZuFqOdm4MZ4vzvnLtKRSFsiKVtYk/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "11a7ea29-2e24-4f9c-91dc-27afa0ca46f7",
      "name": "Send Weekly Report",
      "type": "n8n-nodes-base.gmail",
      "position": [
        928,
        -320
      ],
      "parameters": {
        "sendTo": "user@example.com",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <meta charset=\"UTF-8\">\n  <style>\n    body { \n      font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Arial, sans-serif;\n      line-height: 1.6; \n      color: #333;\n      margin: 0;\n      padding: 0;\n      background-color: #f5f5f5;\n    }\n    .container { \n      max-width: 650px; \n      margin: 20px auto; \n      background: white;\n      border-radius: 12px;\n      overflow: hidden;\n      box-shadow: 0 4px 6px rgba(0,0,0,0.1);\n    }\n    .header { \n      background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);\n      color: white; \n      padding: 40px 30px; \n      text-align: center;\n    }\n    .header h1 {\n      margin: 0 0 10px 0;\n      font-size: 28px;\n      font-weight: 600;\n    }\n    .header p {\n      margin: 0;\n      opacity: 0.9;\n      font-size: 16px;\n    }\n    .content { \n      padding: 30px;\n    }\n    .health-score {\n      background: {{ $json.health_color }};\n      color: white;\n      padding: 30px;\n      border-radius: 12px;\n      text-align: center;\n      margin: 0 0 30px 0;\n      box-shadow: 0 4px 12px rgba(0,0,0,0.15);\n    }\n    .health-score-label {\n      font-size: 14px;\n      text-transform: uppercase;\n      letter-spacing: 1px;\n      opacity: 0.9;\n      margin-bottom: 10px;\n    }\n    .health-score-number {\n      font-size: 56px;\n      font-weight: bold;\n      margin: 15px 0;\n      line-height: 1;\n    }\n    .health-score-status {\n      font-size: 18px;\n      font-weight: 500;\n      opacity: 0.95;\n    }\n    .stats-grid {\n      display: grid;\n      grid-template-columns: repeat(2, 1fr);\n      gap: 15px;\n      margin: 30px 0;\n    }\n    .stat-card {\n      background: #f8f9fa;\n      padding: 20px;\n      border-radius: 10px;\n      text-align: center;\n      border: 2px solid #e9ecef;\n      transition: transform 0.2s;\n    }\n    .stat-card:hover {\n      transform: translateY(-2px);\n      box-shadow: 0 4px 8px rgba(0,0,0,0.1);\n    }\n    .stat-label {\n      color: #6c757d;\n      font-size: 13px;\n      text-transform: uppercase;\n      letter-spacing: 0.5px;\n      margin-bottom: 8px;\n      font-weight: 600;\n    }\n    .stat-number {\n      font-size: 32px;\n      font-weight: bold;\n      margin: 8px 0;\n      line-height: 1;\n    }\n    .stat-percentage {\n      color: #6c757d;\n      font-size: 14px;\n    }\n    .valid { color: #28a745; }\n    .invalid { color: #dc3545; }\n    .risky { color: #fd7e14; }\n    .action-section {\n      background: #fff3cd;\n      border-left: 4px solid #ffc107;\n      padding: 20px;\n      margin: 25px 0;\n      border-radius: 8px;\n    }\n    .action-section h3 {\n      margin: 0 0 15px 0;\n      color: #856404;\n      font-size: 18px;\n    }\n    .action-section ul {\n      margin: 10px 0;\n      padding-left: 20px;\n    }\n    .action-section li {\n      margin: 8px 0;\n      color: #856404;\n    }\n    .btn {\n      display: inline-block;\n      background: #667eea;\n      color: white !important;\n      padding: 14px 32px;\n      text-decoration: none;\n      border-radius: 8px;\n      margin: 20px 0;\n      font-weight: 600;\n      font-size: 16px;\n      box-shadow: 0 4px 12px rgba(102, 126, 234, 0.4);\n      transition: all 0.3s;\n    }\n    .btn:hover {\n      background: #5568d3;\n      transform: translateY(-2px);\n      box-shadow: 0 6px 16px rgba(102, 126, 234, 0.5);\n    }\n    .summary-box {\n      background: white;\n      padding: 20px;\n      border-radius: 10px;\n      margin: 20px 0;\n      border: 2px solid #e9ecef;\n    }\n    .summary-box h3 {\n      margin: 0 0 15px 0;\n      font-size: 18px;\n      color: #495057;\n    }\n    .summary-box pre {\n      white-space: pre-wrap;\n      font-family: 'SF Mono', 'Monaco', 'Courier New', monospace;\n      font-size: 14px;\n      line-height: 1.8;\n      margin: 0;\n      color: #495057;\n    }\n    .insights-box {\n      background: linear-gradient(135deg, #e3f2fd 0%, #f3e5f5 100%);\n      padding: 25px;\n      border-radius: 10px;\n      margin: 25px 0;\n    }\n    .insights-box h3 {\n      margin: 0 0 15px 0;\n      color: #1976d2;\n      font-size: 18px;\n    }\n    .insight-item {\n      margin: 12px 0;\n      padding-left: 25px;\n      position: relative;\n    }\n    .insight-item:before {\n      content: '\u2713';\n      position: absolute;\n      left: 0;\n      color: #1976d2;\n      font-weight: bold;\n      font-size: 18px;\n    }\n    .email-list {\n      background: #f8f9fa;\n      padding: 15px;\n      border-radius: 8px;\n      margin: 15px 0;\n      max-height: 200px;\n      overflow-y: auto;\n    }\n    .email-list h4 {\n      margin: 0 0 10px 0;\n      font-size: 14px;\n      color: #495057;\n      text-transform: uppercase;\n      letter-spacing: 0.5px;\n    }\n    .email-item {\n      padding: 8px 12px;\n      margin: 5px 0;\n      background: white;\n      border-radius: 6px;\n      font-size: 13px;\n      border-left: 3px solid #dee2e6;\n    }\n    .email-item.invalid {\n      border-left-color: #dc3545;\n    }\n    .email-reason {\n      color: #6c757d;\n      font-size: 12px;\n      margin-top: 4px;\n    }\n    .footer {\n      background: #f8f9fa;\n      text-align: center;\n      color: #6c757d;\n      font-size: 13px;\n      padding: 25px;\n      border-top: 1px solid #dee2e6;\n    }\n    .footer p {\n      margin: 5px 0;\n    }\n    @media only screen and (max-width: 600px) {\n      .stats-grid {\n        grid-template-columns: 1fr;\n      }\n      .container {\n        margin: 10px;\n      }\n      .content {\n        padding: 20px;\n      }\n    }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"header\">\n      <h1>\ud83e\uddf9 Email List Hygiene Report</h1>\n      <p>Automated Weekly Validation Results</p>\n    </div>\n    \n    <div class=\"content\">\n      <!-- Health Score Section -->\n      <div class=\"health-score\">\n        <div class=\"health-score-label\">List Health Score</div>\n        <div class=\"health-score-number\">{{ $json.health_score }}/100</div>\n        <div class=\"health-score-status\">{{ $json.health_status }}</div>\n      </div>\n\n      <!-- Statistics Grid -->\n      <div class=\"stats-grid\">\n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Valid Emails</div>\n          <div class=\"stat-number valid\">{{ $json.valid }}</div>\n          <div class=\"stat-percentage\">{{ $json.valid_percentage }}%</div>\n        </div>\n        \n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Invalid Emails</div>\n          <div class=\"stat-number invalid\">{{ $json.invalid }}</div>\n          <div class=\"stat-percentage\">{{ $json.invalid_percentage }}%</div>\n        </div>\n        \n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Risky Emails</div>\n          <div class=\"stat-number risky\">{{ $json.risky }}</div>\n          <div class=\"stat-percentage\">{{ $json.risky_percentage }}%</div>\n        </div>\n        \n        <div class=\"stat-card\">\n          <div class=\"stat-label\">Total Processed</div>\n          <div class=\"stat-number\">{{ $json.total }}</div>\n          <div class=\"stat-percentage\">100%</div>\n        </div>\n      </div>\n\n      <!-- Action Required Section -->\n      <div class=\"action-section\">\n        <h3>\u26a1 Action Required</h3>\n        <ul>\n          <li><strong>{{ $json.invalid }}</strong> invalid email(s) detected and flagged</li>\n          <li><strong>{{ $json.risky }}</strong> risky email(s) found (disposable/catch-all)</li>\n          <li>All email statuses have been automatically updated in your sheet</li>\n          <li>Review flagged emails and consider removal from active campaigns</li>\n        </ul>\n      </div>\n\n      <!-- Invalid Emails List -->\n      {{ $json.invalid > 0 ? '<div class=\"email-list\"><h4>\u274c Invalid Emails Detected</h4>' : '' }}\n      {{ $json.email_details.invalid_emails.map(e => `<div class=\"email-item invalid\"><strong>${e.email}</strong><div class=\"email-reason\">${e.reason}</div></div>`).join('') }}\n      {{ $json.invalid > 0 ? '</div>' : '' }}\n\n      <!-- CTA Button -->\n      <div style=\"text-align: center;\">\n        <a href=\"{{ $json.sheet_url }}\" class=\"btn\">\ud83d\udcca View Full Report in Google Sheets</a>\n      </div>\n\n      <!-- Text Summary -->\n      <div class=\"summary-box\">\n        <h3>\ud83d\udccb Quick Summary</h3>\n        <pre>{{ $json.summary }}</pre>\n      </div>\n\n      <!-- Insights Section -->\n      <div class=\"insights-box\">\n        <h3>\ud83d\udca1 What This Means for Your Campaigns</h3>\n        <div class=\"insight-item\">\n          <strong>Improved Deliverability:</strong> Valid emails ensure better inbox placement and sender reputation\n        </div>\n        <div class=\"insight-item\">\n          <strong>Cost Savings:</strong> Reduced bounces mean lower email service costs and fewer blacklist risks\n        </div>\n        <div class=\"insight-item\">\n          <strong>Better Metrics:</strong> Clean lists lead to higher open rates, click rates, and engagement\n        </div>\n        <div class=\"insight-item\">\n          <strong>Compliance Ready:</strong> Maintain GDPR/CAN-SPAM compliance with verified contacts\n        </div>\n      </div>\n    </div>\n\n    <!-- Footer -->\n    <div class=\"footer\">\n      <p>\ud83e\udd16 <strong>Automated by n8n Email Hygiene System</strong></p>\n      <p>Report generated: {{ $json.processed_at }}</p>\n      <p style=\"margin-top: 15px; opacity: 0.7;\">This report runs automatically every Friday at 5:00 PM</p>\n    </div>\n  </div>\n</body>\n</html>",
        "options": {},
        "subject": "=\ud83d\udce7 Email Hygiene Report - {{ $now.format(\"MMM DD, YYYY\") }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "d30f5ad6-778c-4891-90fc-c58fa49612e9",
      "name": "Merge1",
      "type": "n8n-nodes-base.merge",
      "position": [
        1776,
        -64
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "f48744cc-7449-43fa-8fbd-dfda525f22b8",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        -560
      ],
      "parameters": {
        "color": 7,
        "width": 336,
        "height": 576,
        "content": "## EMAIL LIST HYGIENE AUTOMATION\n\nAutomatically validates your email list **every Friday at 5 PM**.\n\n\u2705 What it does:\n- **Reads emails** from **Google Sheets**\n- Validates each email (format, MX records, deliverability)\n- Categorizes as **Valid**, **Invalid**, or **Risky**\n- Updates sheet with **validation status** and **timestamp**\n- Generates **health score** and **statistics**\n- Sends **professional HTML report** via **email**\n\n\ud83d\udcc8 Benefits:\n- Improved **deliverability**(reduce 15%+ bounce rates to <2%)\n- Better sender reputation with **ISPs**\n- Higher campaign **ROI and engagement**\n- **GDPR/CAN-SPAM compliance** ready\n\n\u23f1\ufe0f Runs: **Every Friday at 5:00 PM**\n\ud83d\udce7 Processes: **Unlimited emails**(loops through all)"
      },
      "typeVersion": 1
    },
    {
      "id": "071eceb8-e834-45ee-926e-cbab44953cfd",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        176
      ],
      "parameters": {
        "color": 7,
        "width": 320,
        "height": 736,
        "content": "## SETUP REQUIRED (5 minutes)\n\nBefore activating this workflow:\n\n1. **Google Sheets** Setup:\n   Create sheet with these exact columns:\n   \u2022 row_number (auto-generated by Sheets)\n   \u2022 name\n   \u2022 email\n   \u2022 status **(leave blank - auto-filled)**\n   \u2022 checked_at **(leave blank - auto-filled)**\n   \u2022 notes **(leave blank - auto-filled)**\n\n2. Credentials Needed:\n   \u2022 Google Sheets OAuth2\n   \u2022 Email validation API **(VerifiEmail)** at **https://verifi.email**\n   \u2022 Gmail OAuth2 (for sending reports)\n\n3. Configure Email Recipient:\n   \u2022 Open **\"Send Email Report\"** node\n   \u2022 Change **\"sendTo\"** from **\"marketing.manager@company.com\"**\n   \u2022 Use: **your-email@company.com** or **team@company.com**\n   \u2022 Optional: Add CC/BCC for multiple recipients\n\n4. Test Before Activating:\n   \u2022 Add **3-5 test emails** to your **sheet**\n   \u2022 Click \"Execute Workflow\" button\n   \u2022 Verify sheet updates correctly\n   \u2022 Check **email report arrives**\n   \u2022 Then toggle **\"Active\"** switch"
      },
      "typeVersion": 1
    },
    {
      "id": "2517b984-ed20-4930-85b1-33d303e0b714",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        160
      ],
      "parameters": {
        "color": 7,
        "width": 384,
        "height": 608,
        "content": "## EMAIL VALIDATION PROCESS\n\nProcesses each email **individually** in a **loop**:\n\n**STEP 1**: Loop starts\n   \u2192 Takes one email from sheet\n\n**STEP 2**: Validation checks\n   \u2713 Email format (RFC 5322 compliance)\n   \u2713 Domain has mail server (MX records)\n   \u2713 Mailbox exists (SMTP verification)\n   \u2713 Not disposable email service\n   \u2713 Not catch-all domain\n\n**STEP 3**: Categorization\n   \u2705 VALID - Passes all checks, safe to use\n   \u274c INVALID - Failed checks, remove from list\n   \u26a0\ufe0f RISKY - Disposable or catch-all, review\n\n**STEP 4**: Update sheet\n   \u2022 Writes status to row\n   \u2022 Adds timestamp\n   \u2022 Includes reason/notes\n\n**STEP 5**: Loop continues\n   \u2192 Processes next email\n   \u2192 Repeats until all done\n\n**Speed**: ~1-2 emails per second\nHandles: **1000+ emails ** automatically"
      },
      "typeVersion": 1
    },
    {
      "id": "7c8c9273-b54f-4a3f-8131-7031cf23c806",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        992,
        160
      ],
      "parameters": {
        "color": 7,
        "width": 368,
        "height": 688,
        "content": "## TRUE/FALSE BRANCHING\n\n**IF node splits workflow** based on **validation**:\n\n\u2705 TRUE BRANCH (Valid Emails):\n   Condition: validation.valid === true\n   \u2193\n   Process Valid Email code:\n   \u2022 Status: \"valid\"\n   \u2022 Note: **\"Email verified successfully\"**\n   \u2022 Check for disposable flag\n   \u2193\n   Update Valid Status:\n   \u2022 Updates Google Sheet row\n   \u2022 Writes status, timestamp, notes\n   \u2193\n   **Merge Input 1**:\n   \u2022 Collects valid email data\n\n\u274c FALSE BRANCH **(Invalid Emails)**:\n   Condition: validation.valid === false\n   \u2193\n   Process Invalid Email code:\n   \u2022 Status: \"invalid\"\n   \u2022 Note: Specific reason (no MX, bad format, etc.)\n   \u2193\n   Update Invalid Status:\n   \u2022 Updates Google Sheet row\n   \u2022 Writes status, timestamp, notes\n   \u2193\n   **Merge Input 2**:\n   \u2022 Collects invalid email data\n\n\ud83d\udd17 Merge combines both branches \u2192 loops back"
      },
      "typeVersion": 1
    },
    {
      "id": "f30a760a-0560-443c-a352-4ae7e16733a8",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1984,
        -112
      ],
      "parameters": {
        "color": 7,
        "width": 368,
        "height": 576,
        "content": "## MERGE & LOOP COMPLETION\n\nThe **Merge node** is critical for data flow:\n\n**INPUT 1**: Valid emails from **TRUE** branch\n**INPUT 2**: Invalid emails from **FALSE** branch\n\nWhat Merge does:\n- Combines both data streams\n- Creates single unified output\n- Feeds back to **Loop Over Items**\n- Loop accumulates all processed emails internally\n\nLoop Behavior:\n- Continues processing next email\n- Repeats **validation \u2192 branch \u2192 merge \u2192 loop**\n- Tracks all items internally\n- When complete, \"done\" output fires\n\n**DONE** Output:\n- Triggers only after ALL emails processed\n- Sends accumulated data to Calculate Statistics\n- Contains complete results from all iterations\n\nThis ensures every email is validated and collected\nbefore generating the final report."
      },
      "typeVersion": 1
    },
    {
      "id": "dc616d16-1e8d-49e7-9ef8-0bc7234a7ea0",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        656,
        -1056
      ],
      "parameters": {
        "color": 7,
        "width": 384,
        "height": 720,
        "content": "## ANALYTICS & REPORTING\n\nAfter all emails processed:\n\n1\ufe0f\u20e3 **Calculate Statistics**:\n   Receives all accumulated emails from loop\n   \n   **Calculates**:\n   \u2022 **Total emails** processed\n   \u2022 **Valid count** & **percentage** \n   \u2022 **Invalid count** & **percentage** \n   \u2022 **Risky count** & **percentage**\n   \n   **Health Score Formula** (0-100):\n   = **(Valid% \u00d7 100) - (Invalid% \u00d7 20) - (Risky% \u00d7 10)**\n   \n   **Scoring**:\n   \u2022 **80-100: Excellent \u2705 (Green)**\n   \u2022 **60-79: Good \u26a0\ufe0f (Orange)**\n   \u2022 **0-59: Needs Attention \u274c (Red)**\n   \n   **Creates**:\n   \u2022 Summary text\n   \u2022 Email lists by category\n   \u2022 Invalid emails with reasons\n\n2\ufe0f\u20e3 **Send Email Report**:\n   Beautiful **HTML email** with:\n   \u2022 **Color-coded health score badge**\n   \u2022 **4 stat cards (valid/invalid/risky/total)**\n   \u2022 **Action items list**\n   \u2022 **Invalid emails with detailed reasons**\n   \u2022 **Direct link to Google Sheet**\n   \u2022 Campaign impact insights\n   \u2022 Professional branding\n\n**Sent to**: Configurable recipients\n\ud83d\udcc5 Frequency: **Every Friday at 5:00 PM**"
      },
      "typeVersion": 1
    },
    {
      "id": "dc73ce02-5bfb-4d98-9f18-3d8753f5acf1",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1568,
        -1152
      ],
      "parameters": {
        "color": 7,
        "width": 384,
        "height": 832,
        "content": "## CUSTOMIZATION OPTIONS\n\nEasy modifications:\n\n\u23f0 Change Schedule:\n   \u2022 **Edit \"Weekly Schedule\" node**\n   \u2022 Current: 0 17 * * 5 (Friday 5PM)\n   \u2022 Examples:\n     - Daily 9AM: 0 9 * * *\n     - Every Monday: 0 17 * * 1\n     - First of month: 0 9 1 * *\n\n\ud83d\udce7 Multiple Recipients:\n   \u2022 \"Send Email Report\" node \u2192 Add options\n\n   \u2022 Comma-separate: email1@co.com,email2@co.com\n\n\ud83d\udcca Add Slack Notifications:\n   \u2022 Add Slack node after email\n   \u2022 Channel: #marketing\n   \u2022 Quick text summary for instant alerts\n\n\ud83d\uddc3\ufe0f Archive Invalid Emails:\n   \u2022 Add Google Sheets node on FALSE branch\n   \u2022 Operation: Append\n   \u2022 Sheet: Create \"Invalid_Archive\" tab\n   \u2022 Keeps historical record\n\n\ud83d\udcc8 Export to CRM:\n   \u2022 Add HTTP Request/Webhook node\n   \u2022 Push validated emails to HubSpot/Salesforce\n   \u2022 Keep CRM automatically synced\n\n\u26a1 Rate Limiting (if needed):\n   \u2022 Add \"Wait\" node after validation\n   \u2022 Wait: 1-2 seconds\n   \u2022 Prevents API throttling for large lists\n\n\ud83c\udfa8 Customize Email Design:\n   \u2022 Edit HTML in \"Send Email Report\"\n   \u2022 Change colors, fonts, branding\n   \u2022 Add company logo"
      },
      "typeVersion": 1
    },
    {
      "id": "2816c5ba-112d-4073-b56c-5083ba104b4e",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1936,
        688
      ],
      "parameters": {
        "color": 7,
        "width": 464,
        "height": 896,
        "content": "## TROUBLESHOOTING GUIDE\n\n**Common issues & solutions**:\n\n\u274c \"Column not found\" error:\n   **Fix**: Check **Google Sheet** has exact column names\n   \u2022 Names are **case-sensitive**\n   \u2022 Required: row_number, name, email, status, checked_at, notes\n\n\u274c **Loop processes only 1 email**:\n   **Fix**: Check Google Sheets node returns multiple rows\n   \u2022 Verify \"Range\" field is empty or set to A:F\n   \u2022 Check \"Use Header Row\" is enabled\n\n\u274c **Statistics show wrong counts**:\n   **Fix**: Enable \"Execute Once\" in Calculate Statistics\n   \u2022 Click gear icon on node\n   \u2022 Toggle \"Execute Once\" to ON\n   \u2022 This processes all items together, not one-by-one\n\n\u274c **Email doesn't arrive**:\n   **Fix**: Check several things\n   \u2022 Gmail spam/promotions folder\n   \u2022 Gmail credential is authorized\n   \u2022 Recipient email is correct\n   \u2022 Try different recipient to test\n\n\u274c **Validation API errors**:\n   **Fix**: Verify API access\n   \u2022 Check API key is valid\n   \u2022 Verify quota not exceeded\n   \u2022 Test with known email: test@gmail.com\n\n\u274c **Merge node shows \"missing input\"**:\n   **Fix**: Both branches must connect to Merge\n   \u2022 TRUE branch \u2192 Merge Input 1\n   \u2022 FALSE branch \u2192 Merge Input 2\n   \u2022 Both Update nodes must complete\n\n\ud83d\udca1 Debug Tips:\n   \u2022 Click individual nodes \u2192 \"Execute Node\"\n   \u2022 Check OUTPUT panel for each step\n   \u2022 Use console.log() in code nodes\n   \u2022 Test with 3-5 emails before full list\n   \u2022 Check execution log (bottom panel) for errors"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "b747883f-8107-45ba-be73-ac39e8076e88",
  "connections": {
    "Merge1": {
      "main": [
        [
          {
            "node": "Process Each Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Email List": {
      "main": [
        [
          {
            "node": "Process Each Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Process Each Email": {
      "main": [
        [
          {
            "node": "Calculate Statistics",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Validate Email Address",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Process Valid Email": {
      "main": [
        [
          {
            "node": "Update Valid Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Valid Status": {
      "main": [
        [
          {
            "node": "Merge1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Statistics": {
      "main": [
        [
          {
            "node": "Send Weekly Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Process Invalid Email": {
      "main": [
        [
          {
            "node": "Update Invalid Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Invalid Status": {
      "main": [
        [
          {
            "node": "Merge1",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Validate Email Address": {
      "main": [
        [
          {
            "node": "Check Validation Result",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Validation Result": {
      "main": [
        [
          {
            "node": "Process Valid Email",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Process Invalid Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Weekly Schedule (Friday 5PM)": {
      "main": [
        [
          {
            "node": "Read Email List",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• gmailOAuth2
• googleSheetsOAuth2Api
• verifiEmailApi

About this workflow

Validates email lists through automated checks, categorizes results as Valid/Invalid/Risky, updates Google Sheets in real-time, and delivers HTML reports. Runs every Friday at 5 PM via cron scheduling. Split in Batches processes one email at a time Each email: validate → branch…

Source: https://n8n.io/workflows/9252/ — original creator credit. Request a take-down →

More Email & Gmail workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.