Seeking Advice on Best Approach for Email Handling with Attachments

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Seeking Advice on Best Approach for Email Handling with Attachments

30 Jun 2018, 17:18

Up to now I have been focusing a lot of my AHK experience on helping my church automate our sermon-to-radio needs (recording the main weekly sermon using a DAW attached to our main mixer board, then processing it in post-production). The output has since been modified to provide two different versions; a full length version for our website, and a 30 minute version for radio. After the post production 'output', it then sends both out via email to their respective recipients one at a time (radio station and web host) using SendSMTP 2.19.0.1 (which has mysteriously disappeared from the internet all of the sudden). All of this works great and gives us exactly what we need. But... we'd like to expand on this, which is what brings me here once again.

In recent weeks our website crashed and it's been a long, painful, and arduous experience to get even half of our website functionality back, or so I have been told by the web master. In lieu of (for now) and in addition later on, we'd like to send out these web versions to our church members and missionaries via email as well.

I'm seeking advice for the best approach to automate emailing these sermons by either / and / or:
A) Sign-up for all email request,
B) Email request for just one-at-a-time delivery.


Essentially I am needing to figure out a simplified system to process requests for 'lifetime membership' as well as 'as-needed / temporary-membership' sermon requests via email, sort of like having a 'request to signup for newsletters' type of system and an "Email me just this one sermon using the code phrase in the weekly bulletin" type of system.

For the 'lifetime' setup I suppose that I could add an event to Windows Scheduler for a convenient time after emailing to the website, and have this launch an AHK script using the same SendSMTP that my 'send to webmaster' script is using. This would require me to manually enter each recipient's name into that script though. (Or, I could simply add these email addresses to the BCC field of the same 'send to webmaster' script... either way, still having to manually create this list of addresses in the script.)

I know that Outlook has some COM interface capabilities as well as VBA, but passing parameters to my AHK Recording 'rig' seemed daunting from what little research I've done on the subject. Outlook can also 'auto-respond' to 'as-needed' requests, but each and every sermon request would have to have a unique pre-made template (.oft) to respond with an attachment... AHK could be used to generate the .oft file, but that just seems like adding 20 steps to what could be a single step process, especially since these .oft template files have to be created from within Outlook.

I have thought about using AHK to automate the email process for the 'lifetime' requests as I mentioned above, but somehow each person's email address would need to be entered into a data file (Excel or otherwise), which I guess I could do manually but would prefer an automated rig. Outlook's VBA looked promising for part of this, parsing the email address to a script of sorts, but can VBA pass this address parameter to AHK or Excel and tell them what to do with it?

