Automate a status report from Outlook tasks (vbscript)

  • Thread starter John McGee via OfficeKB.com
  • Start date
J

John McGee via OfficeKB.com

Hello:

I am trying to automate a status report from Outlook tasks for my team. I
have a few questions:

1. The task folders reside in our teams mailbox. How do I attach to a
different mailbox in my script?

2. The script below only counts tasks in the root of the tasks folder.
How do I count subfolders of the tasks folder? I have 17 subfolders I
need to loop through.

Thanks for a great site and any help you may be able to offer.
John McGee

Credit to Bruce Szabo for the original code.
http://www.serverwatch.com/tutorials/article.php/1475621
----------------------------------------------------------------------------
---------


Dim objOutlook
Dim objNameSpace
Dim objFolder
Dim MyItems
Dim CurrentTask
Dim strOutput

Const olMailItem = 0
Const olTaskItem = 3
Const olFolderTasks = 13


'Create Outlook, Namespace, Folder Objects and Task Item
Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderTasks)
Set MyItems = objFolder.Items
dtLastWeek = DateAdd("d", -7, date)
dtNextWeek = DateAdd("d", +7, date)
'Loop through all tasks with a Due Date on or before Today.
strOutput = strOutput & "<h2>Due This Week</h2>"
icount = 0
For Each CurrentTask in MyItems
If currentTask.DueDate >= dtLastWeek And
CurrentTask.DueDate <= Date Then
icount = icount + 1
strOutput = strOutput & icount & ". " &
CurrentTask.Subject
if CurrentTask.Complete then
strOutput = strOutput & "-<b> COMPLETED</b>-" &
vbCrLf
else
strOutput = strOutput & vbCrLf
end if
if len(currentTask.Body) > 0 then
strOutput = strOutput & "<blockquote><b>Notes: </b>"
& CurrentTask.body & "</blockquote>" & vbCrLF & vbCrLF
else
strOutput = strOutput & vbCrLf
end if
End If
Next

strOutput = strOutput & "<h2>Due Next Week</h2>"
icount = 0
For Each CurrentTask in MyItems
If currentTask.DueDate > date And CurrentTask.DueDate <=
dtNextWeek Then
icount = icount + 1
strOutput = strOutput & icount & ". " &
CurrentTask.Subject
if CurrentTask.Complete then
strOutput = strOutput & "-<b> COMPLETED</b>-" &
vbCrLf
else
strOutput = strOutput & vbCrLf
end if
if len(currentTask.Body) > 0 then
strOutput = strOutput & "<blockquote><b>Notes: </b>"
& CurrentTask.body & "</blockquote>" & vbCrLF & vbCrLF
else
strOutput = strOutput & vbCrLf
end if
End If
Next

strOutput = strOutput & "<h2>Future Tasks</h2>"
icount = 0
For Each CurrentTask in MyItems
If currentTask.DueDate >= dtNextWeek Then
icount = icount + 1
strOutput = strOutput & icount & ". " &
CurrentTask.Subject
strOutput = strOutput & " Due -<b> " &
currentTask.DueDate & "</b>" & vbCrLf
if len(currentTask.Body) > 0 then
strOutput = strOutput & "<blockquote><b>Notes: </b>"
& CurrentTask.body & "</blockquote>" & vbCrLF & vbCrLF
else
strOutput = strOutput & vbCrLf
end if
End If
Next

' create new outgoing message

Set objMsg = objOutlook.CreateItem(olMailItem)

objMsg.To = "(e-mail address removed)" ' your reminder
notification address
objMsg.Subject = "Status Report - " & Date()
objMsg.Display
strOutput = replace(strOutput,vbCrLF,"<br>")
objMsg.HTMLBody = strOutput

'Clean up
Set objFolder = Nothing
Set objNameSpace = Nothing
set objOutlook = Nothing

set objMsg = Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top