• Become a Power user Intro to AutoHotkey Intermediate AutoHotkey Intermediate Objects GUIs are Easy w/AutoHotkey Painlessly switch from V1 to V2

Excel & Outlook Mail merge plus Zoom API call to get registrants

Here’s the script I use to do a mail-merge in Outlook using a list in Excel. I also pull Zoom registrants with an API call

Excel automation without macro is easy | Perform “mail-merge” in Excel
Excel automation without macro is easy | Perform


The key idea of the video is that Excel automation can be used to streamline the process of sending follow-up emails, perform mail merges, and personalize emails.

  • 00:00 The speaker demonstrates how they use Excel automation to send follow-up emails to people who register for their weekly Zoom meetings.
  • 00:37 Automating the process of importing and writing emails in Excel by using a function library and opening a CSV file.
  • 01:51 The speaker demonstrates how to automate Excel tasks, such as opening files, deleting columns, and setting column widths.
  • 02:47 With Excel automation, you can easily fix and manipulate names, store header locations for future reference, and open and scroll through documents.
    • The speaker demonstrates how to open and scroll through a document in Excel.
    • People often enter their names in a format that needs to be fixed, but with Excel automation, the names can be easily separated and manipulated, and the location of headers can be stored for future reference.
  • 04:21 This approach allows you to easily perform a mail merge in Excel by selecting rows, highlighting them, and using a hotkey to generate and review individual emails before sending them.
  • 05:49 Specify the email account from which you want to send emails in Excel automation without macros.
  • 06:36 You can create an HTML email in Excel by using a tool to generate the HTML code, making sure to adjust any necessary tags for Outlook compatibility.
  • 07:40 Performing a mail-merge in Excel is easy and allows for personalized emails by formatting the file, adding personal notes, and using tools like Mailgun or the Melbourne API for tracking and attaching files.
    • Performing a mail-merge in Excel is easy and allows for the creation of personalized emails with just a few clicks.
    • You can easily perform a mail merge in Excel by formatting the file, adding personal notes, and using tools like Mailgun or the Melbourne API for tracking and attaching files.

 