Conversely, is there any way to have AHK go through the list of recent email requests in Outlook and parse out the new addresses and adding them to the webmaster script? Is there a way to parse just those asking for a single sermon and move that request to a folder not to be read through again? (Don't want to delete it as it is a matter of record in case something goes wrong with the interwebs.)

Anyway, if anyone has some experience doing something akin to this, I would really appreciate their input.
Thanks,
TXShooter
Last edited by TXShooter on 06 Jul 2018, 15:04, edited 1 time in total.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

01 Jul 2018, 15:11

I don't know exactly what you want but the answer is probably yes, AHK can do that.

Yes, AHK can send messages through Outlook with attachments.
Yes, AHK could send those messages based on a list of addresses in Excel.
Yes, AHK could custom the emails with names and what file is attached and make them look as pretty as you want.
Yes, AHK could create a list of addresses in Excel from emails' addresses in a folder with filtering based on time or something in the title, body, etc of the emails.
Yes, AHK could automatically maintain and update this list in lots of ways.
Yes, AHK could be scheduled to do any of this at certain intervals.
Yes, AHK could track what all emails were sent to who and when.
Yes, AHK would be one of the easiest scripting languages to creat this in. In my opinion, even easier than the VBA built into Outlook although it could all be done in VBA also.
Yes, it will be some work to do but not real difficult. Dozens of people on this forum could do it with little problem and hundreds could do it with a little googling and research.

Basically Outlook has robust automation capabilities and AHK can tap into those abilities if you have the full desktop version of Outlook.

As far as the website side, if the website could just send an easy to identify "sign up" email to the list keeping computer then AHK could watch for those emails and add people to the list.

Another thing I have seen is setting it up where if a email is received it is then resent to everyone on a mailing list. You can add various security features but the basic idea is that if you want to send something out to your mailing list you just send one email to your "bot" and it resends it to everyone.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

03 Jul 2018, 18:00

Here's what I have so far, and I am finding it to be easier than I thought it would be to get AHK to send emails via Outlook by way of an Excel database. However, I'm still not sure where to start as to having Outlook send inbound requests to my script upon arrival for both individual requests and permanent signups.

My current challenge is to get the variable %RecipientFirstName% within the "U" cell of the spreadsheet to play nice. Currently it is just displaying %RecipientFirstName%, and not populating the actual variable from the call within the script ****RecipientFirstName := oWorkbook.Sheets("Email Memberships").Range("B" A_Index+1).Value****.

I know this is doable now... it's just working out the kinks on this current setup. But... any advice or steering for how to get the inbound request to Outlook to work would be greatly appreciated.

Thanks,
TXShooter

Code: Select all

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Email Via Outlook Script.ahk
; Created by: TXShooter
; Purpose: 	This script will be called by a higher level script to automatically batch-process compose and send an email using parameters to fill the following:
;				1) Read in from Excel Spreadsheet (the database for all sermon listings with details, as well as email recipients' information)
;				2) Subject (either from calling script or from a cell in the Sermon Listings sheet)
;				3) Body (to include salutations) (mixed: from the database and script)
;				4) Attachment (from calling script)
;				5) Loop through the above until all of the recipients in database have been sent their own individual email
;
; Date: 		2018.06.24
; Revision: 	1.0a
;
; DEBUG ISSUES:
;					
;
; TODO:				1) React to inbound email requests for a specific sermon from those that haven't signed up for automatic delivery.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Environment Setup:
	#NoEnv  						; Recommended for performance and compatibility with future AutoHotkey releases.
	SendMode Input  				; Recommended for new scripts due to its superior speed and reliability.
	SetWorkingDir %A_ScriptDir%  	; Ensures a consistent starting directory.
	#SingleInstance, Force			; Force-limit a single instance of the script to run one at a time
	#Persistent

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Variables:
  ; Initial Variables:
		RecipientEmailAddress := "."
		Global OneDriveFolder := ""
	; Get the path for the OneDrive folder
		RegRead, OneDriveFolder, HKEY_CURRENT_USER\Environment, OneDrive
	; Set the entire path for the spreadsheet
		dFilename := OneDriveFolder . "\Media Ministry\Master Recording List.xlsx"
	; Set the COM Object of the spreadsheet
		oWorkbook := ComObjGet(dFilename)
	; Get the title of the latest sermon
			; Cell.Find(What, After, Lookin, LookAt, SearchOrder, SearchDirection,MatchCase,MatchByte,SearchFormat)
			; Cell.Find(What="*", After=, Looking=, ?? ?? ?? ?? (how does this work?)
		LastSermonCell := oWorkbook.Sheets("2018 Sermons").Cells.Find("*", , , , 1, 2).Row
		SermonTitle := oWorkbook.Sheets("2018 Sermons").Range("J" LastSermonCell).Value
			the_Subject = Sermon: %SermonTitle%
			subject := the_Subject	
		EmailGreetings := oWorkbook.Sheets("2018 Sermons").Range("U" LastSermonCell).Value
		MsgBox %LastSermonCell%`n%EmailGreetings%
  ; Start of loop to email using the "Email Memberships" sheet:
	; Get the last cell used in the sheet to be used as a counter of emails
		LastEmailCell := oWorkbook.Sheets("Email Memberships").Cells.Find("*", , , , 1, 2).Row
		Loop % LastEmailCell-1
		{
		RecipientEntryNumber := oWorkbook.Sheets("Email Memberships").Range("A" A_Index+1).Value
		RecipientFirstName := oWorkbook.Sheets("Email Memberships").Range("B" A_Index+1).Value
		RecipientLastName := oWorkbook.Sheets("Email Memberships").Range("C" A_Index+1).Value
		RecipientSignUpDate := oWorkbook.Sheets("Email Memberships").Range("D" A_Index+1).Value
		RecipientEmailAddress := oWorkbook.Sheets("Email Memberships").Range("E" A_Index+1).Value
		; MsgBox, Emailing to %RecipientFirstName% %RecipientLastName% who signed up on %RecipientSignUpDate%, using %RecipientEmailAddress% as MailTo Address.
		Sleep, 300


  ; Set the Body part of the message filling in the Recipient's variable
	the_Body = 
		(%EmailGreetings%
Sincerely,
The Media Ministry
Out Baptist Church
)

		body := the_Body		
		body := StrReplace(body,"`%09","")
		MsgBox Greetings:%EmailGreetings%`n%body%
  ; Set the Attachment file variable to the latest sermon
	attachment = C:\Today\Sermon.MP3
  ; Create the actual email using the above variables
	oMail := ComObjCreate("Outlook.Application")
		NewMail := oMail.CreateItem(0)
		NewMail.Subject := subject
		NewMail.Body := body
		NewMail.Attachments.Add(attachment)
		NewMail.To := RecipientEmailAddress
		NewMail.Send
		}
	ExitApp
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

03 Jul 2018, 18:32

The cell that I'm referencing in my previous comment (in regard to the variable %RecipientFirstName%) is:

Code: Select all

  Thank you %RecipientFirstName% for requesting today`'s sermon. We hope it brings you and your loved ones closer to the Will of God.

May the Lord Bless and Keep you this day.
I'm hoping to work this out because then I could create a custom message for each specific sermon, but still have a personalized touch.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

04 Jul 2018, 01:44

TXShooter wrote:The cell that I'm referencing in my previous comment (in regard to the variable %RecipientFirstName%) is:

Code: Select all

  Thank you %RecipientFirstName% for requesting today`'s sermon. We hope it brings you and your loved ones closer to the Will of God.

May the Lord Bless and Keep you this day.
I'm hoping to work this out because then I could create a custom message for each specific sermon, but still have a personalized touch.
I only had time to glance at your code. Nice job coming from what seemed like very little knowledge of COM in your first post.

If I am understanding right you want to have in your Excel cell an email body that contains AHK variables that you want resolved by AHK before using as the body of the email. This is trickier than it might seem. AHK can't directly resolve variable names stored in a string unless the string is nothing but a variable name.

What you are doing is more of a markup language. You need to parser your string and resolve the variables individually. Below is a function to demonstrate.

Code: Select all

RecipientFirstName := "John"
Message =
(%

   Thank you %RecipientFirstName% for requesting today`'s sermon. We hope it brings you and your loved ones closer to the Will of God.

May the Lord Bless and Keep you this day.
)

