Mass Outlook Calendar Cleanup

May 25, 2013May 18, 2014 Mike Hudson Leave a comment

Over the last few weeks the company that I work for have been migrating from Google Apps over to Microsoft 365 for mail/calendar/contacts and unified messaging etc. This has not been without it’s challenges. Some have which have been fairly bizarre. This week I was approached by a member of our department who had somehow managed to end up with 10,000+ appointments of the same title in his calendar. We set about trying to remove them on mass using the Outlook GUI, but soon realised this would take a very long time. Which is when I put together the following snippet of VBA:

NB: This code ‘takes no prisoners, and straight deletes the appointments matching the criteria you pass it. This wouldn’t be easy to undue if you ran with incorrect parameters. I’d advise adding a break point and testing a few before letting it hit your entire calendar folder.

Dim myNameSpace As NameSpace
Dim myCalendar As Folder
Dim myAppts As Items
Dim myAppt As AppointmentItem
Dim i As Integer

Sub purge_cal()
    Set myNameSpace = Outlook.Application.GetNamespace("MAPI")
    Set myCalendar = myNameSpace.GetDefaultFolder(olFolderCalendar)

    Set myAppts = myCalendar.Items
    i = 0
    For Each myAppt In myAppts
        If myAppt.Subject = "" Then
            i = i + 1
            Debug.Print "Deleting " & myAppt.Subject
            myAppt.Delete

        End If
    Next
        Debug.Print i & " calendar items deleted"
End Sub

Delay Outlook mail sending

February 4, 2012May 18, 2014 Mike Hudson 14 Comments

So today I was approached by an internet friend of mine Richard Tubb, he had a bit of a challenge for me. As a keen Ms Outlook user, he wanted to see if there was a way of delaying all emails that he’d composed and hit send on during the weekend to arrive on the next working day.

Of course, Ms Outlook has the built in deferred delivery time function.. However, setting that manually each time could easily become a bind. So that’s where this little Outlook VBA Snippet steps it.

The code checks to see if today is a weekday upon sending the email, and if it is either a Saturday or Sunday, it then finds the date of the next Monday, and sets the deferred send option to that Monday at 7AM. Of course, should Richard be sending mail through the week, Outlook will simply ignore the code. Simple really 🙂

EDIT: So Richard came back to me with a second request, to delay mail sent on a weekday by 30 minutes, I have now adjusted the code to suit.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error GoTo ErrHand                   'Error Handling
Dim nxtMonday As String                 'Variable containing next Monday's date
Dim objMailItem As MailItem             'Object to hold mail item

Set objMailItem = Item                  'Set object to mail item just sent
    
                
    If Weekday(Date, vbMonday) = 6 Then 'Check to see if todays weekday value is 6 (http://msdn.microsoft.com/en-us/library/82yfs2zh(v=vs.80).aspx)
1:     nxtMonday = Date + (2)           'As today is saturday, Monday is today + 2 days
2:     objMailItem.DeferredDeliveryTime = nxtMonday & " 08:00:00"   'Set delayed delivery time to today + 2 days
    Else
3:     objMailItem.DeferredDeliveryTime = DateAdd("n", 30, Now)     'Else delay is + 30 minutes
    End If

    If Weekday(Date, vbMonday) = 7 Then 'Check to see if todays weekday value is 7
4:     nxtMonday = Date + (1)           'As today is Sunday, Monday is today + 1 day
5:     objMailItem.DeferredDeliveryTime = nxtMonday & " 08:00:00"   'Set delayed delivery time to today + 1 day
    Else
6:     objMailItem.DeferredDeliveryTime = DateAdd("n", 30, Now)     'Else delay is + 30 minutes
    End If

Exit Sub

ErrHand:                                'Due to lack of concentration of the code writing genius there has been a bit of an error,
                                        'Lets tell someone about it by popping up a message box with a detailed description on the screen
    MsgBox ("An error has occured on line " & Erl & ", with a description: " & Err.Description & ", and an error number " & _
        Err.Number)
End Sub

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

On Error GoTo ErrHand 'Error Handling

Dim nxtMonday As String 'Variable containing next Monday's date

Dim objMailItem As MailItem 'Object to hold mail item

Set objMailItem = Item 'Set object to mail item just sent

