Automation Email Attachment Options

Katy Miller

Hello,

I am not sure if this is even possible, and not normally how the automation process is used, but its worth a shot.

Is it possible to set up an automation to email someone form a contact column, with the email containing information from another sheet when a form is submitted?

For reference, the security company I work for is trying to set up some Standardized Interview Questions based on position within the company. We were going to try to set up a form where they select the position that they are going to hold interviews for (i.e. supervisor, general guard, gap of employment questions, etc.), then try to have it automatically send an email with a list of questions that we will have in another sheet based on their selection.

So they would fill out a form and hopefully receive an email with the questions from another sheet. This way when they fill out a form and select something along the lines of gap of employment and supervisor it will pull all of the questions from a separate sheet with the correct questions associated with the supervisor and gap of employment. Same for if they submit security guard questions, etc.

Another catch is not everyone in our company has access to smartsheet, so that is why email is ideal.

Any thoughts would be awesome.

Paul Newcome

It may be possible using formulas with cross sheet references, but...

What information is captured on the form and how exactly is the "Question Sheet" laid out?

Are all of the pertinent questions across a single row, or does each position's questions run down a column?

Are you able to provide some screenshots with sensitive/confidential data removed, blocked, and/or replaced with "mock data" as needed?

Katy Miller

So here is a sample of the question sheet.

Here is the sheet that is attached to the form

They select which positions they are hiring for/ specifications for other questions, and my hope is to somehow set up a way to set automation to email the questions corresponding to the check boxes they select.

I can change how this is set up, this isn't set in stone by any means, but I would like to be able to add/change questions as they come up, and add categories as necessary as well without too much hassle.

Thank you for your time!

Paul Newcome

How many questions do you expect to have in total?

Katy Miller

Its hard to say, I don't have an exact number, and the number could change weekly/monthly pending on positions that may be added.

Paul Newcome

Ok. I have a few ideas, but I am going to test them first. I will get back to you once I have sorted out some of the details.

Katy Miller

Thank you so much for your help and time Paul, I really appreciate it!!

Paul Newcome

Quick question...

Who is filling out the form? Is it the applicant or the manager?

Katy Miller

Local Human Resource managers throughout the US will fill out the form to get the questions. A VP of HR and upper management will be the ones keeping the questions and information in the sheets up to date.

Paul Newcome

Ok. Let me get back to the testing, and I will keep you posted on what our options are for solutions.

Paul Newcome

How many positions do you anticipate having open at once?

Part of the solution I am working through now involves "pre-filling" columns to account for a maximum number of positions at any one given time. So if you think you will never have need for more than 10 columns based on your screenshots, then we could build in 15 just in case kind of thing.

Katy Miller

In regards to different types of questions for different positions I would say upwards toward 15 to 20 due to all the types of positions we have.

Paul Newcome

I think I may have figured something out that doesn't require pre-filling any extras, but I want to make sure the level of effort to manage will work for you.

Whenever a new question is added, it is added to the Questions sheet in the Questions column and then the appropriate box(es) are checked in the position column(s) to indicate which position(s) the question is for. The question row will also need to be indented 1 time so that it is a child of row 2 (the screenshots may help make that a little more clear).

Whenever a new position is added, a new column is added to both sheets. In both sheets the position (column header) is replicated in the first row, and the columns must be inserted "in the middle" of the current range. I added two indicator columns to help keep that part straight.

In the Questions sheet the formula in the second row is dragfilled over from one of the adjacent columns.

In the Form sheet the column will need added to the form.

If you are replacing a column with a new position, then you can just update the column headers and first row in each of the sheets.

Once the form is submitted, the formulas will pull all applicable questions into a Questions column on the form sheet where you can set up an automation to send the data in that column to whomever.

Feel free to ask any questions you need for clarity about the operation and management and take a look at the screenshots. If it seems like something that is easy enough to mange and outputs the desired result, I'll be happy to walk you through the setup. It is actually relatively straightforward with formulas that aren't TOO long and a little bit of conditional formatting.

Screenshots:

Form Sheet:

Questions Sheet:

Katy Miller

Thank you! This looks awesome!

Few questions:

What formula did you use to pull the questions in from the questions sheet to the forms sheet, and with automation can I get it to send the questions column based on their form?

Some general set up information on how to get the form side to work would be great!!

Paul Newcome

I didn't want to spend too much time on details if it didn't look like it would work for you, but now that you have confirmed it looks good, here we go...

First we will start with the Questions Sheet:

We are going to insert a helper row at the top where we will manually replicate the positions in accordance with the column headers.

Next we insert another row below that one and in each of the position columns we are going to use:

=JOIN(COLLECT(CHILDREN($Questions@row), CHILDREN(), 1), CHAR(10))

Then we want to indent each of the actual questions below this row 1 time so they are child rows of row 2.

I added 2 "range" columns that help keep things where they need to be for the formulas to work.

For the two "range" columns that indicate where to enter new positions, I used a symbol type column and then selected the fast forward and rewind arrows accordingly. You can use whatever you want for this. I also used conditional formatting to change the cell fill to orange whenever those particular columns have those particular symbols in them. That part is also entirely up to you, but you are definitely going to want those columns to be there.

And that's it for the Questions Sheet.

Now we can go ahead and move on to the Form Sheet:

Insert a helper row at the top and manually enter each of the positions to correspond with the column headers.

You can see which columns were added.

For the two "range" columns I just replicated what I did on the Questions Sheet.

The Categories column (or Positions column) basically just collects which positions were checked off by using:

=JOIN(COLLECT([Add New Positions to the Right --->]$1:[<--- Add New Positions to the Left]$1, [Add New Positions to the Right --->]@row:[<--- Add New Positions to the Left]@row, 1), ", ")

And next the Questions column pulls the questions using this formula (please note the cross sheet reference names indicate exactly what is referenced):

=JOIN(COLLECT({(?'s) Automation Email Attachment Options Row 2}, {(?'s) Automation Email Attachment Options Row 1}, CONTAINS(@cell, Categories@row)), CHAR(10))

Next we go into the form itself and make sure the position columns are all added. You are going to want to add the General column, make it hidden, and use a default value of checked. This is assuming that ALL applicants get the General questions along with the more position specific questions based on the check boxes they select.

And finally we set the automation up so that there are no columns sent using the advanced options. We are going to instead send the data via placeholders just to make sure it all comes through cleanly. So go on down to where you can customize the message and type out what you want it to say, but replace the section where you would list out the questions with a placeholder referencing the Questions column.

Based on my screenshots above, you could use a "custom message" that looks like this in the Automation:

"Manager Name: {{Manager Name}}

Date: {{Date}}

Positions Selected: {{Categories}}

Applicable Questions:

{{Questions}}"

Then the email that is received from the automation would look like this:

"Manager Name: Paul Newcome

Date: 09/02/20

Positions Selected: General, Supervisor, Gap

Applicable Questions:

What is your name?

Have you worked in the Security industry before?

Describe your work history.

Why did you leave your previous job?

What supervisor experience do you have?

How many employees have you supervised?

Why is there a gap in your employment?"

Basically the placeholders will pull the data from the referenced column(s) and insert it into either the subject or the body (wherever you put them) based on whichever row triggered the automation.

Feel free to take your time setting this up and ask any questions you need and then definitely let me know how it feels after you have had a chance to test it.

Katy Miller

I am in the middle of a project, I will test it later today.

Thank you so much, I will let you know how it goes and if I have any questions/concerns!