MsgBox % Message
MsgBox % Markup(Message)

Markup(String)
{
	X:=1
	while (X := RegExMatch(String, "([^%]*?)%(\S*?)%([^%]*)", M, X+StrLen(M)))
		Result .= M1 %M2% M3
	return Result
}
The Markup function is using some regex magic to loop through the string looking for variables and then resolving them to their actual values. It was hastily written and barely tested. There might be strings that can break it so I would test on some real date before using it to send a ton of emails.

I will look at your code more closely when I have more time.

When you get to building your address list, you might check out this link.
http://the-automator.com/generate-outlo ... n-outlook/
It shows a script that goes through folders and gets addresses and puts them into an Excel sheet. I have not looked at the code but it might be useful in learning some aspects of that. You might be able to just use the script. I am not sure what kind of versatility it provides.

That whole website is really good for learning various things about AHK.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

04 Jul 2018, 09:36

FanaticGuru wrote:Nice job coming from what seemed like very little knowledge of COM in your first post.
Thank you

FanaticGuru wrote:If I am understanding right you want to have in your Excel cell an email body that contains AHK variables that you want resolved by AHK before using as the body of the email. This is trickier than it might seem.
"Trickier"... more like FM (freakin' magic, lol)

FanaticGuru wrote:What you are doing is more of a markup language. You need to parser your string and resolve the variables individually. Below is a function to demonstrate.

Code: Select all

RecipientFirstName := "John"
Message =
(%

   Thank you %RecipientFirstName% for requesting today`'s sermon. We hope it brings you and your loved ones closer to the Will of God.

May the Lord Bless and Keep you this day.
)

MsgBox % Message
MsgBox % Markup(Message)

Markup(String)
{
	X:=1
	while (X := RegExMatch(String, "([^%]*?)%(\S*?)%([^%]*)", M, X+StrLen(M)))
		Result .= M1 %M2% M3
	return Result
}
It took some minor changes but I managed to get your Markup code to work... THANK YOU for that. I would've never have realized it needed to be treated like a Markup Language, but in retrospect, I get it now.

There are still parts of AHK that just completely baffle me. RegExMatch (well, all of the RegEx___ really) is like a foreign language to me with all of its Options (****([^%]*?)%(\S*?)%([^%]*)**** <-- Very confusing to me). I will probably need crayons and construction paper to figure out how to use them properly.
FanaticGuru wrote:When you get to building your address list, you might check out this link.
http://the-automator.com/generate-outlo ... n-outlook/
It shows a script that goes through folders and gets addresses and puts them into an Excel sheet. I have not looked at the code but it might be useful in learning some aspects of that. You might be able to just use the script. I am not sure what kind of versatility it provides.

That whole website is really good for learning various things about AHK.

FG
I have been referencing that website and this one too, along with general Google searches to help me out. COM objects just might be my ticket to a number of other endeavors I am wanting to take.


I'm still trying to sort out how to approach the inbound requests from Outlook. There are two primary functions that I need to work through. One for handling the 'sign me up' requests for receiving all future sermons via email, and another to handle the 'one-off' sermon requests.

I supposed that for the 'sign me up' requests I could create a rule in Outlook that runs an AHK script which scans Outlook folders and handles things that way? Or would it be better to learn VBA and have those specific requests handled internally, thus placing the new email address in the Excel database sheet?

As to the 'one-off' requests? Yeah... not sure on that one. We are planning on putting a 'code' on the projection screen for this purpose, but handling that code within Outlook is still a new concept to me. (During the service we plan on displaying a slide that states, "For a copy of this sermon, text or email "code" to [email protected].") I am wanting to design an AutoResponder template that should the subject line and/or body not contain either "Code" or "Sign Me Up", it will spit back out to the requester a set of instructions on how to use this auto-emailer setup, just in case there's a typo or confusion in their request... it will also include instructions on how to signup for all sermons should they desire to do so.

I very much so appreciate your feedback and input on this.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

05 Jul 2018, 16:01

TXShooter wrote:I'm still trying to sort out how to approach the inbound requests from Outlook. There are two primary functions that I need to work through. One for handling the 'sign me up' requests for receiving all future sermons via email, and another to handle the 'one-off' sermon requests.

I supposed that for the 'sign me up' requests I could create a rule in Outlook that runs an AHK script which scans Outlook folders and handles things that way? Or would it be better to learn VBA and have those specific requests handled internally, thus placing the new email address in the Excel database sheet?

As to the 'one-off' requests? Yeah... not sure on that one. We are planning on putting a 'code' on the projection screen for this purpose, but handling that code within Outlook is still a new concept to me. (During the service we plan on displaying a slide that states, "For a copy of this sermon, text or email "code" to [email protected].") I am wanting to design an AutoResponder template that should the subject line and/or body not contain either "Code" or "Sign Me Up", it will spit back out to the requester a set of instructions on how to use this auto-emailer setup, just in case there's a typo or confusion in their request... it will also include instructions on how to signup for all sermons should they desire to do so.

I very much so appreciate your feedback and input on this.
Just for your knowledge, AHK can monitor Outlook events and take actions based on lots of things. Two that might be useful is when an email is received or when an email is put into a folder. You can create a AHK script that acts just like a rule that takes whatever action you want when emails are received but even more than the rule system allows.

The same thing can be done in VBA within Outlook. A VBA Outlook script has the advantage of always running when Outlook is running. With an AHK script, if the script is not running when Outlook receives an email that would be a problem. The down side of the VBA script is some security headaches. The plus side for AHK, is keeping everything together in just AHK and AHK for me can be easier to code in.

Instead of constant monitoring, another option is to just update the mailing list on a schedule or when you tell the script to do it. The AHK script could then look through a folder for new emails and add the ones added since the last update.

Of course you could also do a hybrid where AHK monitors Outlook in real time and could send back receipt confirmations to people wanting to be added to list and each time the AHK script starts it could look in a folder for any emails received while the script was not running.

As far as sorting out the 'sign me up' or 'one request' emails, that is pretty easy. You can just search the title and body of an email for a specific code or phrase. Using RegEx to find specific patterns in text is exactly what RegEx is designed for. Looking for the phrase "Sign Me Up" or some code in an email is pretty easy. It just has to be unique enough so that people are not including the phrase or code in regular emails by accident.

Basically everything you seem to want is very doable. Just pick what you would prefer and I can probably steer you in the right direction.

Just as an example:

Code: Select all

#Persistent	; Script must continue to run to work

global olApp := ComObjActive("Outlook.Application")
ComObjConnect(olApp, "EventApp_")

EventApp_NewMailEx(IDs)
{
	IDs := StrSplit(IDs, ",")
	for index, ID in IDs
	{
		olMailItem := olApp.Session.GetItemFromID(ID)
		if (olMailItem.Subject = "test123")
			MsgBox % olMailItem.Subject " : received from > " olMailItem.Sender
	}
}
Start Outlook, then run this script. It links to the download emails event. Each time a batch of emails is downloaded it check all the emails downloaded in that batch for any that have the title "test123" and then displays a message for those.

Change olMailItem.Subject = "test123" to olMailItem.Subject ~= "test123" to go from "equals" to "includes".

If you restart Outlook, you would have to restart this script but there are lots of options there also.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

05 Jul 2018, 20:27

Question: In my current method to access the cells in a workbook, am I forced to have that workbook open in Excel?

Code: Select all

oWorkbook := ComObjGet(dFilename)
I'm getting some kind of error saying that I'm using an invalid extension in the filename if I don't have the workbook open, and I wasn't sure if this was related to ComObjGet.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

06 Jul 2018, 13:09

TXShooter wrote:Question: In my current method to access the cells in a workbook, am I forced to have that workbook open in Excel?

Code: Select all

oWorkbook := ComObjGet(dFilename)
I'm getting some kind of error saying that I'm using an invalid extension in the filename if I don't have the workbook open, and I wasn't sure if this was related to ComObjGet.
ComObjGet opens a file, it just does not make it visible by default. If no Excel process is running and then you execute that line, you will not see anything happen but if you look in the Task Manager you will see an Excel process now running. You have to be careful with invisible instances being created and not properly closed and then have a bunch of different Excel processes running.

I prefer not to use ComObjGet in most cases because it does not give you a handle to the application object which is needed for many useful methods and properties. One of the main ones being Quit() to get rid of the ghost processes when you are done. Another is ActiveCell which is an application property. No matter the number of workbooks open, an Excel application only ever has one active cell. Same thing with the other 'active' stuff.

I prefer this:

Code: Select all

F11::
	xlApp := ComObjCreate("Excel.Application")
	xlApp.Visible := true		; for testing
	xlWorkbook := xlApp.Workbooks.Open(A_Desktop "\Test\Test.xlsx")
	MsgBox % xlApp.ActiveCell.Value
	xlApp.Quit()
return
I put xlApp.Visible in there so that the workbook appears but if that was false or not in there at all, Excel would be started invisibly in the background. You also now have a handle to xlApp.

You can open multiple workbooks with one application:

Code: Select all

F11::
	xlApp := ComObjCreate("Excel.Application")
	xlApp.Visible := true		; for testing
	xlWorkbook1 := xlApp.Workbooks.Open(A_Desktop "\Test\Test1.xlsx")
	xlWorkbook2 := xlApp.Workbooks.Open(A_Desktop "\Test\Test2.xlsx")
	MsgBox % xlApp.ActiveCell.Value
	xlWorkbook1.Close() ; close a workbook
	MsgBox % xlWorkbook2.Range("A1").Value
	xlApp.Quit() ; quit an Excel application
return
You could work backwards like this:

Code: Select all

F12::
	xlWorkbook := ComObjGet(A_Desktop "\Test\Test.xlsx")
	xlApp := xlWorkbook.Parent
	xlApp.Visible := true		; for testing
	xlApp.Windows(xlWorkbook.Name).Visible := true	; ComObjGet is invisible by default, an invisible workbook does not have an activecell
	MsgBox % xlApp.ActiveCell.Value
	xlApp.Quit()
return
You could take out the Quit() at the end and if you pushed that hotkey 100 times you could have a 100 instances of Excel running and if they were not visible you might not even realize it. Different versions of Excel handle when to create new instances of Excel different when a workbook is opened so there is also some ambiguity.

All that aside it sounds like there is just something wrong with your path. You might do a MsgBox % dFilename right before that line to make sure the path looks right.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

06 Jul 2018, 13:50

FanaticGuru wrote:All that aside it sounds like there is just something wrong with your path. You might do a MsgBox % dFilename right before that line to make sure the path looks right.
Did that... still same error unless Excel is open. (This is on a Windows 7 machine if that has anything to do with it.)
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

06 Jul 2018, 15:15

TXShooter wrote:
FanaticGuru wrote:All that aside it sounds like there is just something wrong with your path. You might do a MsgBox % dFilename right before that line to make sure the path looks right.
Did that... still same error unless Excel is open. (This is on a Windows 7 machine if that has anything to do with it.)
I don't know. Like I said I generally avoid using ComObjGet. I have found it problematic in general.

Here is a function I have used in the past that might help.

Code: Select all

xlWb1 := xlGet_Workbook("One.xlsx", A_Desktop "\Test\")
xlWb2 := xlGet_Workbook("Two.xlsx", A_Desktop "\Test\")

MsgBox % xlWb1.ActiveSheet.Range("A1").Value
MsgBox % xlWb2.Worksheets(2).Range("A1").Value
MsgBox % xlWb2.Worksheets("Test").Range("A2").Value
MsgBox % xlWb2.Worksheets("Test").Range("A2").Row
MsgBox % xlApp.ActiveCell.Address
MsgBox % xlApp.ActiveCell.Row
MsgBox % xlApp.ActiveWorkbook.Name

xlGet_Workbook(WB, Path:="")
{
	global xlApp ; once function is called xlApp will be available to use outside the function
	If !Path	; default path
		Path := A_Desktop "\Test\"
	if !xlApp ; If no xlApp then attempt to connect to running app or open app
	{
		try
			xlApp := ComObjActive("Excel.Application")
		catch
			xlApp := ComObjCreate("Excel.Application")
		xlApp.Visible := true
	}
	try ; try to connect to open workbook
		xlWb := xlApp.Workbooks(WB)
	catch
		try ; try to open workbook
			xlWb := xlApp.Workbooks.Open(Path WB)
		catch ; catch failure to open
		{
			MsgBox % "Wookbook: " WB "`nCannot be found at: " Path WB
			return
		}
	return xlWB
}
The function xlGet_Workbook is for getting a connection to a workbook. It first determines if Excel is already running, if not it starts it. Then it determines if the workbook is already open, if not it opens it. Then returns an COM object connected to the workbook or tells you there was a failure.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

