I am on faculty at a university, and I periodically need to email students with advising information. I don’t have many advisees (rarely more than 10), so this is enough that it’s annoying to create each new email the traditional way. I would love to create an email-merge automation short that can take a database file (perhaps CSV) containing student names, email addresses, and some other information (such as options for that student’s course schedule) and merge that into a Drafts document that could then be sent out through email. I’ve played around with this using Drafts and Shortcuts, but I can’t figure out exactly how to make this work. Any ideas?
1 Like
I think you can do this all in Shortcuts. I’ve discovered you can actually pull a list of data from a plain old text file or even a note in Notes, and use the Split Text action to pull out the individual lines as separate data items. Even populate a Dictionary with them if that helps. Should work with a CSV file too I think.
Probably the Repeat With Each action will be helpful.
Split up each line using the newline as the split character (prob need to use regex for that), then further split the line of data using comma as the split character. Store each data chunk into a variable, then string together those variables as needed and compose an email message. Easily done right in Shortcuts.
1 Like
Yes, you can use shortcuts for all of it, but maybe start in drafts for custom part of communication, markdown to rich text conversion can occur in drafts or shortcuts, and distribution could be queued up with dictionary to repeat with a set of contacts, there is a new app Data Jar #data-jar that would allow storage and reuse of contacts, text, even files that could be integrated into that shortcut.
1 Like
Okay. I was able to create a very rudimentary and messy version of the Shortcut, but I have to think there is a better way. This shortcut automatically creates an email sent specifically to any number of recipients, with the text and salutation personalized to the recipient. The default format does not include variables other than name and email address, but other information could be included by further parsing the split line data in the first section. In that case, the section composing the body would also need to be reworked. This shortcut is designed to be used with text sent (in my case from drafts) in the following format for a variable number of recipients:
Email subject
Name 1, Email 1
Name 2, Email 2
Name 3, Email 3
Name 4, Email 4
Email body (written in Markdown)
For one thing, it doesn’t feel as streamlined as I think it could be, although I’m not sure what to remove. More importantly, there’s not an easy way to take additional data (besides the name and email address) from the Shortcut Input and insert it into fields in the body of the email. I would like to have placeholders in the “Email body” text that could be replaced with other data included at the end of each line in the first section. Any ideas?
https://www.icloud.com/shortcuts/75bea5f679804cce902e092eb1d944e4
1 Like
I made something very similar, but it pulls data from a Google Sheets spreadsheet that gets updated as people register with Google Forms. You’ll need the spreadsheet ID, an API key and I’ve found that rich text needs to be converted to HTML format for line breaks to register.
It also uses Regex to convert the JSON data into a string, and then Regex again to create a dictionary that be referenced for name variables. You could trim my version down quite a bit if you only have names and emails, use “Ask for Input” blocks to customise your drafts and store your advisee data in a Google Sheet if you wanted
https://www.icloud.com/shortcuts/86c1eaebbf5f47b3aaac328d6eba3b66
1 Like