TFS Daily Emails

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

TFS Daily EmailsTeam 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.

 

 

12 comments on “TFS Daily Emails”

  1. Minh Reply

    Thanks a lot for your tips. Do you have any sollution for TFS2012? It doesn’t have View All Work Items as a Report so I can’t get the correct link and in the end, the display in mail is not correct.

    • Charlie Reply

      I haven’t tried on TFS 2012 yet, only 2010. I’d hope to find a report that had the list with basic formatting and no TFS Web navigation for a clean chunk of text for the email body.

    • Charlie Reply

      On the TFS web (2012) I have access to I cannot create any reports so am unable to try and create a ‘bare bones’ listing. Everything I’ve tried thus far shows the listing along with all of the TFS Web navigation elements. A clean report view would be my strategy once I’m able to create a report. I’m assuming that I would have a URL that would contain the list and be shareable. That’s what I would need to have inserted into the PowerShell script.

  2. alissa Reply

    Thank you so much for this!! One issue i found is that in the email report, the IDs are displaying an incorrect link. “/tfs/web/wi.aspx?pguid=2xxxxxx… etc”. it should have the http://

    • Charlie Reply

      I haven’t tried this, but the approach I would take would be to replace the part of the URL that seems to be the unique and distinct beginning of the relative URL with a complete beginning of an absolute URL. In your example above, that would be “/tfs/web/wi.aspx”.

      So the extra command in the PowerShell script would be:

      $htmlbody = $htmlbody -replace “/tfs/web/wi.aspx”, “http:///tfs/web/wi.aspx”

      Just put that before the $mail.Send() command.

  3. alissa Reply

    Hi Charlie, Thanks for the info! What actually worked was this:

    $mail.HTMLBody = $htmlbody
    $htmlbody = $htmlbody -replace “/tfs/web/wi.aspx”, “http://:8080/tfs/web/wi.aspx”
    $mail.HTMLBody = $htmlbody

    :)

  4. Amit Reply

    Could Not find “Tools–>View All Work Items as a Report”.
    Because TFS web URL is opening in VS 2012.
    Any luck?

  5. Ed Reply

    I’m running into an issue when scheduling the task to run and outlook is already open. (it works fine when I run it manually and outlook is open or when automatic and outlook is closed). Do you know how to resolve this issue?

    New-Object : Retrieving the COM class factory for component with CLSID
    {0006F03A-0000-0000-C000-000000000046} failed due to the following error:
    80080005 Server execution failed (Exception from HRESULT: 0x80080005
    (CO_E_SERVER_EXEC_FAILURE)).
    At C:\Users\essey\Desktop\Team Process\scripts\SendEEFunPCSnapshot.ps1:30
    char:12
    + $outlook = New-Object -com Outlook.Application
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMExcept
    ion
    + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Comman
    ds.NewObjectCommand

Add a comment...