• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

Adding / Manipulating / Clearing Borders in Excel with AutoHotkey

Borders in ExcelAutoHotkey Merchandise-White Stress ballIn this tutorial I show how to add / remove / edit Borders in Excel with AutoHotkey.   Below is the code I use to apply the changes.  Be sure to review this if you’re unfamiliar with connecting to Excel via a COM object,

Function for working with Borders in Excel

;~  Note some weights and line styles are not allowed with each other
;~  Weight 1=Hairline 2=Thin 3=Med 4=Thick  ***  Line 0=None 1=Solid 2=Dash 4=DashDot 5=DashDotDot

;***********************Cell Borders (box)********************************.
IfEqual,Line,0,SetEnv,Line,-4142 ; Excel constant for no border
Loop, 4{
PXL.Application.ActiveSheet.Range(RG).Borders(A_Index+6).Weight := Weight
PXL.Application.ActiveSheet.Range(RG).Borders(A_Index+6).LineStyle := Line

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)

Here is the video where I walk through using the function to play with Borders in Excel

AutoHotkey Bottle 4

Create / Connect to Excel with AutoHotkey via COM


AutoHotkey Merchandise-White Stress ballAutomating Excel with AutoHotkey is pretty easy however first connecting with the Excel object can be a bit confusing.

This video walks through both connecting with a running Excel application and creating a new one.  It also walks you through how to set the object visible (and use the Task Manager to see if it exists)

Here is the code reviewed in the video:

Creating & Connect to Excel with AutoHotkey via COM

AutoHotkey Bottle 1

Automating an HTML Outlook email with AutoHotKey

Outlook email

HTML Outlook emailAutoHotkey Merchandise-White Stress ball

With AutoHotKey it is very easy to automate an HTML Outlook email!  In the script below I show how to populate some of the common parameters of the object and insert HTML to the body as well as passing in a variable with time.  You can easily tweak the settings to your needs.

#SingleInstance, Force 
Browser_Back:: ;hotkey for running below
olMailItem := 0
o:= ComObjActive("Outlook.Application").Session()
MailItem := ComObjActive("Outlook.Application").CreateItem(olMailItem)
MailItem.BodyFormat := 2 ; olFormatHTML
MailItem.TO :="Joe@the-Automator.com;joejunkemail@yahoo.com"
;~ MailItem.CC :=""
;~ MailItem.Replyto :="joejunkemail@yahoo.com"

TodayDate := A_DDDD . ", " . A_MMM . " " . A_DD . ", " . A_YYYY ;storing pretty version of date
MailItem.Subject := "email on " TodayDate ;Subject line of email

;***********html body of Outlook email******************* 
MailItem.HTMLBody := "
<H2 style='BACKGROUND-COLOR: red'><br></H2>
<HTML>Your file is here on "(TodayDate)" <br><br>
<span style='color:black'>Please let me know if you have any questions.
<br><br><a href='mailto:Joe@the-Automator.com'>Joe Glines</a> 
;~ MailItem.Attachments.Add(NewFile)

;~ MailItem.Display ;Make email visible
;~ mailItem.Close(0) ;Creates draft version in default folder
MailItem.Send() ;Sends the email

AutoHotkey Bottle 3Here is a video walking through the usage of the above script.

  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey