Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

MS Office Automation Functions (via COM) [thanks Sean]


  • Please log in to reply
132 replies to this topic

Poll: Should this be continued? (29 member(s) have cast votes)

Should this be continued?

  1. Yes (28 votes [96.55%])

    Percentage of vote: 96.55%

  2. No (1 votes [3.45%])

    Percentage of vote: 3.45%

Vote Guests cannot vote
tank
  • Administrators
  • 4345 posts
  • AutoHotkey Foundation
  • Last active: May 02 2019 09:16 PM
  • Joined: 21 Dec 2007
by default range accepts a1c1 format as apposed to r1c1 format
column needs to be a letter reverence
other wise you can use cells instead which is cells.item[c].item[r] to use numeric references to single cells


let me know if you need more detail
Never lose.
WIN or LEARN.

stressbaby
  • Members
  • 153 posts
  • Last active: Aug 10 2015 09:41 PM
  • Joined: 17 Aug 2009
beautiful, thanks
COM_Invoke(pxl, "Cells.item[" pasteStart "].item[1].Select")


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
I'm just experimenting with the concepts here to get an idea of how to use COM with Outlook. I'm trying to convert some sample code from the Outlook 2003 VB Language Reference manual:

SendReply()
	Dim myOlApp As Outlook.Application
	Dim myNameSpace As Outlook.NameSpace
	Dim MyItem As Outlook.MailItem
	Dim myItem2 As Outlook.MailItem
	Dim myAction As Outlook.Action
	Set myOlApp = CreateObject("Outlook.Application")
	Set myNameSpace = myOlApp.GetNamespace("MAPI")
	On Error GoTo ErrorHandler
	Set MyItem = myOlApp.ActiveInspector.CurrentItem
	For Each myAction In MyItem.Actions
		If myAction.Name = "Reply" Then
			Set myItem2 = myAction.Execute
			myItem2.Send
			Exit For
		End If
	Next myAction
	Exit Sub
	ErrorHandler:
		MsgBox "There is no current item."
End Sub

I tried to use ahklerner's Outlook_GetMessageText() function as a template to work from but without much luck. Particularly I'm stuck here:

Dim MyItem As Outlook.MailItem
Dim myItem2 As Outlook.MailItem


I can't seem to find a way to connect to the MailItem object. I first tried this:

if !myItem:=COM_GetActiveObject("Outlook.MailItem")
  MsgBox Could not get MailItem.
if !myItem2:=COM_GetActiveObject("Outlook.MailItem")
  MsgBox Could not get MailItem.

But that threw the message boxes(produced no COM errors). So I switched to this instead:

Outlook:=COM_GetActiveObject("Outlook.Application")
if !myItem:=COM_Invoke(Outlook,"MailItem")
  MsgBox Could not get MailItem.
if !myItem2:=COM_Invoke(Outlook,"MailItem")
  MsgBox Could not get MailItem.

But still, no dice but this time I received a COM error prior to the message box("Unknown Name").

My experience with Visual Basic is virtually non-existent but I understand the gist of what the script is doing. Eventually I connect to the Name properties for the Action object and loop through them until I find 'Reply' then Execute the Reply action. But connecting to the MailItem object has got me stumped. Any ideas?

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007
Outlook.MailItem is sort of namespace, just part of the declaration of the variable. Just start from Set myOlApp ....

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
Well now that I can skip that I've gotten a little farther, I'm assuming that ahklerner's step-by-step style can be condensed to this?

