Pulling in Variables from a .CSV File to Use in Mail Merges

Hi all,

I am trying to pull in variables from a .csv file to use in a mail-merge type macro e.g. I would like to pull the 'first name' from row 1 column A of a .csv and then pull the 'appointment time' from row 1 column B etc.

I would then like to loop this process for the remaining info in row 2, row 3 etc.

Hope this is clear, the essential use it to send out a series of personalized messages through a browser based system that currently doesn't have a mail merge capability.

Thanks in advance for any help,

1 Like

Hi Keyser

Here is how I would do it.

This prompts you for the csv-file. Drag it into the field.
Then it process each line in to csv in the For Each action.
So any action you need to do with each record, should be placed inside the For Each action. In this example I only display the variables.

Heres is a short video of how it works: Video

This is the macro.

And this is my example csv.


Wow, thank you Jimmy this is fantastic! This has shaved hours off my day and is incredibly helpful. Really love the attention to detail in the response also, very elegant solution.


I would like to thank @JimmyHartington for his detailed answer. I had a very similar problem today :slight_smile: Cheers.

1 Like

Also wanted to thank you for this Jimmy, very helpful.

1 Like

Hey Jimmy, I wanted to thank you for putting this up here. This saved me hours of work. Sending out over 100 email messages with login credentials. This was the ticket!! I added in opening up mail and then sending the messages off. So satisfying watching the emails pop up briefly on the screen and then all the variable information dropped in and it sent off.

My only need at this point is to figure out a way to do this for formatted text. I'll keep tinkering around until I figure it out.

Thanks again!

1 Like

Hi Mike

In another macro I have made formatted emails for Apple Mail.
Maybe not the most efficient way but it works.

Hopefully the actions below can get you in the right direction.

The first Set variable action is HTML for how you want it formatted. You can see that I have put in variables.
The Execute Shell converts the HTML to RTF and puts it in the clipboard.
The Execute AppleScript creates a new email in Apple Mail based on variables as well and then pastes the RTF into the body.
It does not get sent automatically. But I am sure this can be added to the AppleScript.

Keyboard Maestro Actions.kmactions (2.8 KB)

1 Like

Hey Mike,

Apple has never made creating styled email via AppleScript easy, and the ability has been broken since Mavericks (if I remember correctly).

However – with the advent of AppleScriptObjC it became possible again.

The annoying thing is that Keyboard Maestro CANNOT run the relevant script – you have to use FastScripts, or Apple's Applescript Editor, or Script Debugger, or an applet.

(FastScripts' demo version has unlimited menu scripts but only 10 available keyboard shortcuts for them, so it can easily be used for this task without paying $9.95 for the full version. The full version has unlimited app-specific and global keyboard shortcuts for scripts.)

# Auth: Christopher Stone { Heavy Lifting by Shane Stanley }
# dCre: 2013/12/04 22:27 +1100
# dMod: 2018/12/18 05:35 CST
# Appl: Mail, AppleScriptObjC
# Task: Create new styled-text email.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @ASObjC, @Create, @Styled-@Text, @Email
# Subject  :  Re: Mail slow creating html message
# From     :  Shane Stanley <sstanley@myriad-com.com.au>
# Date     :  Wed, 04 Dec 2013 22:27:54 +1100
# To       :  AS users <applescript-users@lists.apple.com>

On 2 Dec 2013, at 5:16 PM, Christopher Stone <listmeister@thestoneforge.com> wrote:
> If memory serves there's no way to make the window visible before sending if you want to send HTML mail.

I wouldn't say no way. Put this in an ASObjC-based library. It also requires that styled email is turned on manually for some reason.

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions

use framework "AppKit"
use framework "OSAKit"
use framework "Quartz"
use framework "WebKit"

set theSource to "<html><body><H1>It can be done</H1><hr><p><a href=\"http://www.macosxautomation.com/applescript/apps/\">Good stuff here</a></body></html>"
set theSource to current application's NSString's stringWithString:theSource
set theData to theSource's dataUsingEncoding:(current application's NSUTF8StringEncoding)
set theStyledText to current application's NSAttributedString's alloc()'s initWithHTML:theData documentAttributes:(missing value)
set mailShare to current application's NSSharingService's sharingServiceNamed:(current application's NSSharingServiceNameComposeEmail)
mailShare's setRecipients:{"s.claus@north.pole.com"}
mailShare's setSubject:"I wish"
mailShare's performWithItems:{theStyledText}


You can also include attachments.

Now what you can then do with the resulting email by script, I really don't know...



Tinkering wouldn't get you here. It took the one and only Shane Stanley.