How Can I Setup Periodic Trigger to Send Mail via Apple Mail with Attachments?

Hey everyone (or anyone :slight_smile:,

OK - so, I am throwing this out there to see if some of you can help me find a better (more reliable) way to submit my timecards. I am going to try to explain it the best I can; so, my humblest apologies if I am not clear.
My work has recently asked me to submit my timecards online through email, and I am having a bit of trouble remembering to submit them on time (I can be a little absent minded). After various casual threats to take away some of my work, I thought it was high time to automate sending in my timecards.
So, the way my time cards work is that they send me an excel file(s) where I fill in the dates for each day that I worked and the hours that I worked. Each project lasts about 7 weeks and I am only allowed to work so many hours, so I usually fill out all the time cards right away and then store them in different folders so that I can just send them on the appropriate Monday morning (if I remember!). I say time card"s" because sometimes I am working on multiple projects and I get paid for each project from each timecard. Under the best possible circumstances, I am to submit those to my supervisor for review and all is well. However, I forget, so, what I have done is to created multiple automator calendar events (one for each of the 7 possible Monday time cards) wherein when that particular Monday is triggered by the calendar date, it grabs the timecards from the particular folder where that week's timecards are being held, it then attaches them to a mail message, and with everything filled out correctly, it sends it on its merry way.

Here is my dilemma: I don't trust automator (at all), and while it has worked once (somewhat successfully - there was one glitch but I think I fixed it) my faith/trust in Automator is almost non-existent - which is why I absolutely love KM. Can someone help me develop or think of a way to do what I need to do using KM?
Here is a screenshot of my Workflow in Automator:

The tags are in there because I have hazel move the files out of the folder and into a different folder after they are sent (that way I know that when the next timecards are being filled out for the next time that folder gets accessed in the triggered calendar event, I am not resending old timecards)

Again, I would love to put my time and trust into KM to do this but after researching the Forums for hours, it really seems like the triggering options and attachment options are pretty limited in scope. I have read Peter's reasoning behind why this is the case, and I absolutely agree; however, I would really like to work with KM for this project rather than Automator. Any thoughts, musings, or questions are much appreciated!

Disclaimer: KM is the extent of my programming ability! Haha! I love this program because I can usually do what I need to do and if I can't, I can find a script that will allow me to do what I need to do. Having used it for my work for over four years now, I wouldn't think twice about spending $500 bucks on this program. I absolutely appreciate all the work that went into this program.
Cheers,
Shawn

The triggering issue is easy to resolve.

Create a macro that triggers every day at the desired time, and then use the DOW function to determine if it is a Monday, and if so do the task. If calculation:

DOW() == 2

Or wait for 8.0, but this is easy and works now.

Processing the folder is easy enough, use the For Each action with the Folder Contents collection. You can tag the folders green with that and/or move them to another folder directly.

Attachments are more of a challenge as Keyboard Maestro only supports a single attachment. So you’re pretty much stuck doing it via AppleScript. It’s not that hard, but I don’t offhand know the AppleScript to do it.

Thanks for doing me a solid Peter - much appreciated! So, with respect to the triggering, let me ask you this (and it is going to sound like a fifth grade word problem, so I apologize for that in advance) - one of the tricky aspects of organizing these time cards is that I can have staggered projects. So I might be submitting work from week 1 for one project while at the same time submitting work from week 3 on another project. So, lets say that I have two projects going and both of them are seven weeks long and one of them started on Tues July 18 and another started on August 1. When August 7 comes around I am submitting the time card for Week 3 for the first project and week 1 for the second project. So, what I have done is to create seven different folders to keep track of the time cards for a seven week cycle. So, in seven weeks, I will be submitting my final time card for the last project, but (most likely) I will also have one or two other projects started that will also have time cards associated with that folder.

So, here is what I am getting at (please bear with me if you are seeing something that I am not seeing): During my research, I saw your recommendation using the DOW in a few other posts, and while it will work to trigger the right day, I also need it to trigger on a seven week cycle. So that that particular macro - say the macro that accesses the folder for August 7 - will then run again seven weeks later on September 18. And the macro that will access the next week’s timecards for August 14 will then activate again on September 25 and submit those timecards for that particular week.

Is there a way to use the DOW to be triggered on a custom cycle (say every seven weeks or every three weeks, etc.)?

Thanks again Peter!
Cheers,
Shawn

I like to keep things simple, if I can. Have you considered setting up a recurring event in Calendar to remind you?

When you get the reminder for the event, you could trigger a KM macro (maybe by Name) if you wish.

Since your main issue is remembering to send the time card, and you need some flexibility in what to send, I'd keep it simple with a reminder separate from the KM Macro. You might even have several KM Macros:

  1. Open Finder to the folder where you store the time cards
  2. Send Excel files (time cards) selected in the Find via Mail.

Just an idea . . .

@JMichaelTX’s suggestion that you are perhaps making this more complicated than it needs to be is a good one.

That said, you can get a cycling 7 week counter with a calculation like this:

TRUNC((NOW()-TIME(2017,1,1))/3600/24/7) MOD 7

Adjust the starting date (2017,1,1) as desired to get the index for this week to where you want it if necessary, as well as the switchover day for when one week becomes the next.

Hey JMichaelTX,
Yea, I have thought about that, and I have done it (I might have to end up doing it again, as a last resort, if Automator and KM don’t work out). Part of the problem with the reminder through Calendar is that I am not always near my computer when I am reminded - so, I forget. I know it sounds lame but there are a long list of reasons concerning: work, wife, kids, sports, coaching, more work, friends, church and thinking about all of those things where submitting a silly thing like a time card gets lost in the shuffle.

Thanks for the thought! That is why I am asking the forum - to solicit any ideas or avenues that I haven’t considered or don’t know how to navigate.
All the best,
Shawn Lorenzen

Hey Peter, thank you for the calculation! - I really do appreciate it. The community is so tremendously helpful and considerate.

On a philosophical note - With your comment regarding making this more complicated than it needs to be, are you referring to my actual automation attempt with automator and KM or my “wanting to automate this process?” If you are referring to my “wanting to automate this process” I am going to most respectfully disagree with you. It is actually because submitting a time card within a 24 period is difficult for me to follow through that I am wanting to automate the process. For me NOT to attempt or work through an automation process is making my life more difficult than it needs to be, and can cause bigger problems than it needs to be (it seems that all automation could fall under the phrase of “making something more complicated than it needs to be” if it is taken as a means of “wanting to automate a process” - why automate it when you can just do it or send/write your self a reminder to do it?).

If you are referring to my automation, then I am your humble servant :slight_smile:.
All the best,
Shawn Lorenzen

If you are not at your Mac, how will KM help you?

Apple Calendar syncs with the iPhone, so if you have an iPhone you should get the reminder there also.

I can say for sure about Calendar, since I use Outlook, but when I log in to my Mac (actually any of my Macs), I get a reminder popup from Outlook of events/appointments due that day (including past-due events).

If you want further reminders, you could set KM to popup a reminder whenever you launch and/or activate any of the apps that you use to work on your projects.

Sorry to be blunt, but in the end, the discipline is up to you, if you want to get paid. Seems like great motivator to me. :wink:

For reminders I don’t want to forget, I have Calendar send me an email, then it’s in my mail box until its done.

OK, that’s fine. Go for it.

Use what has been suggested so far, and see where you are at, and then you can ask the specific questions for what part is still giving you troubles to implement.

Hey Peter and MichaelTX,
Appreciate both of you and your input.
To respond briefly to MichealTX concerning how KM will help me when I am not at my Mac: I want it to send the email (with the appropriate specifications listed previously) without me having to think about it or trigger it manually during my day (I will have to set up the spreadsheets on my own, but once they are filled out I want KM or Automator, or whatever, to send them in for me so I don’t have to think about it). Which also goes to the final comment in the response which says that the discipline is up to me to make it happen. I suppose that is true for all automation - we could just do it ourselves but we would like some actions done for us, and in my case I want a fairly significant action to be taken off my plate and automated for me - if I can. I am not sure how much more detail I can go into concerning the “why”, and I apologize for not being able to be clearer here, suffice to say that if this action can be automated through KM, or if there is a better way to do it so that I don’t have to think about it once the files are saved - awesome.

I am so appreciative to the both of you and your enduring patience with novices like myself who are slow to work through some of these automation practices.
All the best,
Shawn

I’d like to help, and suggest an KM automated system for you.
I’ve gone back and reread your requirements in the above posts, but much is still unclear to me. So, I’ll make some assumptions, and suggest an approach based on that.

###Assumptions
(please correct/confirm)

  1. You need to send an email once a week with your project time
  • For planning purposes, I’ll assume you send the email on Mon, at 10:00 am local time
  • Time for each project is kept in a separate Excel file
  • At least once a week, you update the Excel files for time you have worked.

OK, if all of that is correct, then you could do this:

###Calendar App

  • Setup repeating event for 8:00 am every monday
  • Notify you that all Excel files must be updated by 9:00 am

###Manual Steps

  1. Make sure all of your Excel time sheets are updated by Mon, 9:00 am (must be before 10:00am, so I’m using 9:00 am to give you some pad).
  2. After you update each Excel file, MOVE the file to a “To Be Sent” folder.

###KM Macro

  1. Macro set to trigger on every Monday at 10:00 am
  2. Use a Execute AppleScript Action to send email
  • Use a template text (previously built) for body of email
  • Mail Recipient and Subject are hard-coded in script
  • Attach all files in the “To Be Sent” folder
    • If there are NO files in the folder, display an error msg so stating, and abort process
  • Send email
  1. Check for success or error from Execute AppleScript Action.
  2. Use KM Action to MOVE all files in “To Be Sent” folder to a “Sent” folder
  • Doing this in a separate KM Action so that if the AppleScript fails, the Excel files remain in the “To Be Sent” folder.
  1. Show a Display Window popup listing all files that were sent.

All of this is very doable. Does it make sense to you?
Again, confirm, or correct.

Once we have a clear understanding of the steps you want, If you need help implementing them, just ask, but be specific about what help you need.

JMichaelTX,
Thanks a million! I shall go through your assumptions and KM steps one at a time:

Assumptions
1.You need to send an email once a week with your project time
For planning purposes, I’ll assume you send the email on Mon, at 10:00 am local time
(This is correct)

  1. Time for each project is kept in a separate Excel file
    (This is correct)

  2. At least once a week, you update the Excel files for time you have worked.
    (Actually, I enter in all of my times at once when I am first sent the timecard spreadsheet from my company. I am only allowed to work so many hours a week, so I just enter in those hours immediately and save a separate excel file for each of the seven weeks. So, by the time I am ready to send the Week 7 timecard worksheet, I haven’t looked at it for seven weeks (and I don’t look at it), and I just submit it on that Monday after Week 7.)

KM Macro
Here is where I will need help:

Step 2: Use a Execute AppleScript Action to send email
Use a template text (previously built) for body of email
Mail Recipient and Subject are hard-coded in script
Attach all files in the “To Be Sent” folder
If there are NO files in the folder, display an error msg so stating, and abort process
Send email

(Basically, I don’t know any Apple Script - I can work through some of it a little bit, but, compared to you all, it is better if I just say that I don’t know any)

Everything else makes sense. Would you be willing to create an apple script for me or point me in the right direction where I can find an Apple Script that would do that for me?

Thank JMichael TX! Again, I apologize for being less than clear. In all honesty, I wasn’t sure how much information I should post here - I didn’t want to waste anyone’s time with extemporaneous details, but at the same time I wanted to give enough information with which to work – not to mention, show/demonstrate that I have put in quite a bit of work myself (with what I know), and that I am not trying to get a free pass (wanting someone else to do all the work for me).

Hey JMichaelTX and Peternlewis,
I wanted to thank you again for jumping in to help me with my KM automation. I just wanted to update you all that I was able to create a macro from KM to fulfill what I needed to accomplish.
All the best,
Shawn

1 Like

That’s great! If you don’t mind sharing, please post your final, working, macro so others may benefit.

Alright,
It may be a bit "wonky" and elementary, but it works haha! I am sure there is a more efficient way to do it, but it seems to do the job well.
Timecard Macro Copy Macros.kmmacros (81.8 KB)

Here is what I have going on:
A macro to wake up and run a few programs (for other macros).

A separate Master Timecard Macro that runs a few individual macros which starts one minute later (every Monday)

That Master Macro enables and disables the associated macros wherein each individual macro will grab the right file from the right folder via applescript.

After the timecards are "grabbed" from the right folder, applescript then sets up an email with the appropriate settings, attaches the documents to the mail, KM then disables the macro and enables the next macro which is for next week's timecard.

It then sends the mail.

When I reach the end of the seven week cycle or the macros associated with the Master Macro, it then recycles through the Master Macro starting from the beginning.

The only thing that I have to do manually is to save the appropriate week's timecard into the appropriate folder (which I do when I first receive the timecards at the beginning of the project) and KM does the rest.

I am sure there is a better way (or more efficient way), but it seems to work - and I am glad that I have a way to do this that works with KM and doesn't rely on Automator. Thanks guys!

Moderator’s Note: Topic Changed to better reflect content of thread.

FROM: A more reliable method than Automator?
TO: How Can I Setup Periodic Trigger to Send Mail via Apple Mail with Attachments?