Hi Every one,

Following is a code that prints out weekly individual task lists from a
master Critical Path.

The code first creates a list of unique individuals on a temporary page,
- then filter my critical path in a Column called "Next week" to only show
action requiring follow-up on following week.
- then prints-out a list of individuals who will receive task lists,
- and finaly loops through alll values in "MyUniqueRng" to filter and print
out the list name by name.

What I would like to do, is instead of Printing-out these individuals task
lists,
sending them by e-mail whith outlook

So first it means that after filtering the list by employee's names one by
one the macro must:
- Create a folder in My Document & name it "Task Lists - Current Date"
- Create a workbook and name it with Mycell Value (Name of
employee)followed by current date
- Paste the filetered list (Only visible part) to the workbook nwe have just
created

- vlookup for the employee's e-mail address

- send it to the employee by e-mail with outlook

- store the workbook in the folder created by the macro befor the loop.

(Provided that all names are listed on another separate sheet (Whole list
of
employees) and that I would write their e-mail addresses on a column at the
right of the "Name" column, I assume that by a loop through the range
"MyUniqueRng" combined to a V-Lookup these addresses could easily be pasted
in Outlook to send individual e-mails. )


It would be great If somebody could assist me in this matter.

Thanks,

Paul



Sub Print_Next_Weeek_Task_Lists()

Application.ScreenUpdating = False
Dim newWks As Worksheet
Dim curWks As Worksheet
Dim myRng As Range
Dim myRng2 As Range
Dim myUniqueRng As Range
Dim myCell As Range


Set curWks = Sheets("Critical Path")
Set newWks = Worksheets.Add

With curWks
.AutoFilterMode = False
Set myRng = .Range("A6", .Cells.SpecialCells(xlCellTypeLastCell))
Set myRng2 = .Range("A5", .Cells.SpecialCells(xlCellTypeLastCell))
myRng2.AutoFilter Field:=16, Criteria1:="<"
myRng.Columns(4).Copy _
Destination:=newWks.Range("a1")
With newWks
.Range("a1", .Cells(.Rows.Count, "a")).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("b1"), Unique:=True
.Range("b:b").Sort Key1:=Range("b1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Set myUniqueRng = .Range("b1", .Cells(.Rows.Count,
"b").End(xlUp))
End With

With Sheets("Task List Distribution NW") ' Prints Task List
Distribution Record
myUniqueRng.Copy
Sheets("Task List Distribution NW").Select
Range("A7").PasteSpecial (xlPasteValues)
.PrintOut Copies:=1, preview:=False
Range("A7:A60").ClearContents
End With


.Range("L4").Value = "Next Week"
''''LOOP STARTS
For Each myCell In myUniqueRng.Cells
myRng2.AutoFilter Field:=4, Criteria1:=myCell.Value
.Range("O3").Value = myCell.Value
.PrintOut Copies:=1, preview:=False
Next myCell
'''''LOOP STOPS
.Range("O3:P3").ClearContents
.Range("L4").ClearContents
If .FilterMode Then
.ShowAllData
End If

End With

Application.DisplayAlerts = False
newWks.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Hi

Sounds like a not-too-difficult task for Word's Mail Merge with Excel table
as datasource - without any code.

In general:
You must have a master tasks page, where all tasks are listed along with
individual names, dates, and weeknumbers calculated from dates.

You must have a page where all individuals are listed (a row for an
individual), along with mail address. There are additional columns, where
for every individual 1st, 2nd, etc. task for next week is retrieved from
master page (Probably you have to have some hidden column where an
indicator, composed from individual ID and the number of task for this
individual, is calculated for every task from next week. For other weeks the
formula must return an empty string.). In task list composed with mail
merge, every column is used to type a task on separate row. You have to
estimate a reasonable number for those columns - when there isn't enough
columns, some tasks may remain excluded from task list, when there is too
many columns, there will be a lot of empty rows in sent tasl lists.

You have to create a master mail merge document in Word, with table on
individuals list sheet in your Excel workbook as source (When you made any
changes in your Excel workbook before, save it before). Tasks in task list
will be fields read from task columns in source table - Task1 for 1st task
in next week, Task2 for 2nd task in next week etc.

When running mail merge, you set filter to Task1<"", and select e-mail as
destination. Point at the field with e.mail address, when asked, and
proceed. All mails are sent to recipients.

Don't forget to save the master mail merge document. In future weeks edit
the task list, save the workbook, open the master mail merge document, and
process mail merge again.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Paul." wrote in message
...
Hi Every one,

