Automate Emails for an Invalid Request

[vpr]

Sorry about the length, but I can't explain it in a short description... Please don't be turned off by the length of this msg!

Hello,

I work in a department with 19 other people. Regular agents fill out a form on their computer that gets sent out to us requesting information to be mailed to a customer, if the request is invalid we then send an email to the agents supervisor w/ a reason as to why.

I currently have been working on an excel document to try and automate the task of sending the emails out, I have a DATA tab that has the supervisors's name, email address, and nick name (A2:C64) In Columns F2:F10 are some pre-written statements as to why the request was invald and in G2:G10 there are some extended reasons.

Everyone in my department has a seperate sheet (19 of them, labeled as their name) and every user will fille out in this order: Account Number, Date of Request, Coach Name, Reason for Invalid (Points to Column F of the data tab), Description to be used if other is selected in the previous cell, and Email Sent (Yes/No). This is B5:HWhatever (will go on forever basically).

The macro should take all this information and generate an email that would then be sent to the correct superviosr.

Before the macro is run, I'd like a form to come up and ask if they'd like to run it on Everybody's Sheet, or just their own. I'd also like to have a progress bar while this is running as it could take some time depending on how many emails needed to be generated. The Information is then put into a "Team Summary" sheet which lists all the summary's sorted by date and then this information is used on a chart for the supervisor of MY department.

This sounds like a simple thing to be done, but i've been having problems getting it done... I'm open to suggestions to changing the workbook completly, but it does need to be friendly to multi-users at the same time...

All the work computers we're on have Office XP (Includding Outlook), with IE only.

Thanks a lot - I really appriciate it!

[OBP]

Don't appologise for giving too much data, it makes it easier for us to understand your problem. I am constantly amazed by how many people do great work, but are really using the wrong program, I think this should ideally be in Access, but that is just my view.
What you want to do with the emails sounds quite straight forward but it would be much easier for the people on here to help you if you could post a copy of the workbook, even if it has dummy data in it. To include your "pre-recorded" messages in the email you will need some tick boxes for the users to indicate which one(s) to include.

[vpr]

Thank you for the reply... I agree that this would be better suited for Access, however no one at works seems to know how to use Access... They use excel for everything... and when I say everything - I mean everything... Therefor, I'm just trying to stay in the realms of what they know instead of trying to teach a whole new thing, as I'm just an employee...not a trainer.

I'd be happy to post a makeshift workbook - I'll do that when I get home, as i'm not home at this moment...

Thank you again... I have been doing some work w/ the IE Progress bar in the KB (Thanks Mike (I think that's your name!)) and have made some minor modifications to make it look how I want... I've also figured out HOW I can accomplish what I want, it's just trying to figure the code out as i'm not a coder - I just tinker w/ stuff lol.

So when I get home, I'll post a make-shift copy of what I have hoping that would help...

I also read in another topic that people were upset about cross-posting... I did make a topic on MrExcel's site asking for similar help a few days prior to making this one, however, i've yet to get a reply which is why I decided to move ask over here...

Thank you again, and anything you need to make things smoother I'd be happy to provide...

[vpr]

Here is a barebones (ugly) copy of what is being used right now... As you will notice it's pretty basic... I hope to be able to spiff it up a little bit and make it a bit easier to use (like the tick boxes you mentioned)


Thanks In Advance!

[vpr]

bump :)

[geekgirlau]

As a general approach, I would first of all have a named range on each of the data sheets (for example, "datBob"). Create a user form with a combo box. As your Workbook Open event, you populate your combo box with "All", then each range name starting with "dat", then display the user form.

When the user clicks on an "OK" or "Run" button, the macro looks at the selected range, and filters it for all records that have been filled out completely (I'm assuming columns A to E are required, and the Email Sent should be blank). You can easily record the steps required to select and filter the range.

If there are any records matching your criteria, you then loop through each valid record individually, sending the email and then copying the record to the Team Summary sheet. If the user has selected "All", then you need to loop through all ranges in the workbook named "datXXXX".