Often I have client data in a spreadsheet and I need to reach out to various clients about the projects I’m working on. In this example I demonstrate how to use Offset in Excel with AHK.
In the video I mention how I also use Offset in Excel in conjunction with a Outlook to write “pretty” HTML emails. Here’s the link to a good demonstration of it.
Below is the code I demonstrate creating the mail merge with Offset in Excel
XL_Handle(XL,1) ; pointer to Application XL_Handle(XL,2) Pointer to Workbook
XL_email_Offset_Col(XL,RG:="E2:E4",URL:="-4",Friendly:="-3",Subj:="-2",Body:="-1")
;***********************Insert email OFFSET in Columns (data in rows)********************************.
;Neg values are col to left / Pos are col to right
XL_email_Offset_Col(PXL,RG="",URL="",Friendly="",Subj="",Body=""){
For Cell in PXL.Application.ActiveSheet.Range(RG){
Cell.Value :="=Hyperlink(""mailto:" . Cell.offset(0,URL).Value
. "?Subject=" . Cell.offset(0,Subj).Value
. "&Body=" . Cell.offset(0,Body).Value
. """,""" . Cell.offset(0,Friendly).Value . """)"
}
}
;***********Handle*******************
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)
}
