Automated emails pulling email address from within the sheet

I have a form that people fill out to request access to a system, and I automate the sending of an email to a specific address that is filled out on the form. A solution I found through this forum sounded like it might work, but it advised to make a separate reference sheet with all potential Contacts, which is not feasible. This list would require constant updating, as the list of employees I want to send the automated email to would be constantly changing (I want the email to go to a GM of a specific location, and the GMs change over time). I can automate the email sending, but in the automation task, it only gives me a list of people that sheets have been shared with. Is there a way to simply pull an email address from a cell, and customize the message that it sends to include specific sheet information? The information would all be in the same row.

I would want the automated email to read something like this:

"Dear {{GM Email}},

You are receiving this notification because {{Person who filled out form}} is requesting access to ____ system on behalf of {{Future System User}}. This requires your approval. Please respond to this email approving or denying this request within 3 business days."

It would be ideal if they could either A) respond directly to the automated email with approved or denied, and then that answer could be added to a separate column (ie, "GM Approve/Deny"), or B) click on a Approve/Deny radio button in the email, and it gets tracked to the column listed previously.

This is probably alot to ask of Smartsheet, but anyone have any ideas? Can this even be done?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!