Seeking Advice on Best Approach for Email Handling with Attachments
Seeking Advice on Best Approach for Email Handling with Attachments
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
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.
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
The cell that I'm referencing in my previous comment (in regard to the variable %RecipientFirstName%) is:
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.
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.
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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.TXShooter wrote:The cell that I'm referencing in my previous comment (in regard to the variable %RecipientFirstName%) is: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.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.
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
}
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
Thank youFanaticGuru wrote:Nice job coming from what seemed like very little knowledge of COM in your first post.
"Trickier"... more like FM (freakin' magic, lol)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.
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
}
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.
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.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'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.
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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.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.
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
}
}
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
Question: In my current method to access the cells in a workbook, am I forced to have that workbook open in Excel?
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.
Code: Select all
oWorkbook := ComObjGet(dFilename)
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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.TXShooter wrote:Question: In my current method to access the cells in a workbook, am I forced to have that workbook open in Excel?
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.Code: Select all
oWorkbook := ComObjGet(dFilename)
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
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
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
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
Did that... still same error unless Excel is open. (This is on a Windows 7 machine if that has anything to do with it.)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.
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
I don't know. Like I said I generally avoid using ComObjGet. I have found it problematic in general.TXShooter wrote:Did that... still same error unless Excel is open. (This is on a Windows 7 machine if that has anything to do with it.)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.
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
}
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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?
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?
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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 ... 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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
Why .offset when you can have Header,Row? (Or can you?... that's what I'm asking.)Xeo786 wrote:why R1C1 when you have .offset...
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.
Re: Seeking Advice on Best Approach for Email Handling with Attachments
are you talking about Name Ranges, assigning names? if so then following might helpTXShooter wrote: I'm wanting to use headers because the columns can change at a whim from users that access this spreadsheet
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
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
You can use Find to search across a row for a specific header and then get the column number when found.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.
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
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
FanaticGuru,FanaticGuru wrote:You can use Find to search across a row for a specific header and then get the column number when found.
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.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
FG
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"
}
}
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.
Code: Select all
CellDate := "07/18/2018"
FormatTime, CellDate, M/d/yyyy
oWB.Sheets(Sht).Range(Headers[A_Index] NR).Value := CellDate
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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".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.
Code: Select all
CellDate := "07/18/2018" FormatTime, CellDate, M/d/yyyy oWB.Sheets(Sht).Range(Headers[A_Index] NR).Value := CellDate
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
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
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Seeking Advice on Best Approach for Email Handling with Attachments
It was not about finding the name of the column headers, it was about finding the column that a header is in.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.
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
}
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
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
Re: Seeking Advice on Best Approach for Email Handling with Attachments
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)
Who is online
Users browsing this forum: Billykid and 214 guests