Following is a code that prints out weekly individual task lists from a
master Critical Path.

The code first creates a list of unique individuals on a temporary page,
- then filter my critical path in a Column called "Next week" to only show
action requiring follow-up on following week.
- then prints-out a list of individuals who will receive task lists,
- and finaly loops through alll values in "MyUniqueRng" to filter and
print
out the list name by name.

What I would like to do, is instead of Printing-out these individuals task
lists,
sending them by e-mail whith outlook

So first it means that after filtering the list by employee's names one by
one the macro must:
- Create a folder in My Document & name it "Task Lists - Current Date"
- Create a workbook and name it with Mycell Value (Name of
employee)followed by current date
- Paste the filetered list (Only visible part) to the workbook nwe have
just
created

- vlookup for the employee's e-mail address

- send it to the employee by e-mail with outlook

- store the workbook in the folder created by the macro befor the loop.

(Provided that all names are listed on another separate sheet (Whole
list
of
employees) and that I would write their e-mail addresses on a column at
the
right of the "Name" column, I assume that by a loop through the range
"MyUniqueRng" combined to a V-Lookup these addresses could easily be
pasted
in Outlook to send individual e-mails. )


It would be great If somebody could assist me in this matter.

Thanks,

Paul



Sub Print_Next_Weeek_Task_Lists()

Application.ScreenUpdating = False
Dim newWks As Worksheet
Dim curWks As Worksheet
Dim myRng As Range
Dim myRng2 As Range
Dim myUniqueRng As Range
Dim myCell As Range


Set curWks = Sheets("Critical Path")
Set newWks = Worksheets.Add

With curWks
.AutoFilterMode = False
Set myRng = .Range("A6", .Cells.SpecialCells(xlCellTypeLastCell))
Set myRng2 = .Range("A5", .Cells.SpecialCells(xlCellTypeLastCell))
myRng2.AutoFilter Field:=16, Criteria1:="<"
myRng.Columns(4).Copy _
Destination:=newWks.Range("a1")
With newWks
.Range("a1", .Cells(.Rows.Count, "a")).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("b1"),
Unique:=True
.Range("b:b").Sort Key1:=Range("b1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Set myUniqueRng = .Range("b1", .Cells(.Rows.Count,
"b").End(xlUp))
End With

With Sheets("Task List Distribution NW") ' Prints Task List
Distribution Record
myUniqueRng.Copy
Sheets("Task List Distribution NW").Select
Range("A7").PasteSpecial (xlPasteValues)
.PrintOut Copies:=1, preview:=False
Range("A7:A60").ClearContents
End With


.Range("L4").Value = "Next Week"
''''LOOP STARTS
For Each myCell In myUniqueRng.Cells
myRng2.AutoFilter Field:=4, Criteria1:=myCell.Value
.Range("O3").Value = myCell.Value
.PrintOut Copies:=1, preview:=False
Next myCell
'''''LOOP STOPS
.Range("O3:P3").ClearContents
.Range("L4").ClearContents
If .FilterMode Then
.ShowAllData
End If

End With

Application.DisplayAlerts = False
newWks.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Hi Arvi,

Thanks a lot for your very ddetailed reply.

Unfortunately I believe I must do something in the loop part of my macro
which as it is prinnts out weekly individual task lists from a pretty complex
Master Critical Path Document.

You can see the LOOP at the end of the macro

The first part of the macro finds out who are the people who will receive
task lists

Then The Loop filters the Critical Path Name By Name to print individual
task list for the following week

Instead of printing these task lists I want to send them by e-mail to all
the individuals following the procedure described on my first post

As the list of individual is different every week and as the structure of
the document is already in place I thing evrything must be done in this loop.

-Filter the Critical Path to only show actions for next week

-then in another column filter individual by individual

-for each of them, create a workbook named ("Task List" "Date")

-copy the visible part of the filtered Critical Path which is actually the
individual task list

-then paste it to the workbook we have just created.

-close this workbook