If Weekday(Date, vbMonday) = 6 Then 'Check to see if todays weekday value is 6 (http://msdn.microsoft.com/en-us/library/82yfs2zh(v=vs.80).aspx)

1: nxtMonday = Date + (2) 'As today is saturday, Monday is today + 2 days

2: objMailItem.DeferredDeliveryTime = nxtMonday & " 08:00:00" 'Set delayed delivery time to today + 2 days

Else

3: objMailItem.DeferredDeliveryTime = DateAdd("n", 30, Now) 'Else delay is + 30 minutes

End If

If Weekday(Date, vbMonday) = 7 Then 'Check to see if todays weekday value is 7

4: nxtMonday = Date + (1) 'As today is Sunday, Monday is today + 1 day

5: objMailItem.DeferredDeliveryTime = nxtMonday & " 08:00:00" 'Set delayed delivery time to today + 1 day

Else

6: objMailItem.DeferredDeliveryTime = DateAdd("n", 30, Now) 'Else delay is + 30 minutes

End If

Exit Sub

ErrHand: 'Due to lack of concentration of the code writing genius there has been a bit of an error,

'Lets tell someone about it by popping up a message box with a detailed description on the screen

MsgBox ("An error has occured on line " & Erl & ", with a description: " & Err.Description & ", and an error number " & _

Err.Number)

End Sub

If you find this code useful, or think you have a better way of doing this, drop me a line in the comments below!

Cheers
Mike

Do you Xobni? The ultimate Outlook add-on

June 26, 2009 Mike Hudson Leave a comment