if !Outlook:=COM_GetActiveObject("Outlook.Application") {
    MsgBox Could not start Outlook instance.
  COM_Invoke(Outlook,"ActiveInspector.CurrentItem.Actions[Reply].Execute")

I've run the script both as a step-by-step check and straight down the dotted path as above. When I run it while a mail item is active I get the warning from Outlook that "a program is trying to execute an action or verb on your behalf..." but it never actually executes the Reply command. Am I missing another step? In addition, how would I extract a list of the available actions on a mail item?

EDIT: I thought it would be worth mentioning that I assumed the above would be the correct COM statement based on some other information I gleaned from the VB reference:

Set myOlApp = CreateObject("Outlook.Application")
myItem = CreateItem(olMailItem)
Set myReply = [color=red]myItem.Actions("Reply").Execute[/color]

I have also gotten this to generate the Outlook warning (but not execute Reply) as well so I assume it is the equivalent of the above:

COM_Invoke(Outlook,"ActiveInspector.CurrentItem.Actions.Item[Reply].Execute")

Strangely enough, I was able to send an email by modifying the code like this:

COM_Invoke(Outlook,"ActiveInspector.CurrentItem.Send")

But it does not work for reply, so Send must interact with mail items differently.

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007
Probably this (:I don't use outlook).
COM_Invoke(Outlook,"ActiveInspector.CurrentItem.Actions.Item[Reply].Execute.[color=red]Send[/color]")


CozHelp
  • Members
  • 3 posts
  • Last active: Nov 08 2010 11:49 PM
  • Joined: 20 Oct 2009
I'm using the COM.ahk and the enigmatiqk - various functions <!-- m -->http://www.autohotke... ... 285#260285<!-- m -->

I am trying to write data to a sheet that isn't active so my active sheets has minimum formulas, and i'll hide the formula sheets. But currently I can only write to the active sheet and if I click some where while its writing I'll get an error.

The code I use to write is:

loop,100
{
Excel_SetText("A", "1", "Boo")
}


COM Error Notification
Function Name: "Formula"
ERROR: (0x800AC472)
PROG:
DEC:
HELP: ,0
Will Continue?
Yes No

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007
The code will work only with active cells. Use the following code instead, after replacing the ones in red with appropriate ones. BTW I recommend using AutoHotkey_L and COM_L when using COM, which the code was written with.
COM_GetActiveObject("Excel.Application").Workbooks("[color=red]Book1[/color]").Worksheets("[color=red]Sheet1[/color]").Cells(1,1).Formula := "Boo"


Joy2DWorld
  • Members
  • 562 posts
  • Last active: Jun 30 2014 07:48 PM
  • Joined: 04 Dec 2006

The code will work only with active cells. Use the following code instead, after replacing the ones in red with appropriate ones. BTW I recommend using AutoHotkey_L and COM_L when using COM, which the code was written with.

COM_GetActiveObject("Excel.Application").Workbooks("[color=red]Book1[/color]").Worksheets("[color=red]Sheet1[/color]").Cells(1,1).Formula := "Boo"


Sean,

be aware that *a lot* of institutional environments require a lengthy approval process for new software. the _L fork is not, and won't' be at least for good while approved for *a lot* of user base.


if relevant for your thinking...
Joyce Jamce

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007

be aware that *a lot* of institutional environments require a lengthy approval process for new software. the _L fork is not, and won't' be at least for good while approved for *a lot* of user base.

I heard about it. However, I decided to post codes written in AHK_L and COM_L from now on, as now there exist abundant samples written in AHK and COM in the forum, and the translation will be almost straightforward, with one parameter restriction, although it may appear cumbersome.
COM_Init()
Excel := COM_GetActiveObject("Excel.Application")
COM_Invoke(Excel, "Workbooks([color=red]Book1[/color]).Worksheets([color=red]Sheet1[/color]).Range(A1).Formula", "Boo")
BTW, I enhanced COM_Invoke to allow multiple parameters and the following sometime ago, however, became unnecessary now.
COM_Init()
COM_Invoke("Excel.Application", "Workbooks([color=red]Book1[/color]).Worksheets([color=red]Sheet1[/color]).Range(A1).Formula", "Boo")


Joy2DWorld
  • Members
  • 562 posts
  • Last active: Jun 30 2014 07:48 PM
  • Joined: 04 Dec 2006
by comparing the two, will likely in large way help educate many new to objects/object programming, or at least help demo the advantage (especially with com) in language object awareness.
[am not aware of chances of official AHK adaptation of com or object language, etc]

ps: and excuse my ignorance, in native AHK com, is the Cells param an array or passable by invoke with 2 params. Array passable by VB array ref ?
Joyce Jamce

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007

in native AHK com, is the Cells param an array or passable by invoke with 2 params.

You posted while I was editing my previous post. Anyway, no with current COM.ahk in the forum.

Array passable by VB array ref ?

You meant this passing SafeArray?

fsd5858
  • Members
  • 2 posts
  • Last active: Oct 11 2013 06:28 PM
  • Joined: 30 Jul 2013

Is it possible to kick off an AHK script from a SQL Server SSIS package or stored procedure?

 

Thanks,

 

Sharon