14 Jul 2018, 21:37

What's the best approach to using the Header Names of a spreadsheet rather than the R1C1 convention in Excel (using ComObjCreate)? I'm asking because the columns in this spreadsheet can / do get shifted around by any one of several users, and I am (was) attempting to place strings in specific column order, which as you can guess has some bad things happening.

I have tried (successfully) reading into an array the header names, but now I'm lost trying to find the tree in the forest again. My brain is shortcircuiting when trying to compare the name of the column header (in array) to the actual header-specific variable that I am trying to use to write to the cell under that header column. (Did that make sense? Because I felt a large thud on the ground after thinking and writing it up.)

It should be simple...
0) After processing all of the data that needs to go into the spreadsheet, perform the following:
1) Read into an array the header names, starting from A1 to the last column used
2) Scan that array and compare the names of each array element to a predefined array of header names
3) When match is found, put the data of the variable into that column's last used row+1 (cell)
4) Loop until all data has been entered into the spreadsheet and happily move on to the next task.

Tree... meet forest. (thud)

Can anyone offer up some advice here?
User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Seeking Advice on Best Approach for Email Handling with Attachments

16 Jul 2018, 01:33

why R1C1 when you have .offset... i have some checking task so I paste few specific column that's an other excel com script but following is simply code well.. like if you selected A:A It will not compare A1 to B:B, it will compare A1 to B1 then A2 and B2 and so on ... :trollface: do not select whole A;A it will be dead slow if check 1048576 cells through for loop specially when inputting values for every cell

