Join binary data from iCalendar and JSON in an Email

Describe the issue/error/question

I have a Database Query providing Information for a calendar event. This is passed to the Mail node sending the event as attachment - like in your example for the iCalendar Workflow.
What I would like to do is join each item of the query result with each event generated and use the query result fields in the Mail message.
How can I correctly join binary data into an item using a workflow node?

What is the error message (if any)?

Please share the workflow

(Select the nodes and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow respectively)

Share the output returned by the last node

Information on your n8n setup

  • n8n version:latest
  • Database you’re using (default: SQLite):postgres
  • Running n8n with the execution process [own(default), main]:
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]:docker

Hey @Rand_Kepp,

Can you share the data or a simplified workflow that shows what you are trying to do?

Here is the workflow, doing a query - to our ticketsystem redmine having absence information, generating an event and sending a mail.
I would like tot take email from the query and other variables and use them in the mail parameters.

Hey @Rand_Kepp,

I would have expected what you have to work, Do you have an example of the output from the MySQL node? Is it maybe more than one item coming out?

Yes the query can have multiple results:

id firstname lastname address attendance_date attendance_date NAME approved created_on updated_on approved_at description dauer


1575 Peter Zapfl [email protected] 25.10.2022 25.10.2022 Krankenstand / Sick Leave (null) 28.10.2022 10:15:24 28.10.2022 10:15:24 (null) HALBTAG
1577 Hans Hurch [email protected] 31.10.2022 31.10.2022 Urlaub / Vacation (null) 31.10.2022 11:49:32 31.10.2022 11:49:32 (null) TAG
1576 Zap Brannigan [email protected] 11.11.2022 11.11.2022 Zeitausgleich / Compensatory Time (null) 31.10.2022 09:04:15 31.10.2022 09:04:15 (null) TAG

and I would like to use
firstname, lastname, address, etc in the corresponding mailtext.

Hey @Rand_Kepp,

So would that be 3 attachments you want to add to the email or do you have that bit sorted? To build out the message to send you would need to use a code node to loop over the inputs and build out the message so how you did that would depend on the message you are sending but it would start with a loop which the node gives you when you first add it to the canvas.

Hello Jon,

each row in the query result should produce 1 email with attachment.
but i do not now how to get the fields from the query result plus the event.ics file into the mail.

So if I use a code loop “how” do i merge binary data and json content, so that the email node can use it?

kind regards

Randy

Hey @Rand_Kepp,

If you want one email and 1 attachment for each it should sort of just do it, Check out the example below which sends 3 emails one for each cal item and uses the name from the Code node.

Hello Jon,

this partially works, just that values are just taken in subject of mail, not in the message :slight_smile:

Do you have any idea why they are not replaced?

kind regards

Randy

Hey @Rand_Kepp,

At a guess… The text field is not set to expression so toggling that should sort it out for you.