Microsoft Bob

Just a short, simple blog for Bob to share some tips and tricks.

Be sure to check out my non-technical blog at www.bobsbasement.net.

Month List

Outlook Macro: Export Appointments to TSV File

Using this Outlook VBA Macro

Over the years, I had noticed that I had appointments from years ago stuck in my calendar, so I wrote this Outlook VBA Macro to export a list of all my appointments to a tab-separated (TSV) file so that I could open it in Microsoft Excel and analyze all of my appointments. (After writing this macro, I wrote my Delete Old Appointments macro to delete old appointments.)

Outlook VBA Macro Example Code

Sub ExportAppointmentsToTsvFile()

Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objAppointement As Outlook.AppointmentItem
Dim objNetwork As Object
Dim objFSO As Object
Dim objFile As Object
Dim strUserName As String

Set objOutlook = Application
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

Set objNetwork = CreateObject("WScript.Network")

strUserName = objNetwork.UserName

If InStr(strUserName, "\") = 0 Then
strUserName = objNetwork.UserDomain & "\" & strUserName
End If

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("c:\outlook-calendar.tsv")

objFile.WriteLine "UserName" & vbTab & _
"AppointementStart" & vbTab & _
"AppointementEnd" & vbTab & _
"AppointementRecurrenceState" & vbTab & _
"AppointementSubject" & vbTab & _
"AppointementSize" & vbTab & _
"AppointementUnRead" & vbTab & _
"AppointementLocation"

For Each objAppointement In objFolder.Items
DoEvents
objFile.WriteLine strUserName & vbTab & _
objAppointement.Start & vbTab & _
objAppointement.End & vbTab & _
objAppointement.RecurrenceState & vbTab & _
objAppointement.Subject & vbTab & _
objAppointement.Size & vbTab & _
objAppointement.UnRead & vbTab & _
objAppointement.Location
Next

MsgBox "Done!"

End Sub
Posted: Sep 30 2008, 13:33 by Bob | Comments (0)
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: Office | VBA
Social Bookmarks: E-mail | Kick it! | DZone it! | del.icio.us

Outlook Macro: Delete Old Appointments

Using this Outlook VBA Macro

Over the years, I had noticed that I had appointments from years ago stuck in my calendar, so I wrote this Outlook VBA Macro to help keep my outlook calendar thinned-out.

Note: This macros deletes appointments and attachments from your Outlook calendar - make sure that you want to do this before running this macro.

By default the macro will:

  • Delete all appointments over a year old (except recurring appointments.)
  • Delete all attachments from 6-month-old appointments.
  • Delete large attachments from 2-month-old appointments.

You can alter these dates by adjusting the appropriate lines in the macro.

Outlook VBA Macro Example Code

Sub DeleteOldAppointments()

Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objAppointement As Outlook.AppointmentItem
Dim objAttachment As Outlook.Attachment
Dim objNetwork As Object
Dim lngDeletedAppointements As Long
Dim lngCleanedAppointements As Long
Dim lngCleanedAttachments As Long
Dim blnRestart As Boolean
Dim intDateDiff As Integer

Set objOutlook = Application
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

Here:

blnRestart = False

For Each objAppointement In objFolder.Items
DoEvents
intDateDiff = DateDiff("d", objAppointement.Start, Now)

' Delete year-old appointments.
If intDateDiff > 365 And objAppointement.RecurrenceState = olApptNotRecurring Then
objAppointement.Delete
lngDeletedAppointements = lngDeletedAppointements + 1
blnRestart = True

' Delete attachments from 6-month-old appointments.
ElseIf intDateDiff > 180 And objAppointement.RecurrenceState = olApptNotRecurring Then
If objAppointement.Attachments.Count > 0 Then
While objAppointement.Attachments.Count > 0
objAppointement.Attachments.Remove 1 Wend
lngCleanedAppointements = lngCleanedAppointements + 1
End If

' Delete large attachments from 60-day-old appointments.
ElseIf intDateDiff > 60 Then
If objAppointement.Attachments.Count > 0 Then
For Each objAttachment In objAppointement.Attachments
If objAttachment.Size > 500000 Then
objAttachment.Delete
lngCleanedAttachments = lngCleanedAttachments + 1
End If
Next
End If
End If
Next

If blnRestart = True Then GoTo Here

MsgBox "Deleted " & lngDeletedAppointements & " appointment(s)." & vbCrLf & _
"Cleaned " & lngCleanedAppointements & " appointment(s)." & vbCrLf & _
"Deleted " & lngCleanedAttachments & " attachment(s)."

End Sub
Posted: Sep 30 2008, 13:31 by Bob | Comments (3)
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: Office | VBA
Social Bookmarks: E-mail | Kick it! | DZone it! | del.icio.us

FTP Clients - Part 1: Web Browser Support

Since we've been testing a lot of FTP clients with our new FTP server for IIS 7, I thought that it would be a good idea to discuss some of the highlights and pitfalls that we have run into when testing various clients.

I thought that I'd begin this series with an examination of several web browsers, which are really not the best FTP clients around - web browsers are mostly just "putting a pretty face" on an FTP site rather than functioning as an FTP client. In any event, here's a summary table of different features that I tested with a few web browsers:

Client NameDirectory
Browsing
Explicit
FTPS
Implicit
FTPS
Virtual
Hosts
True
HOSTs
FireFox 3.0.2 (Mozilla) Rich N N Y N
Google Chrome 0.2.149 (Beta) Basic N N Y N
Internet Explorer 7.0 Basic N N Y N
Opera 9.5.2 Rich N N Y N

See the individual client notes below for more information on each client.

Firefox 3.0.2

Directory Browsing:
Some older Firefox versions only worked with UNIX directory listings - you could not use MS-DOS directory listings. That said, I was able to use version 3.0.2 with MS-DOS directory listings but only when I did not enable four-digit years; if you enable four-digit years, Firefox will only display blank directory listings.
 
Outside of that problem, Firefox has a great user experience for browsing FTP sites that's very reminiscent of Apache's Fancy Indexing feature for directories that have no home page.
FTP SSL/TLS:
SSL connections cannot be created by this client.
Virtual Hosts:
You can create a connection to an FTP7 virtual host only if a "Global Listener" FTP site is created that requires authentication, after which you can enter the "sitename|username" syntax to connect to the virtual host. There is no method for entering custom commands, so HOST does not work.
Observations:
One really odd thing that Firefox does is allow you to keep hitting the "Up to a higher level directory" link and it just keeps appending ".." to the path. That's really odd, and it doesn't seem to break anything, but it sure is annoying.



One last annoyance is that my default installation of Firefox cached the directory listings from the FTP server in between sessions. So I used Firefox to browse an FTP site, then completely closed Firefox, then when I came back I saw directory listings from my previous session instead of updated directory listings until I forced a refresh.

Internet Explorer 7.0

Directory Browsing:
Internet Explorer works with both MS-DOS and UNIX directory listings. But that being said, Internet Explorer's FTP browsing experience is completely no-frills - it's an ugly "What You Have Is What You See" approach to viewing your FTP site. (In fact, it looks a lot like the built-in IIS directory browsing pages.)



That being said, Internet Explorer works with both MS-DOS and UNIX directory listings, and has no trouble with four-digit years like Firefox.
FTP SSL/TLS:
SSL connections cannot be created by this client.
Virtual Hosts:
You can create a connection to an FTP7 virtual host only if a "Global Listener" FTP site is created that requires authentication, after which you can enter the "sitename|username" syntax to connect to the virtual host. There is no method for entering custom commands, so HOST does not work.
Observations:
A truly great feature of Internet Explorer's FTP browsing experience is the "Open FTP Site in Windows Explorer" integration, where you can switch from the web browser representation to a full Read/Write Windows Explorer view of your site.



One last annoyance is that Internet Explorer cached the directory listings from the FTP server in between sessions just like Firefox. If I completely closed Internet Explorer and reopened it I saw FTP directory listings from my previous session instead of updated directory listings until I forced a refresh. (Something about that just doesn't seem right. :-O)

Google Chrome 0.2.149 (Beta)

Directory Browsing:
In case you thought that Internet Explorer's FTP browsing experience was pretty basic, Google Chrome's browsing experience is even simpler than IE's.



That being said, Google Chrome seemed to work with both MS-DOS and UNIX directory listings, and had no trouble with four-digit years.
FTP SSL/TLS:
SSL connections cannot be created by this client.
Virtual Hosts:
You can create a connection to an FTP7 virtual host only if a "Global Listener" FTP site is created that requires authentication, after which you can enter the "sitename|username" syntax to connect to the virtual host. There is no method for entering custom commands, so HOST does not work.
Observations:
Part of me is inclined to cut Google Chrome a little slack on their display since this is their first browser and it's still only in beta. ;-)

Opera 9.5.2

Directory Browsing:
Much like Firefox, Opera has a rich FTP browsing experience:



The only downside to the directory browsing experience is slow the speed that it took to connect to the server and render the directory listing. I eventually got tired of waiting and stopped testing it before I could play around with the MS-DOS versus UNIX and other directory browsing settings.
FTP SSL/TLS:
SSL connections cannot be created by this client.
Virtual Hosts:
You can create a connection to an FTP7 virtual host only if a "Global Listener" FTP site is created that requires authentication, after which you can enter the "sitename|username" syntax to connect to the virtual host. There is no method for entering custom commands, so HOST does not work.
Observations:
As mentioned in the directory browsing notes earlier, Opera's FTP browsing experience was incredibly slow for me. I didn't make any changes to Opera at first - I simply used the default installation and it would take an inordinate amount of time to connect to the server; often the Opera browser would simply switch to "Not Responding" and I would have to end the task from Task Manager.

Summary

As far as web browsers are concerned, they're not great FTP clients. That being said, here's my thoughts on their respective experiences:

  • Directory Browsing:
    • Best: Firefox 3.0.2, Opera 9.5.2
    • Worst: Google Chrome 0.2.149 (Beta), Internet Explorer 7.0
  • Performance:
    • Best: Firefox 3.0.2, Google Chrome 0.2.149 (Beta), Internet Explorer 7.0
    • Worst: Opera 9.5.2
  • Shell Integration:
    • Only Internet Explorer 7.0 had shell integration, which enables true Read/Write FTP capabilities
  • FTP SSL/TLS:
    • None of the four web browsers that I tested had FTP SSL/TLS capabilities
  • Virtual Hosts:
    • All four web browsers that I tested could use FTP7 virtual hosts

In the next part of this series, I'll start taking a look at some specific FTP clients.

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/
Posted: Sep 24 2008, 22:16 by Bob | Comments (0)
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: FTP
Social Bookmarks: E-mail | Kick it! | DZone it! | del.icio.us