Use AutoHotkey to do a “mail-merge” in Excel and send emails through Outlook

In the below video I demonstrate how I leveraged my XL function library to automate importing a CSV file of people that attend my weekly meeting on Zoom and write them custom emails in Outlook using AutoHotkey.  Make sure you get the XL file function and put it in your library so you can use the #Include command.

Use AutoHotkey to do a “mail-merge” in Excel and send emails through Outlook walk-through

 

Use AutoHotkey to do a “mail-merge” in Excel and send emails through Outlook


#SingleInstance,Force
#Include
MsgBox Update: Hour One`nHour two`nchat path`nRegistration Path
Subject_Line:="May 4th Landlord Mastermind meetup"
Hour_One:="https://www.dropbox.com/s/video.mp4" ;?dl=0
Hour_Two:=""
Chat_Path:="C:\Users\Joe\Dropbox\Guests\Landlord\2020_05_04\chat.txt"

From_Account:="Joe@the-Automator.com"
Browser_Forward::Reload
;**************************************
Browser_Back::
XL:=XL_Handle(1)
loc:=XL_Find_Headers_in_Cols(XL,["email","First_Name","Last_Name"]) ;pass search terms as an array and store locations in loc Object ;~ MsgBox % loc.email A_Tab loc.First_Name

First_RWSL := XL.Selection.Row ;Identifies first row selected
Loops:=XL.Selection.Rows.Count ;returns number of rows selected ;~ MsgBox % First_RWSL a_tab loops
;***********now loop over active rows*******************
loop, % XL.Selection.Rows.Count {
email:= XL.Range[loc.email . (First_RWSL + a_index-1)].value ; email address
First_Name:= XL.Range[loc["First_Name"] . (First_RWSL + a_index-1)].value ; First Name
First_Name:=Format("{:T}",First_Name) ;Titlecase first name

MailItem := ComObjActive("Outlook.Application").CreateItem(0) ;Create email
MailItem.Attachments.Add(Chat_Path) ;Attach file
MailItem.SendUsingAccount := MailItem.Application.Session.Accounts.Item[From_Account] ;Set which account to be deployed from
MailItem.BodyFormat := 2 ;html format
MailItem.TO :=email ;
MailItem.Subject:=Subject_Line
;************************************************************
Body =
(

Hi %First_Name%,


We had a good call in this evening at the Mastermind meetup!


We decided to skip next Monday's meeting. The next meeting will be May 18th.

Starting in July we're going to meet on the 1st and 3rd Monday of the month.


You can watch the meeting here. We did not have a second hour.


Cheers

Joe Glines
)
MailItem.HTMLBody:=body
;~ MailItem.Display
MailItem.Send
Notify().AddWindow(email " sent",{Time:2000,TitleSize:16,size:14})
}
return

;********************Import***********************************
^i::
XL:=XL_Start_Get(1,1)
API_Token:=""
EndPoint:="https://api.zoom.us/v2/meetings/820000540/registrants?page_size=100" ;page_number=2
data:="First_Name`tLast_Name`temail`r"
Registrants:=API_Call("GET",Endpoint,QS,API_Token)
if (Registrants.page_count>1){
MsgBox Crap- there are multiple pages. `n`nDeal with it!
return
}
for k, v in Registrants.registrants
data.=v.first_name a_tab v.Last_Name a_tab v.email "`r"

Clipboard:=data
Notify().AddWindow(Clipboard,{Time:3000,Icon:300,Background:"0x1100AA",Title:"Done",TitleSize:16,size:14})
XL.Application.ActiveSheet.Range("A1").PasteSpecial(-4104)
XL_Freeze(XL,"1") ;Freeze header row
XL_Format_Format(XL,"A1:C1",1)
XL_Format_Cell_Shading(XL,"A1:C1","19")
XL_Col_Width_Set(XL,"A:B=25|C=35") ;-1 is Automatic
return

;********API call**********************
API_Call(Type,Endpoint,QS,API_Token){
HTTP:=ComObjCreate("WinHttp.WinHttpRequest.5.1") ;Create COM Object
HTTP.Open(Type, EndPoint QS) ;GET & POST are most frequent, Make sure you UPPERCASE
HTTP.SetRequestHeader("Authorization","Bearer " API_Token) ;Authorization in the form of a Bearer token
HTTP.SetRequestHeader("Content-Type","application/json") ;JSON
HTTP.Send(Payload) ;If POST request put data in "Payload" variable
Response_data:=HTTP.ResponseText ;Save the text that is returned
oAHK:=ParseJSON(Response_Data) ;Make sure the ParseJSON function is in your library
DebugWindow(Obj2String(oAHK),1,1,200,0)
Return oAHK
}

Comments are closed.