Many months ago one of my work colleagues from Intrasource introduced me to Xobni (http://www.xobni.com/). If like me you are a heavy emailer, you may find Outlooks search facility clumbersome and slow. That’s where Xobni comes in, Xobni indexes all of your mail, both sent and received and enables an almost instant search facility. Taking the pain out of waiting for search results to be returned.

An overview of Xobni’s features:

Email Search

Before Xobni, searching for emails within Outlook was a slow and unrewarding experience. Now with Xobni’s instant search, the email you’re looking for appears immediately, as you type.

Xobni can build a list of search results for a single keyword 50 times faster than Outlook. Add that up and you will save hours each week.

People Search

One unique element of Xobni is that search results are split into two categories: people and mail. Why do we do this? We believe our users have an easier time remembering names over keywords. When you are looking for an old email message, you can usually remember the name of the person who sent it to you. Type that name into the search box, click on the people result, and you’ll get every conversation you had with that person, as well as all the attachments, contact information, and other information you may need.

Imagine that: a search for ‘Matt’ pulls up not only all the profiles of every Matt you’ve ever exchanged emails with, but also every email where the name Matt is mentioned. All in 0.3 seconds.

Yahoo! Mail Search

Did you know that Xobni is the only way to search your Yahoo Mail outside of Yahoo itself? You get all the benefits of Xobni, but applied to your Yahoo Mail: Xobni’s lightning-fast search, quick attachment discovery, contact information extraction, and integrations with all the major social networks. All you have to do is enable the Yahoo Mail search option in the Xobni options menu.

Contact Info Search

Xobni pulls contact info from email signatures, message bodies, Outlook contacts, as well as our partners: LinkedIn, Facebook, Hoovers, Skype, and Yahoo. If you need to quickly pull up the phone number, company name, and title before your meeting with Jim — simply type ‘Jim’ in the Xobni search box, and his whole profile of information will be at your fingertips.

Company Search

Who are all the people you know at Cisco? Xobni’s search feature also searches the email domains of every person you communicate with. Type ‘cisco.com’ into the xobni search box, and you’ll see everyone you’ve communicated with that has a cisco.com email address. The results are ordered from the person you’ve emailed with most at the top, to the least emailed at the bottom. Click on one of the results and open the Hoover’s extension to see their company’s corporate information.

You can find out more about Xobni and download the application over at http://www.xobni.com/

Monitor a folder for changes in outlook

December 24, 2008May 15, 2014 Mike Hudson Leave a comment

Sometimes, you may want to fire rules or perform functions on the arrival of an item in a folder inside Microsoft Outlook. The built in rules wizard is pretty powerful and useful in these situations, however it doesn’t work with folders outside of your mailbox.

With this example, it is possible to use the ‘WithEvents’ method on a folder outside of your own mailbox.

This even works on Public Folders!

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-type" content="text/html; charset=utf-8">
    <title>Server Error &middot; Gist</title>
    <style type="text/css" media="screen">
      body {
        background: #f1f1f1;
        font-family: "HelveticaNeue-Light", Helvetica, Arial, sans-serif;
        text-rendering: optimizeLegibility;
        -webkit-font-smoothing: antialiased; }

      .container { margin: 100px auto; width: 600px; text-align: center; }

      a { color: #4183c4; text-decoration: none; }
      a:visited { color: #4183c4 }
      a:hover { text-decoration: none; }

      h1 { letter-spacing: -1px; line-height: 60px; font-size: 60px; font-weight: 100; margin: 0px; text-shadow: 0 1px 0 #fff; }
      p { color: rgba(0, 0, 0, 0.5); margin: 20px 0 40px; }

      ul { border-top: 1px solid #ccc; list-style: none; margin: 20px 0; padding: 20px 0; }
      li { display: table-cell; font-weight: bold; width: 1%; }
    </style>
  </head>
  <body>
    <div class="container">
      <h1>OH NOES, 404.</h1>
      <p>We seem to have missed the <em>gist</em> of that <em>gist</em> you were looking for.</p>
      <ul id="error-suggestions">
          <li><a href="https://github.com/contact">Contact Support</a></li>
          <li><a href="https://status.github.com">Status Site</a></li>
          <li><a href="https://twitter.com/github">@github</a></li>
      </ul>
      <p><img src="https://github.s3.amazonaws.com/media/maint-logo.png" alt="GitHub"></p>
    </div>
  </body>
</html>

Outlook VBA: Extract IP address from email header

December 24, 2008May 18, 2014 Mike Hudson Leave a comment

Whilst working in VBA in Microsoft Outlook, I decided to make a function to extract IP addresses from the headers of an email. This is mainly used for reporting spam, but I am sure it could be put to many other good uses!

If you use it, please leave me a comment with details on how you are using it.

Function GetIPAddresses(ByVal MsgHeader As String) As String()
Dim tempArr() As String, i As Long, RegEx As Object, RegC As Object
Set RegEx = CreateObject("vbscript.regexp")
ReDim tempArr(0)
With RegEx
.Global = True
.MultiLine = True
.Pattern = "[?(d{1,3}.d{1,3}.d{1,3}.d{1,3})]?"
End With
If RegEx.Test(MsgHeader) Then
Set RegC = RegEx.Execute(MsgHeader)
ReDim tempArr(RegC.Count - 1)
For i = 0 To RegC.Count - 1
tempArr(i) = RegC.Item(i).SubMatches(0)
Next
End If
Set RegEx = Nothing
Set RegC = Nothing
GetIPAddresses = tempArr
End Function Sample usage:
Dim IPAddrs() As String
IPAddrs = GetIPAddresses(theHeader)
If Len(IPAddrs(0)) &gt; 0 Then
MsgBox Join(IPAddrs, vbCrLf)
End If

Function GetIPAddresses(ByVal MsgHeader As String) As String()

Dim tempArr() As String, i As Long, RegEx As Object, RegC As Object

Set RegEx = CreateObject("vbscript.regexp")

ReDim tempArr(0)

With RegEx

.Global = True

.MultiLine = True

.Pattern = "[?(d{1,3}.d{1,3}.d{1,3}.d{1,3})]?"

End With

If RegEx.Test(MsgHeader) Then

Set RegC = RegEx.Execute(MsgHeader)

ReDim tempArr(RegC.Count - 1)

For i = 0 To RegC.Count - 1

tempArr(i) = RegC.Item(i).SubMatches(0)

End If

Set RegEx = Nothing

Set RegC = Nothing

GetIPAddresses = tempArr

End Function Sample usage:

Dim IPAddrs() As String

IPAddrs = GetIPAddresses(theHeader)

If Len(IPAddrs(0)) > 0 Then

MsgBox Join(IPAddrs, vbCrLf)

End If

Welcome to MikeSel.info

Operating System How To's

Programming How To's

CMS How To's

Security How To’s

Home Automation

Outlook

Mass Outlook Calendar Cleanup

Delay Outlook mail sending

Do you Xobni? The ultimate Outlook add-on

Email Search

People Search

Yahoo! Mail Search

Contact Info Search

Company Search

Monitor a folder for changes in outlook

Outlook VBA: Extract IP address from email header