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!



Sub SetOutlookTask09()

Dim Wks As Worksheet

Sheets("Calendar").Select


  Dim olApp As Object
  Dim olTask As Object
  
  ' Start Outlook and a new Outlook Session
    Set olApp = New Outlook.Application
      olApp.Session.Logon
      
  ' Create the Task Item
    Set olTask = olApp.CreateItem(olTaskItem)
    
  ' Set the Task properties and Save them
   
   With olTask
.StartDate = Range("I32")
.DueDate = Range("I31")
.Subject = Range("I30")

  ' !!!MESSAGE PART - Type the message here...

.Body = "Blurb re task go in here!"

 'Set aRecipient = olTask
 .Recipients.Add ("rae@notarealaddressforthis!.com")

.Assign
.Save
.Send


 End With
  ' I have commented this bit out as I don't want to close Outlook after i've sent the task request(s)
  ' End the Session and Quit Outlook
  '  olApp.Session.Logoff
  '  olApp.Quit
   
  
  ' Release the Objects we created
    Set olApp = Nothing
    Set olSession = Nothing
    Set olTask = Nothing
      
 End Sub
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!!