Insert “pretty” hyperlinks in an Email merge in Excel with AHK

In this Excel tutorial I walk through how to generate a “pretty” hyperlink that is a mail-merge from Excel cells.  This can be very handy when you have, relevant, information for each email that you want to send.

Normally I’d use the offset command (as demonstrated here) and then just select a range however this is the quick/dirty approach.  Having said that, it still gives you a good overview of what is needed.

Code used in video to perform email merge in Excel

XL_Handle(XL,1) ;get handle to application

;***********************insert "pretty" email link********************************
XL_Insert_Email(XL,email:="A2",Disp:="B2",Subj:="C2",Body:="D2",Dest:="E2")
XL_Insert_Email(XL,email:="A3",Disp:="B3",Subj:="C3",Body:="D3",Dest:="E3")
return

XL_Insert_Email(PXL,email="",Disp="",Subj="",Body="",Dest=""){
PXL.Application.ActiveSheet.Range(Dest).Value:="=Hyperlink(""Mailto:"
 . PXL.Application.ActiveSheet.range(email).value
 . "?Subject=" . PXL.Application.ActiveSheet.Range(Subj).Value
 . "&Body="    . PXL.Application.ActiveSheet.Range(Body).Value
 . ""","""     . PXL.Application.ActiveSheet.Range(Disp).Value . """)"
}

;***********Get handle to Excel*******************
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean  Updated by jethrow*****************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
    If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
    Return	ComObjEnwrap(9,pacc,1)
}

Video how to perform the Email Merge in Excel