NBS – Schedule talks to be sent

How to Schedule Audios to be sent by Email

Is it worth the effort?

  • Sure is!
  • You want results right?
  • Often it’s the seemingly small things that make a big difference.

What would I use this for?

  • When you start coaching people, it’s a great idea to send them an audio.
  • The audios help keep fore of mind what needs to be fore of mind.
  • Audios inspire people, give them ideas, help them with penny-drop moments and help you build the relationship with them.
  • Receiving an email with a link to an audio on their phone is easy for them – they just have to click on the link.
  • And for those who are engaged in regular coaching, receiving an emailed talk is just as good as an SMS. They can save the email till they have listened to it.
  • After a while, you’ll find the people you are coaching will become ‘addicted’ to listening and find their attitude and daily results improve. This makes it much easier to transition them into listening to ‘Daily Listening’ themselves (with enough prompting from you)

What audios should I send & what audios have I already sent?

  • This is where scheduling really comes into its own
  • You can schedule a list of emails containing a short message promoting the audio plus a link to the audio. This way you also have a record of what you have sent them and you can see what is about to be sent.
  • Have a look at the ‘Mail Merge Audios’ tab for an ever-expanding list of great audios to send.
  • The top 40 rows (or so) contain recommended audios which may be best to send to new people.
  • The rows after that could be good to help people advance and get better results (how to become more consistent, manage time, manage emotions, etc)
  • You can also use the ‘Mail Merge Audios’ tab to copy a link to a talk and write your own SMS or email containing that link – this is especially useful if someone is attending but delaying joining the NBS for some reason (sometimes though even for these guys, you may want to schedule a list of things to send them.

How to get a list of Audios by Author

  • Let’s say you wanted to get a list of talks done by Jason Ross or Matt Kreis.
  • When you are in the ‘Mail Merge Audios’ tab, it’s easy to find all talks by an author as follows:

How the mail merge add-on works!

  • Hang in there – it’s not that hard and it’s worth understanding – like everything, when you take the time to understand how it works up front, it’s easy thereafter!
  • The Google Sheets Add-on that makes all this work requires:
    • That the Add-on is installed (we’ll discuss this below)
    • That you have a draft email present in Gmail (that’s easy and is also discussed below)

We cover how to set Mail Merge up below  – first, you need to understand the columns in the ‘Mail Merge List’ Tab

  • Following is a picture of the leftmost columns (where you need to add) :
  • When your ‘Mail Merge List’ tab is brand new it will come with 1 example row.
  • When you are ready to schedule your first audio email, overtype the values in this sample row for First Name, Last Name, Email Address and Talk ID with the values you want.
  • To schedule more emails, copy and paste this row into the next blank row.
  • First name, Last Name, Email address – enter the details of the person you want the mail to go to.
  • Talk ID. Simply enter a valid ID (one that exists in your ‘Mail Merge Audios’ tab – ideally copy and paste the ID(s) from the ‘My Favourite Audios’ tab).
  • After entering a valid ID in the Talk ID column, the ‘Talk Heading’ and ‘My Message’ columns will be automatically filled in for you.
  • Scheduled date
    • If schedule date is blank, an email will be sent when you run the Mail-Merge add-on
    • If you want the email to be scheduled to be sent at a future date/time, enter something like 08/06/19 8 pm. This is 8 pm on the 8th of June 2019.
    • When you type this, Google sheets will automatically convert this to 08/06/18 20:00
    • The add-on checks the sheet roughly every half-hour and sends anything that has a scheduled date prior to the date the add-on checks.
    • Note that anything with a blank date will not be sent (this only happens when the add-on is manually run)
    • Most importantly, the add-on will automatically check what needs to be sent even if you don’t have your sheet open and even if  your computer is turned off (it happens automatically in the ‘cloud’)
    • This means you can change the talk ID right up to the time the email is scheduled to go (this could be handy if you have just had a coaching session and decide that another talk might be more appropriate – or you could just insert a row and send another one as well!)
    • It also means you can delete emails that are scheduled into the future simply by deleting the row.
  • Your Personalised Message
    • This is where you type a message that you would want to go just after the person’s name in the email to be sent.
      • For example: ‘It’s great to hear how much you are enjoying the audios. Here’s another I thought you’d enjoy.
    • Note: Following this the system will automatically insert the standard description for the audio
  • Adding or inserting rows to schedule more emails
    • If you want to add a row at the bottom of the sheet
      • Simply copy the row above (right click on the row number and copy) and paste it into the row below.
      • In this case, make sure you change all relevant details in the copied row, (name, Talk Id, etc) AND also delete the entry in the ‘Mail Merge status’ column (otherwise the email may not be sent properly!)
  • Hints:
    • It’s good to have all emails for the same person grouped together. To achieve this, you can insert a row after the last email you sent them (right-click on the row below which you want to insert a new blank row and Select ‘Insert 1 below’). Then copy and paste as described above into the newly inserted blank row. Then change details as required (especially ‘Mail Merge status’ – must be blank)
    • If you are sending someone a series of talks – let’s say 1 a week, rather than manually typing in the days you can do it with a simple formula. In the above screenshot, the dates are exactly 1 week apart. This was achieved by manually entering the date in cell F1 and then entering the following formula in cell F2: ‘=F1+7’. This simply adds 7 days to the date in F1.

After you have entered the above details, the message shown in the following screenshot will be automatically constructed:

Note: the <br>’s in the above messages are simply there to create line breaks in the email so just try to ignore them when you are reading the constructed message.

Here is a sample of what the email looks when it is sent (more on how to set up the email template below):

This is produced from a simple draft email that looks like this:

Here’s what you need to do to set up everything described above – Step-by-Step

Note: Once you have done this setup, running it thereafter is easy!

(1) Set up some test data in the ‘Mail Merge List’ tab

  • Set up some talks to be sent and put in your own email address so you can run a test and see how it works with emails just being sent to yourself to start with.
  • Initially, leave the schedule date blank so that the emails will be sent to you immediately.
  • Later, you can try scheduling some to yourself to see how that works
  • After each test, remember to clear (delete) the data in the ‘Mail Merge Status’ column – otherwise that row will be skipped when you run the the add-on.

(2) Set up a Draft Gmail which looks exactly like the one above (you can fiddle with it if you want to change the wording but make sure the {{…}} are as shown above. During processing these variables will be replaced by data from the spreadsheet.

(3) Install the Google Add-on ‘Mail Merge with Attachments’. To do this, follow the process shown in following screenshots:

If you see ‘Mail Merge with Attachments’ in the list, click on it.

If it says: ‘Instal’ click that and answer all the questions about permissions.

If ‘Mail Merge with attachments’ does not appear in the list, Click ‘Get add-ons’ and proceed as follows:

In the Search bar at the top, type in ‘mail merge’ and select the following:

Then go through the installation procedure.

(4) Run your first test as follows:

Premium Version: The premium version is inexpensive (about $29 per year). So you could see this as a contribution to the software developer who has created a great add-on! It will also allow you to remove the ‘powered by mailmerge’ advertising from your emails and allow you to send more than 50 emails each day.

When you press ‘Configure Mail Merge’ (see screenshot above)

The following screen will be displayed:

Mail Merge Sheet Selection

At the top of the screen, you have the ability to select one of the tabs in your Activity Sheet

Make sure this is set to ‘Mail Merge List’

If not, use the drop-down box to select this tab

Then fill in Senders Name etc as required.

And then press ‘Continue’

The following scrren will then be displayed:

Press Continue again and the following screen will be displayed:

Ckick the ‘EDIT’ as shown in the above screenshot and the following will then be displayed:

If the correct Gmail draft (the email draft you created to work with Mail Merge) is displayed, you can press Save and continue.

Otherwise, press the drop-down box and choose the correct draft.

Note: if you have not yet created an email draft, see above for the format required – quite simple and intuitive.

After pressing save you will be taken back to this screen:

Press Continue and the following screen will appear:

You can Send a test but if you are just sending something to yourself in the ‘Mail Merge List’ tab, you may as well ‘Run Mail Merge’ as a test.

After running, the ‘Mail Merge Status’ column in your spreadsheet will be updated. Following is an example screenshot:

That’s it – you are off and running!

Play around a bit and you’ll soon see how it all works – any questions, ask your coach

Have fun!

The Admin Team