TFS Daily Emails: How to get regular TFS query results into your inbox automatically

Team Foundation Server (TFS) allows you to create queries and save them as personal views or team views. You can even set up alerts on individual work items assigned to you so you know when they’re created or changed. However, TFS doesn’t give you a way to get a recurring snapshot of the TFS view you created into your inbox automatically. Sometimes waiting for a change event isn’t what you’re looking for.

I’ve used the process outlined below for TFS daily emails and it’s been working great for me.

I’m using a combination of the following:

• PowerShell
• TFS 2010
• Outlook 2010
• Task Scheduler

You may not have the exact same versions as I’m working with, but the general approach should still work for you. In my instance, I have it set up with default parameters and such to make it easier to share with folks I work with. You can adapt this to suit your own needs. I’ve broken the process down below for my own TFS daily emails.

Creating the PowerShell script

The PowerShell script will do the following for us:

• Make sure the email client (Outlook) is running. If it isn’t, start it.
• Package up your login credentials for TFS (yes, your password is stored here.. :-b)
• Capture the text (HTML) of the TFS view you want in your inbox.
• Send the email.
• Close the email client (Outlook).

Here is the PowerShell script I used to accomplish the above. This is an abbreviated version so it should be easier for you to modify, and hopefully easier to follow here.

#############################
# Set some default parameters
# The $url parameter is decribed more fully near the bottom of this post.
#############################
param (
[string]$url = "http://<yourserver:port>/tfs/web/UI/Pages/Reports/...",
[string]$username = [Environment]::UserName,
[string]$installdir = "C:Users" + $username + "",
[string]$passwordfile = $installdir + "mypass.txt"
)

#############################
# Check to see if Outlook is running.
# If it’s not, start it and set a flag so we can keep
# track of whether to shut Outlook down when done.
#############################
if (get-process -EA "SilentlyContinue" outlook | where {$_.ProcessName -eq "Outlook"})
{
    $outlookwasrunning = "true"
}
else
{
    $outlookwasrunning = "false"
    Start-Process Outlook
    #######    
    # add in a 15-second pause to give Outlook
    # some time to start up before trying to send
    # the email message and then closing Outlook
    #######
    Start-Sleep -s 15
}   
$outlook = New-Object -com Outlook.Application

#############################
# I create the protected password file by using a PowerShell 
# command at the prompt. Here's the PowerShell command I to 
# use to generate the file:
#
# Read-Host -AsSecureString | ConvertFrom-SecureSTring | Out-File mypass.txt
#
# This will prompt you to enter a value for your password
# and save to mypass.txt.
#############################
$password = type $passwordfile | ConvertTo-SecureString
$credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $username,$password

#############################
# Capture the HTML of the TFS view you want in your inbox.
#############################
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = $credentials
$htmlbody = $webclient.DownloadString($url)

#############################
# Create the email message, and send.
#############################
$mail = $outlook.CreateItem(0)
$today = Get-Date
$mail.subject = "Daily TFS query report for " + $today
$mail.To = $username
$mail.HTMLBody = $htmlbody
$mail.Send()
#############################
# add in a 5-second pause to give Outlook a moment to send the email.
#############################
Start-Sleep -s 5

#############################
# If Outlook wasn’t already running, close.
#############################
if ($outlookwasrunning -eq "false") {$outlook.Quit()}

Getting the correct TFS URL

1. Start your browser and navigate to your TFS instance (the Web view – not the TFS client in Visual Studio).
2. Go to (or create) the TFS view or query that you want to have sent to your inbox.
3. Next, go the Tools menu in the TFS web view and click on  View All Work Items as a Report.
4. Now, when the report shows up, right-click on the report and select Properties.
5. From the Properties window, copy the URL (all of it) to the clipboard.
6. Paste the URL into the PowerShell script above where I’ve put the placeholder for the $url parameter.

Test it!

To try this out, enter the following command from the PowerShell prompt once you’ve completed your script. I’ve called mine “SendTFSQueryAsEmail.ps1”, but you can use whatever name suits you best.

PS> .\SendTFSQueryAsEmail.ps1

You should get the TFS query results in your inbox.

Schedule it!

Now that you have the script ready and working you can use the Task Scheduler (comes with Windows) to set up your view to run at the same time every day.

1. Start the Task Scheduler. Go to Start, All Programs, Accessories, System Tools and click Task Scheduler.
2. Click Create Basic Task in the Actions pane on the right.
3. Enter a Name such as “My Daily TFS Email”, then click Next.
4. Choose a frequency (like Daily) and click Next.
5. Set the trigger time (such as 8:00:00 AM), click Next.
6. Leave the selection at Start a program, click Next.
7. Enter powershell.exe in the Program/script field.
8. Enter installdir\SendTFSQueryAsEmail.ps1 in the Add arguments (optional) field.
   1. If you put the files in a location other than c:\users\<youralias> you will also need to include the –installdir parameter. So the value you would type in is installdir\SendTFSQueryAsEmail.ps1 –installdir installdir
9. Enter your installdir in the Start in (optional) field, then click Next.
10. Click the checkbox for Open the Properties dialog for this task when I click Finish.
11. Click Finish.
12. In the Security Options section, select the radio button for Run whether user is logged on or not, then click OK.
13. If prompted, provide your credentials and click OK.
14. The task should fire off at the next scheduled time.
15. You can test it by selecting the task you just created in the Task Scheduler and clicking Run in the Actions panel on the right.

Important Note

If you’ve never run PowerShell on your computer before, you may need to grant your script permission to run. To do this, take the following steps.

1. Open a PowerShell window *as administrator*.
   1. Click Start, then All Programs, Accessories, Windows PowerShell
   2. Right-click Windows PowerShell and pick Run as Administrator.
2. Run the following command from the prompt.

PS> Set-ExecutionPolicy RemoteSigned

This will allow PowerShell scripts to run on that PC. RemoteSigned just means local scripts don’t need to be signed, remote ones do. I’m assuming you’d be running locally.