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

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.

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

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

I’m having the same issue, and I found a very similar solution with a raw javascript function.

But I feel there should be a node made in purpose to take a JSON object array (i.e. typical output from a SQL select) and transforming it into a single, aggregate string representing a readable HTML table ready to be sent via an email message.

Or is there anything like this and I missed it?

I’m completely with you. I also think, that this should be a default block. I was surprised, that it wasn’t.
I think it should be fairly easy to implement such a block as this functionality is already contained in almost every block. You can switch in the result pane between a JSON view and tabular view. And I guess, that the tabular view contains the code we need.

1 Like

I was thinking about this yesterday, I also thought the list node would help me with this.
I think is a good use case to improve the Item Lists node.

Some API’s allow you to send data in bulk (which is cheaper in terms of hourly/daily use quotas) access the API once vs 50 times for example.

I would also like to know how this can be handled in a new node development for example and not let this pre-processing work for the user or maybe at least a way to let know about this.

What do you think about this @MutedJam ?

1 Like

I like this idea very much and I know for a fact that @maxT is paying close attention to the data transformation challenges here on the forum in order to improve the n8n user experience going forward.

So while I don’t think there are definitive plans just yet, improvements are on the horizon :slight_smile:

2 Likes

@MutedJam @maxT Let me know if I can give a hand.

1 Like

Thanks everyone for IDing this usecase and agree it should be possible without writing js since it’s broadly useful.

To paraphrase in my own words, we basically need to be able to concatenate an array of strings (or numbers) into a single array, and separate each item by some string (like , ).

I see a few places where this functionality could live (and also maps to planned work on our roadmap).

The first would be a string transform core node, so you’d first use Item Lists to create the array, then the transform node to output the single string.

The second, would be making this available in expression editor. There’s a planned overhaul of expressions, and some form of no-code functions (or autocomplete w/ reference syntax) is envisioned, so I could see this being one of them.

To everyone in this thread, which method would you prefer and why?

Hey @maxT in my case I’d like to go from N items to a single item containing a list of all values from the input items.

Example:

item1: {json: { "name": "Sergio"}}
item2: {json: {"name": "Max"}}

go to:

item: {json: [{"name": "Sergio"}, {"name": "Max"}]

I don’t know if that’s also the intention of OP, at least that’s how I understood it.

Hi,

my intention is quite simple. I receive an array of items and want to format or transform them in a way, that i can send the whole array as a table via E-Mail (or other messaging blocks like matrix).

This does not only involve “merging” multiple array items into one virtual tiem, it also involves transforming from the json format into something “human readable” like HTML.

This might be done in one step (block) or in two. Due to the fact, that this is actually already part of the n8n, my thought was it should be fairly easy to cut out this functionality and make a block out of it.
→ each block has the ablity to display the response as the json representation or as table. The table thingy is what i basically want to use for the body of my message (be it E-Mail or Matrix or whatever).

@mcmaxter Your block is basically step 1 in that transformation.