Code: Select all

F2:: ; select raneg and press F2 it will compare for values of cell and next cell then put result into 3rd cell for selected Colomun
for cell in ComObjActive("Excel.Application").Selection 
	if (cell.EntireRow.Hidden = False) ; and skip hidden rows like when you have lots of filters applied and you do not want make some mess
		if (cell.value > cell.offset(0,1).value)
			cell.offset(0,2).value := "OK"
		else 
			cell.offset(0,2).value := "Ex"
return
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

16 Jul 2018, 17:56

Xeo786 wrote:why R1C1 when you have .offset...
Why .offset when you can have Header,Row? (Or can you?... that's what I'm asking.)

I'm wanting to use headers because the columns can change at a whim from users that access this spreadsheet. In that scenario, I'm not sure how to apply the .offset method.
User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Seeking Advice on Best Approach for Email Handling with Attachments

17 Jul 2018, 03:23

TXShooter wrote: I'm wanting to use headers because the columns can change at a whim from users that access this spreadsheet
are you talking about Name Ranges, assigning names? if so then following might help

Code: Select all

oExcel := ComObjCreate("Excel.Application")
oExcel.Visible := True
oWorkbook := oExcel.Workbooks.Add

oExcel.Sheets("Sheet1").Range("A1").value := "List"
Cells := oExcel.Worksheets("Sheet1").Range("A2:A10")
Cells.Name := "List"	; assigning Specific name to a range "see Excel's Name Manager"
oExcel.Worksheets("Sheet1").Range("A11").value := "=sum(list)"	; formula by using Range Name