hey it’s chalk lines and I just want to demonstrate how lazy I am so I I I’m involved in a meetup group it’s for landlords and because of the co vid issues we decided to have weekly meetings a set of monthly and since we can’t meet in person I offered to use my zoom meeting and so I each week I get a list of people that have registered and of course it changes week to week and I want to send a follow-up email to people that registered not the attendances they registered with links to the videos we
recorded any the chat file any other files or anything and so it’s just a no it’s a little bit of a laborious process to import it each time import the file write an email if I had to I could put us I could put the CC and arrow CC everybody but you know what it’s not professional into is it it’s it’s just hey you know what I can automate this so let me walk through here I’m using my Excel function library that I got in my library folder here is just a reminder these are the things I need to update
each time right here I have a path to a CSV file let’s go ahead and open that on your site to open that so you can see what’s in there this is this is the actual file I download from zoom unfortunately they zoom has an API but they don’t allow for grabbing this file via the API which would have been amazing but anyway it’s fine so it downloads it and it has some stuff in it the approval and status they’re always going to be approved and the date stipend changes which is kind of irrelevant this is just a stacked file
with with my family here I put them in there some to not give away everyone else’s to email addresses but you get the concept right it’s just a comma delimited file I need to open it into Excel so in here in my file I come in here and I reload in case something goes wrong but I can hit ctrl I and it’s gonna launch Excel it’s gonna make it visible it’s gonna use this multi open air path and it’s gonna open that CSV file path and we turn a pointer to Excel actually I probably don’t need this here
but I’m Ono this is the freeze it just looks somewhere something else I freeze the first row after it gets opened and then I look because I don’t want the approval status and I don’t want registration time so I looked in columns a1 through F 1 so I look in the header row anywhere you find these words it’s gonna delete that column that’s a function I’ve built into my Excel library and then I go ahead and choose to set the width of everything in columns a through C to 15 I could have setup minus 1 which is automatic but
some of these aims are much wider than others so that is all when I hit ctrl I let’s go ahead relize see what happens so BAM opens it up frees us I could scroll with free of course it doesn’t matter but I usually have like 20 in here these all came out nice a nice thing about this is some people I noticed they when they entered their name they put it they somehow even though said first name last name they put it like this right so if it’s a common file its gonna import like this and so this allows me having it
separate where I can import it and then after this I can go you know what yeah I can fix it in poops I’m just copy and paste now I can do whatever to it right I can make that little lighter doesn’t doesn’t really matter now the second part this is the part that I really like in excel isso when I hit my AutoHotkey you know let’s let’s move this here and put excel here there we go and I think I can drag this over some and that all adapt there we go so it gets a handle to the current version of excel right here and then it
goes through and looks at the header oh there’s another function I wrote and then stores in this LOC I think that’s an object yeah it stores where these headers are so it’ll store the location for email to store the location for first nameless or the location for last name so that way if things ever move I don’t have to worry about it right it’s automatically gets updated in here I just had a message box I can get rid of this but it would actually show me that where they are that’s not needed anymore I was during
testing in then this is anAuto cool one so this identifies the first row selected so if I if I select like these two it’ll get it’ll actually this function here gets the first one and loops through and they’ll say oh there were two selections start there so what I really like about this approach is I can highlight I can highlight one and hit myHotkey and it’ll write that one email oh I didn’t reload itit had a pause there and let’s see if it’s still here I had this pause here but I realized you
know what because I’m not deploying it and that’s I could have it deploy it but I don’t want to write I like to be able to look at them real quickly so not into me or hit this and here’s that email and I can double check hey is that hyperlink work does that one work I really should click it and let’s make sure it pulls up and pulled up on the other page here you can see a loading great ok I have my email there we had a great call maybe I’d wanted maybe I put in the date or somethinghere’s the
attached file yes the right file so I could hit Send and this would deploy for me oh and also notice it says hi John right that’s because I did a mail merge in here so this is my HTML let me let me keep going down the road here so itit takes the first name and a title cases the first name in case they didn’t uppercase their first name so it didn’t stores it in the first name and then it creates an email object and attaches the file this is the path to that the chat from the session so and I might have a
couple other ones so this is where I would come in here and probably just add another line i duplicate this and add the other paths I believe that works just fine to add one or two more right I’m not gonna have probably more than two so I’m not worried about putting into an object and looping over them or something but I could this is an important one here I have I think like I don’t know six or eight email accounts in Outlook and I want it to come from a specific one so here this from account this line here that’s what this does up
here I have from is to gel with the-Automator right this is where I could change it’s like my gmail or some other account and that makes sure it comes from that account otherwise it would just if you don’t specify it uses your default account here this format that says hey make an HTML email the email who’s gonna go to I’m sending it to the email address that’s what I’ve pulled from the file and then the subject which is gonna be the same but I could dynamically if I wanted to put like their first name in
here something I could and then this is the body which is an HTML file what what I what you could do is like take this and go to a tool like this no this one here we go now here the it would actually generate the HTML I’m gonna do it reverse come on there we go so you can see it here right and I could come in here and bold something and change colors and then I could grab this HTML and and but notice it it actually it’s interesting it dropped out the HTML at the beginning and end which is fine just have to make
sure you understand that you might need to make some tweaks right for this Outlook email I have the tags beginning and ending with HTML right so those and actually this library here I don’t need there’s no reason to have one there because that’s the end of the file so I don’t know why I have an extra one there but you get the idea right it makes a nice-looking close this now let’s do all three of these at the same time and I can click anywhere it doesn’t matter I’m clicking here I can click
anywhere and this will write the three so here are my three it was just fast so there’s the one to John and there’s to Johnny right I’m gonna close it otherwise I could just deploy here’s one to Kathy and Kathy and there’s the one to me Joe and Joe so now it’s it’s simple I can hit a couple buttons double check the file is the way I want on the names the way I want it and of course it the paths are proper and I’m done I can hit Send it’s a nice easy way to be done the other really cool thing is in
Outlook I’ll have each one of these you know have 20 emails pop or I can do them one at a time whatever I want right I can I can click on a couple do those then do the next couple do those however I want it right I love how it interacts with the two but what’s really cool is with this approach I could you know if I had something to say to Kathy when her email is here I could I can click in here and then I can write a note you know I can add a personal note and it’s everyone gets the vast majority the same thing but I can tweak
it right so I really like this kind of approach the other cool thing I don’t have it set up but you could mailgun allows for sending through Outlook so if I wanted tracking on who clicked what I could send this through mail gun because it can actually you can put in the SMTP servers for Outlook will deploy through mail done and I could get a I can see who opened who clicked it how many times they clicked it it’s it’s sometimes you know you want that kind of level information the other option is I I have
a tool using the Melbourne API which I could use that in that one I didn’t have a way for it to attach files and so I went this route for now but I might update my other ones where I can attach a file so anyway just thought I’d share that with you Cheers

Comments are closed.