Assign task to people in range of cells & attach file

Hallo all

Long time since I was last here asking questions (I do browse round a lot) but I've been getting stuck again!

I've managed to cobble together a macro from various posts here but I need it to do some more if anyone could help me please?!

I have a spreadsheet called “Schedule” that lists 15 tasks that need to be done by a certain date each month (dates can change month by month) and each task will need to be completed by a different number of people. E.g. Task01 by 4 people, Task02 by 6 people... etc

It has 3 tabs:
Responsibilities – which has a list of tasks and details etc
Schedule – flowchart of tasks and timescales

Calendar – which is for my use only and has calculations on when tasks have to be done and by whom including email addresses etc


I would like to be able to press a button so that each task is created and emailed to all the people involved in each task

So far I have managed to get it to work to assign a task and send it. It gets the Due Date, Start Date and Subject Line from Cells in the 'Calendar' tab in my spreadsheet


I am hoping to be able to do 2 more clever things (well 3 if I am being greedy!)...

1) To select the email addresses from the range of cells where they are - which in this case will be I2:I21 - the single task named in cell I30 will go to ALL addresses in the 19 Cells I2:20, but there may only be 8 or so of these cells filled in as each task has a different number of people involved, but not more than 20


2) To attach a copy of the spreadsheet called "Schedule" BUT I want to hide the Calendar tab BEFORE I send it and the Macro buttons are on that as is all the detail needed to assign/send the tasks

Is this last question possible or should I be considering saving a version of the file with the tab hidden separately and attach it from the folder (both the spreadsheet with the macro and the spreadsheet with the tab hidden will be in the same folder) instead of the one I am sending it from?

Here is my code that works as far as it goes!

Please Login or Register  to view this content.

Many thanks in advance

Rae

PS - the greedy third task would be to have all 15 tasks go off at a touch of one button, but one button per task is going to make very happy so the-multi button idea will be a fun project for me to work on once this part is sussed!!

Hallo

Sorry for delay in reply - it's been quite a commute home!

Atached is a file with the same layout but stripped of all data in first 2 tabs and te emails in the 3rd a fake as you can see!

R

Hallo again....

Have been having a think about this and my main problem is being able to get the macro to select the email addresses from the cells and use them to assign the task - is there any one who could me with this please?

The bit about attaching a file I can work around until I can find out how to do it (I have decided that I will just include a link to the file in the body of the task as the file is held on a newtork drive that everyone has access to - seems easier to my little brain to do this for now!)

So I'm just after being able to:

select the email addresses from the range of cells where they are - which in this case will be I2:I21 - the single task named in cell I30 will go to ALL addresses in the 19 Cells I2:20, but there may only be 8 or so of these cells filled in as each task has a different number of people involved, but not more than 20

I am hoping this isn't too difficult to do, as so far using the code I have posted above I can get a task assigned ok if I have an email address written in the code!

TIA

R