Link to home
Start Free TrialLog in
Avatar of Andrew Walker
Andrew Walker

asked on

EMAIL RANGES BASED ON CURRENT TIME

Hi Guys
I am looking for some help, I am building a macro to help me paste the contents of an excel sheet into the body of an email for an hourly which  i have done using Ron De Bruin's code and that works fine.

My issue is the report get's sent every hour starting at 9am and includes up to the previous two hours report e.g
10am report will include the snapshot from 9am as well
11am report will include 9 and 10am overviews as well, the maximum ever sent is 3 intervals so for example the 13:00 report would show 13:00,12:00 and 11-does that make sense?

User generated image
i need a way for the macro to select the correct current interval and correct amount of previous intervals based on current time and paste into email body

I have included an example file which does have some vba in the sheet and module already. I have named the ranges  from 09:00 to 20:00 and also got so formulas in column a and b which i was planning to use to get correct interval time but you guys know better

I hope this makes sense and any q's please ask I will be in your debt forever haha
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

No file attached.
gowflow
Avatar of Andrew Walker
Andrew Walker

ASKER

sorry guys please see attached
EMAIL-RANGE.xlsm
So let me recap what  I understood

From what I see from this macro roughly is that you select a range or multiple ... then run the macro and what ever you selected it will convert it as HTML so it can be put in a HTML mail.

and what your looking for is:

when you run the macro, you want it to simply select the correct time frame based on what you explained if ran now at 11 it should take 11,10 and 9

Is this what you want ?
gowflow
yeah thats right, but if you ran at 10 for example it should only take 10 and 9 because they would be the only 2 intervals available. and 9 would only take 1 interval as it is the first report of the day, I know I am waffling on haha so I do apologize
Sure ... I see you only posted the strict bit of code as this is a function ... it return the HTML code that is pasted in the email part of an other function or sub right ?

Question: how now do you call this RangetoHTML manually or it is a code that calls it ? can't be manual as it is a function
right ?

gowflow
there is an addition in module , I have never tried to call it manually tbh
ok then I need this addition as my code need to be incorporated there !!! I need the sub that calls RangeToHTML

gowflow
all the code i used should be in the file i uploaded the code to get it to call range to html is in module , and the rest is in the sheet.
Ooops !!! Did not see the sheet :(

Will post in a moment.
gowflow
np man thanks for your help
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this is fantastic i can not thank you enough!!