How to send a list of items via single E-Mail

Hi,

i’m trying to send a single E-Mail that contains the result of a table query. My flow looks as follows:

grafik

But when i execute it, the SeaTable node returns 10 items and thus the Send Email node sends 10 E-Mails. But what i want is to send a single E-Mail with all items listed inside the body of the mail. How can i do that?

Thanks

Hi @mfreudenberg, welcome to the community :tada:

If you’re getting a relatively simple structure from SeaTable, the Item List’s Aggregate Items operation might be what you’re looking for. It merges multiple items into a single new item which you can then use in your email.

Hi @MutedJam ,

thanks for the reply. I should have mentioned, that i already tried that node. As it seams, it is meant to aggreagate columns, but not items to a single value. Maybe i have misread the docs?

Maybe there’s some misunderstanding here - could you share an example of the data returned by your SeaTable node?

As for the Item Lists node, maybe this example makes it easier. Say I have these three items with a value column:
image

The Item Lists node can easily convert them into a single item like so:
image

So three items become one. You can copy and paste the below example into your n8n canvas to try this yourself:

Example Workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{\n  json: {\n    value: 'foo'\n  }\n}, {\n  json: {\n    value: 'bar'\n  }\n}, {\n  json: {\n    value: 'baz'\n  }\n}]"
      },
      "name": "Set Example Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "operation": "aggregateItems",
        "fieldsToAggregate": {
          "fieldToAggregate": [
            {
              "fieldToAggregate": "value"
            }
          ]
        },
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set Example Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Example Data": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

More complex transformations are also possible but might be a bit more tricky. In this case the example of your data would be very helpful.

Your data is a table with one colum. But my data has multiple columns. All i want to do is to aggregate it into one string, so i can send a single E-Mail.

I have modified your example to contain two columns. What the list item node does is to aggregate all items from name column into one array and aggregate all items from color column into one array.
The output is still an array and would lead to two E-Mails beeing send out.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{\n  json: {\n    name: 'alice',\n    color: 'blue'\n  }\n}, {\n  json: {\n    name: 'bob',\n    color: 'red'\n  }\n}, {\n  json: {\n    name: 'john',\n    color: 'yellow'\n  }\n}]"
      },
      "name": "Set Example Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        440,
        300
      ]
    },
    {
      "parameters": {
        "operation": "aggregateItems",
        "fieldsToAggregate": {
          "fieldToAggregate": [
            {
              "fieldToAggregate": "name"
            },
            {
              "fieldToAggregate": "color"
            }
          ]
        },
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        610,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set Example Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Example Data": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

In your example, the Item Lists node returns a single item with two fields. The Send Email Node would run once when passing on this item (and you could reference both fields in your email text/html via expressions):

Example Workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{\n  json: {\n    name: 'alice',\n    color: 'blue'\n  }\n}, {\n  json: {\n    name: 'bob',\n    color: 'red'\n  }\n}, {\n  json: {\n    name: 'john',\n    color: 'yellow'\n  }\n}]"
      },
      "name": "Set Example Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "operation": "aggregateItems",
        "fieldsToAggregate": {
          "fieldToAggregate": [
            {
              "fieldToAggregate": "name"
            },
            {
              "fieldToAggregate": "color"
            }
          ]
        },
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    },
    {
      "parameters": {
        "fromEmail": "[email protected]",
        "toEmail": "[email protected]",
        "text": "=Names: {{$json[\"name\"].join(', ')}}\nColors: {{$json[\"color\"].join(', ')}}",
        "options": {}
      },
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 1,
      "position": [
        850,
        300
      ],
      "credentials": {
        "smtp": {
          "id": "14",
          "name": "SMTP Account"
        }
      }
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set Example Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Example Data": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Item Lists": {
      "main": [
        [
          {
            "node": "Send Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

I’ll have to check this out. Not sure if this is how i need the data. At least i wouldn’t send out n E-Mails.
To me this looks like the “table” is then virtually transposed.
I’ll try it out and report back.

Edit: Typo

So, what you have suggested was not, what i had in mind. I have written a small function block that demonstrates, what i had in mind. Please see below.

I am serializing the rows into a CSV like table and store it in the table ouput file. I think i’ll do something similar with a preformatted HTML table. I’ll store that in the html key.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -310,
        290
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{\n  json: {\n    name: 'alice',\n    color: 'blue'\n  }\n}, {\n  json: {\n    name: 'bob',\n    color: 'red'\n  }\n}, {\n  json: {\n    name: 'john',\n    color: 'yellow'\n  }\n}]"
      },
      "name": "Set Example Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -80,
        290
      ]
    },
    {
      "parameters": {
        "fromEmail": "[email protected]",
        "toEmail": [email protected]",
        "subject": "Test from n8n",
        "text": "=Hi,\n\nthis is my \"virtual table\":\n{{$node[\"Function\"].json[\"value\"]}}\n\nbye! 🖐",
        "options": {}
      },
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 1,
      "position": [
        350,
        290
      ],
      "credentials": {
        "smtp": {
          "id": "4",
          "name": "SMTP account"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nlet strValue = \"\";\nfor (item of items) {\n  strValue += item.json.name + ',' + item.json.color + '\\r\\n'\n}\n\nlet returnArray = []\nreturnArray.push({json: {key:\"table\", value: strValue }})\nreturn returnArray;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        170,
        290
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set Example Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Example Data": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Send Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
1 Like

Sounds like you found a solution then, thanks a lot for sharing!

1 Like