xlRange := oExcel.Worksheets("Sheet1").Range("List") 	; call that range by Name not by address 

for cell in xlRange   ; checking is "=sum(list)" working 
	cell.Value := a_index

Loop, 10  ; assigning multiple names to multiple ranges
{
	Range%a_index% := Cells.offset(0,A_index)
	Range%a_index%.Name := "list"A_index
	;~ Cells.offset(-1,A_index).value := "list"A_index  
}

for heading in oExcel.Worksheets("Sheet1").Range("b1:k1") 	; putting heading visibility
	heading.value := "list"A_index
	
for nm in oWorkbook.Names 	; checking how many names are asigned to this workbook and their range
	msgbox, % nm.Name "`n" nm.RefersTo
return
or I failed to understand your objective .. :think:
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

17 Jul 2018, 11:34

TXShooter wrote:What's the best approach to using the Header Names of a spreadsheet rather than the R1C1 convention in Excel (using ComObjCreate)? I'm asking because the columns in this spreadsheet can / do get shifted around by any one of several users, and I am (was) attempting to place strings in specific column order, which as you can guess has some bad things happening.
You can use Find to search across a row for a specific header and then get the column number when found.

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % ColumnNumber := xlApp.Rows(1).Find("Date").Column
for Cell in xlApp.ActiveSheet.UsedRange.Columns(ColumnNumber).Cells
	MsgBox % Cell.Value