-attach it to a standard e-mail (It could be a mail merge template with a
field referring to the name of the individual

-copy and paste the e-mail address of the individual (as I was saying in the
document there is a sheet with all possible individuals listed in one column
with their corresponding e-mail addresses in the next column, so I assume we
could easily retreive it with a vlookup)

-send the email


If you or somebody has an idea I would really appreciate


Thanks again Arvi

"Arvi Laanemets" wrote:

Hi

Sounds like a not-too-difficult task for Word's Mail Merge with Excel table
as datasource - without any code.

In general:
You must have a master tasks page, where all tasks are listed along with
individual names, dates, and weeknumbers calculated from dates.

You must have a page where all individuals are listed (a row for an
individual), along with mail address. There are additional columns, where
for every individual 1st, 2nd, etc. task for next week is retrieved from
master page (Probably you have to have some hidden column where an
indicator, composed from individual ID and the number of task for this
individual, is calculated for every task from next week. For other weeks the
formula must return an empty string.). In task list composed with mail
merge, every column is used to type a task on separate row. You have to
estimate a reasonable number for those columns - when there isn't enough
columns, some tasks may remain excluded from task list, when there is too
many columns, there will be a lot of empty rows in sent tasl lists.

You have to create a master mail merge document in Word, with table on
individuals list sheet in your Excel workbook as source (When you made any
changes in your Excel workbook before, save it before). Tasks in task list
will be fields read from task columns in source table - Task1 for 1st task
in next week, Task2 for 2nd task in next week etc.

When running mail merge, you set filter to Task1<"", and select e-mail as
destination. Point at the field with e.mail address, when asked, and
proceed. All mails are sent to recipients.

Don't forget to save the master mail merge document. In future weeks edit
the task list, save the workbook, open the master mail merge document, and
process mail merge again.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Paul." wrote in message
...
Hi Every one,

Following is a code that prints out weekly individual task lists from a
master Critical Path.

The code first creates a list of unique individuals on a temporary page,
- then filter my critical path in a Column called "Next week" to only show
action requiring follow-up on following week.
- then prints-out a list of individuals who will receive task lists,
- and finaly loops through alll values in "MyUniqueRng" to filter and
print
out the list name by name.

What I would like to do, is instead of Printing-out these individuals task
lists,
sending them by e-mail whith outlook

So first it means that after filtering the list by employee's names one by
one the macro must:
- Create a folder in My Document & name it "Task Lists - Current Date"
- Create a workbook and name it with Mycell Value (Name of
employee)followed by current date
- Paste the filetered list (Only visible part) to the workbook nwe have
just
created

- vlookup for the employee's e-mail address

- send it to the employee by e-mail with outlook

- store the workbook in the folder created by the macro befor the loop.

(Provided that all names are listed on another separate sheet (Whole
list
of
employees) and that I would write their e-mail addresses on a column at
the
right of the "Name" column, I assume that by a loop through the range
"MyUniqueRng" combined to a V-Lookup these addresses could easily be
pasted
in Outlook to send individual e-mails. )


It would be great If somebody could assist me in this matter.

Thanks,

Paul



Sub Print_Next_Weeek_Task_Lists()

Application.ScreenUpdating = False
Dim newWks As Worksheet
Dim curWks As Worksheet
Dim myRng As Range
Dim myRng2 As Range
Dim myUniqueRng As Range
Dim myCell As Range


Set curWks = Sheets("Critical Path")
Set newWks = Worksheets.Add

With curWks
.AutoFilterMode = False
Set myRng = .Range("A6", .Cells.SpecialCells(xlCellTypeLastCell))
Set myRng2 = .Range("A5", .Cells.SpecialCells(xlCellTypeLastCell))
myRng2.AutoFilter Field:=16, Criteria1:="<"
myRng.Columns(4).Copy _
Destination:=newWks.Range("a1")
With newWks
.Range("a1", .Cells(.Rows.Count, "a")).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("b1"),
Unique:=True
.Range("b:b").Sort Key1:=Range("b1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Set myUniqueRng = .Range("b1", .Cells(.Rows.Count,
"b").End(xlUp))
End With

With Sheets("Task List Distribution NW") ' Prints Task List
Distribution Record
myUniqueRng.Copy
Sheets("Task List Distribution NW").Select
Range("A7").PasteSpecial (xlPasteValues)
.PrintOut Copies:=1, preview:=False
Range("A7:A60").ClearContents
End With

''''''FILTER TO SHOW ONLY ACTIONS FOR NEXT WEEK
.Range("L4").Value = "Next Week"
''''LOOP STARTS FILTER INDIVIDUAL BY INDIVIDUAL
For Each myCell In myUniqueRng.Cells
myRng2.AutoFilter Field:=4, Criteria1:=myCell.Value
.Range("O3").Value = myCell.Value
.PrintOut Copies:=1, preview:=False
Next myCell
'''''LOOP STOPS
.Range("O3:P3").ClearContents
.Range("L4").ClearContents
If .FilterMode Then
.ShowAllData
End If

End With

Application.DisplayAlerts = False
newWks.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub