Re: How to auto send email, from multiple sheets, it works on one not on multiple

[nje]

In the attached sample file, the auto email sender works when the book is first opened, but only when there is just one sheet in the workbook with emails to be sent.
I really want it to work when there is more than one sheet with emails to be sent out, it will end up with about 10 sheets in total.
The sheets will be similar but with different data, but all the dates in the same column.
I haven't got a clue how or if this can be done, I don't think this is an easy problem to solve but I would appreciate any help or advice on how to solve it, if it's possible.

Thank you

[nje]

In the workbook, there is a code for protecting/unprotecting all sheets and when I unprotect, you see it run through all the sheets quickly and unprotect them.
I have fiddled with the code from it and tried to incorporate bits of it into the code for the auto send emails, but no joy.
My thinking was that if the email sender only works on the active sheet then by running through all of them, it might activate the email sender.
I'm clutching at straws here, but still no further forward, but not for the want of trying.
Any suggestions would be very welcome!!
Thanks

[nje]

If what I'm asking is not possible, I would appreciate it if someone could tell me please, or even make an alternative suggestion. The only alternative I can come up with at the moment is to have 10 different work books with just one sheet in each, but that doesn't seem to be too efficient.

[nje]

I've spent the best part of 7 hours today searching this forum and the net for a solution, I've tried to incorporate bits of code from all sorts of code for looping multople sheets, auto opening workbooks, auto sending this and auto that but I've been defeated by error 438, error 13, mistyped object errors and every other error excel can produce. However, I have managed to recover the original code and it still works on the single sheet.
I'm not going to give up yet, because having searched all alternatives I think what I need is achievable, I just don't have a clue how.
Any help would be appreciated

[john55]

just try it....

Please Login or Register  to view this content.

[nje]

John55, Thank you for your reply. You have gone to a lot of trouble but I only want to send a message, not an attachment. If I could somehow incorporate the part of your code into the one I already have to activate all the sheets in the workbook (there will be about 10) then this would be perfect.
Thank you once again

[john55]

or

Please Login or Register  to view this content.

[nje]

Wow, that's some code, it goes well over my head. The reason why I can't use an attachment sent with the email, is because the emails will be going to a mobile phone, hence it just sends a short message out to alert them.
The code in my sample book works well, but as I said, it will have about 10 sheets and as it is at the moment, it will only automaticlly send the email for one sheet, So I'm looking to extend the current code to include other sheets. Manythanks for your contribution so far

[john55]

yr file is protected, I have no ideea what code are you using.
this one (the last one) can be use for sending ranges from many sheets. just edit the code for yr needs.
hope it helps you!

[nje]

john55, thanks and sorry for the protection I thought I'd removed it.
I would love to be good enough to 'just edit it' but unfortunately despite trying to incorporate bits of yours into the original I'm out of my depth.
I have attached an unprotected version, if you would be good enough to take a look
Thanks

[john55]

see this

Please Login or Register  to view this content.

[nje]

john55, thanks again for all your effort, the code looks fantastic but I'm going to need some time to try and figure it out. I can't readily work out how it figures out when each review date in every row in column 'K' is reached and how it sends out just the details from the specific cells in each row seperately

[john55]

hi man,
I really don't get it...could you be more specific, what you want to send 'n you can't?!
also, pls take a look at the top of code, I unprotect the sheets because I see you protect some cells and then at the bottom of code I protect them again, if you do not need to protect the sheets, remove the protection and remove those parts regarding protection.

[nje]

John, My apologise for any confusion In my workbook the re will be about 10 sheets.
In every row in column K there will be a review date, This will be the same in every sheet.
The current code works and sends an email with the contents from columns 34, 35, 2, 3, 4, 37, 8, 40
The email is automatically sent when the workbook is opened
The only problem with the current code is that, it is only sending an email for one sheet
I want it to send an email for all sheets where the date in column K is equaled or in the past
So, my code works but I don't know how to adapt it to work on more than one sheet.
I have attached the workbook where the code works on just the one sheet and not the other (Engine & Propeller)
Thanks again John for your time and expertise The code I'm on about that works is in module 1

[john55]

I am sorry, I can not help you with yr code.

[nje]

Thanks for trying, but I'm sure there must be someone out there who can

[jaslake]

Hi nje

Try this

Please Login or Register  to view this content.

[nje]

Thank you for your reply and code.
I have added the second half of my code (the bit with where to send the email) but have recieved error coded 13 Type Mismatch in line If SendAnOutlookEmail(wksReminderList, i) Then
I have attached the workbook with the full code in it.
Thanks again. it looks promising

[jaslake]

Hi nje

Change this line of Code (drop the s)

Please Login or Register  to view this content.

To this

Please Login or Register  to view this content.

[nje]

jaslake - Thank you very much, it works brilliantly. your expertise is very much appreciated

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.

[nje]

Just when I thought everything was perfect!!! The success of solving the last problem has now highlighted another
I'm not sure if I should start another thread or not, but here is the problem in the once again attached sample.
It's now the message that is sent out.
What ever sheet is active when I close the book, remains the active sheet and when the critical date is reached in column 'K' and the email is prompted the email only collects the info from the active sheet even though it reacts to the dates in both the engine and propeller sheet. the info it sends only originates from one of the sheets.
When the email is propmted, it should collect the info from the relevant row oor rows of each sheet
So if the active sheet is 'engine' and I close the book with 'engine active and the 'propeller sheet dates activate the email, the emails sent out will be from the engine sheet and only contain information from the engine sheet and not the propeller sheet. Sorry I realise it must be confusing to read this.
I think I should be naming the sheets somewhere in the code, but have no idea where, or what to put
Thanks

[jaslake]

Hi nje

I see it...I'll look at it.

[nje]

That's good of you, thank you

[jaslake]

Hi nje

In the Private Function SendAnOutlookEmail replace this Code

Please Login or Register  to view this content.

With this Code

Please Login or Register  to view this content.

Why??? The Old line of Code is picking all Data except

Please Login or Register  to view this content.

from the Active Sheet.

The New Code picks up all Data from

Please Login or Register  to view this content.

[nje]

That is a very good explanation, which I actually understood, I wouldn't have been able to work it out, if I had sat here all year though.
Your solution, has solved it again, thank you very much. I take it now that I can just extend the rows downwards on the existing sheets and then add the other eight sheets and it won't affect anything, obviously I'll have to copy the formulas, email details etc down. Many thanks again for your efforts

[jaslake]

Hi nje

I believe you should be good to go

Please Login or Register  to view this content.

Let me know of issues.