This looks in row 1 for a column with "Date", displays the column number and then loops through all the used cells of that column.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

17 Jul 2018, 18:57

FanaticGuru wrote:You can use Find to search across a row for a specific header and then get the column number when found.

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % ColumnNumber := xlApp.Rows(1).Find("Date").Column
for Cell in xlApp.ActiveSheet.UsedRange.Columns(ColumnNumber).Cells
	MsgBox % Cell.Value
This looks in row 1 for a column with "Date", displays the column number and then loops through all the used cells of that column.

FG
FanaticGuru,
Thank you. However... finding the name of the column headers wasn't really what I was asking about. I am already doing that.

Code: Select all

GetDatabaseHeaderColumns()
{	
	; Set the COM Object of the spreadsheet
		oWorkbook := ComObjCreate("Excel.Application")
		oWorkbook.Visible := False
		oWB := oWorkbook.Workbooks.Open(dFilename)
		Sht := "Sheet 1" ; Name of the sheet in Excel
		LC := oWB.Sheets(Sht).UsedRange.Columns.Count
		NR := oWB.Sheets(Sht).UsedRange.Rows.Count+1
	Headers := {}
	Columns := {}
	Loop, %LC% {
		Headers[A_Index] := oWB.Sheets(Sht).Cells(1, A_Index).Value
		Columns[A_Index] := Chr(64+A_Index)
		MsgBox, % Headers[A_Index] " is in cell " Columns[A_Index]"1"
	}
}
My challenge was more about how to use the header name as opposed to Row1, Column1. "What's the best approach to using the Header Names of a spreadsheet rather than the R1C1 convention in Excel (using ComObjCreate)?"

