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) }