Unfortunately, having the name of the column doesn't quite crossover neatly when using .Range with arrays... or maybe it does and I'm not coding the array correctly. If so, by all means please correct me. :D

Code: Select all

	CellDate := "07/18/2018"
	FormatTime, CellDate, M/d/yyyy
	oWB.Sheets(Sht).Range(Headers[A_Index] NR).Value := CellDate
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

18 Jul 2018, 15:34

TXShooter wrote:Unfortunately, having the name of the column doesn't quite crossover neatly when using .Range with arrays... or maybe it does and I'm not coding the array correctly. If so, by all means please correct me. :D

Code: Select all

	CellDate := "07/18/2018"
	FormatTime, CellDate, M/d/yyyy
	oWB.Sheets(Sht).Range(Headers[A_Index] NR).Value := CellDate
For something like this to work Headers[A_Index] would need to contain a column letter like "B" but the way you populated the array it will probably contain a header name like "Date".

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Seeking Advice on Best Approach for Email Handling with Attachments

18 Jul 2018, 16:14

TXShooter wrote:FanaticGuru,
Thank you. However... finding the name of the column headers wasn't really what I was asking about. I am already doing that.
It was not about finding the name of the column headers, it was about finding the column that a header is in.

With that ability, you don't really need an array with all the header names.

Below is an example function that puts information into columns based on the header name at the top.

Code: Select all

DropInColumnByHeader("Amount", 123.45)

DropInColumnByHeader(Header, Value)
{
	global xlApp
	if !xlApp
		xlApp := ComObjActive("Excel.Application")
	Header_Column := xlApp.Rows(1).Find(Header).Column
	Empty_Row := xlApp.Columns(Header_Column).Find("*",,,,,2).Row + 1
	xlApp.Cells(Empty_Row, Header_Column).Value := Value
}
In the example, the function looks for a column with "Amount" in the first row then puts "123.45" at the 'bottom' of that column.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
TXShooter
Posts: 165
Joined: 13 Dec 2017, 09:27

Re: Seeking Advice on Best Approach for Email Handling with Attachments

18 Jul 2018, 17:37

FanaticGuru wrote:For something like this to work Headers[A_Index] would need to contain a column letter like "B" but the way you populated the array it will probably contain a header name like "Date".

Would my line of code above work?Columns[A_Index] := Chr(64+A_Index)

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Billykid and 214 guests