Highlight And Move Multiple Emails

Okay so I said I would be writing some VBA code to export contacts and emails from Outlook to Excel, well, I lied, sort of. That code is still coming, but before I post it, here is a routine that lets you run VBA code on multiple messages at the same time. Most of my routines so far have been written to run on one email at a time:

Resend This Message
Save Incoming Attachments
Read those image files in Outlook

and so on.

This macro is perfect for manual execution of a routine on multiple emails. In the example below, I select a few emails from my Inbox and then move them to another folder. You could easily adapt this macro to forward all the selected messages, save attachments from all of them, etc.

Sub MoveToFolder()

Dim olMyFldr As Outlook.MAPIFolder
Dim MsgColl As Object
Dim Msg As Outlook.MailItem
Dim objNS As Outlook.NameSpace
Dim i As Long

' check if we have multiple items selected
On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
    Case "Explorer"
        ' a collection of selected items
        Set MsgColl = ActiveExplorer.Selection
    Case "Inspector"
        ' only one item was selected
        Set Msg = ActiveInspector.CurrentItem
End Select
On Error GoTo 0

If (MsgColl Is Nothing) And (Msg Is Nothing) Then
    GoTo ExitProc
End If

Set objNS = Outlook.GetNamespace("MAPI")
Set olMyFldr = objNS.GetDefaultFolder(olFolderInbox).Folders("Completed")

' now we can act on the msg collection,
' or on the individual msg we selected

If Not MsgColl Is Nothing Then
' we selected multiple items
    For i = 1 To MsgColl.Count
        ' set an obj reference to each mail item so we can move it
        Set Msg = MsgColl.Item(i)
            With Msg
                .UnRead = False
                .Move olMyFldr
            End With
    Next i
ElseIf Not Msg Is Nothing Then
    With Msg
        .UnRead = False
        .Move olMyFldr
    End With
End If

ExitProc:
Set Msg = Nothing
Set MsgColl = Nothing
Set olMyFldr = Nothing
Set objNS = Nothing
End Sub

I highlighted in red the sections you would replace with your own code. The first
part (right after "Set Msg = MsgColl.Item(i)") is the one that loops through each
selected message and runs your code. So you can move, forward, copy, export, reply
with attachments, etc, each mail item you selected. The second one runs only if one
mail item is selected.

In both cases, the object variable "Msg" is a reference to the current mail item.
That's what you will be manipulating.

If you're saving attachments, for extra credit try adding the SelectFolder() Function
from the Save Incoming Attachments post.

Enjoy,
JP

---

Open Any Email Attachment From Outlook

I was inspired to write some VBA code that lets you open any attachment in its native application by a recent newsgroup post from Sue Mosher. The truth is I was bored, and it was easy to code because I simply reused code I had just written for the Save Incoming Attachments post. Hopefully the value of a stock code library will now be apparent to you.

This code uses the Windows Script Host Object Model to let Windows choose what program to use to open an attachment. Actually, it uses whatever file associations that were created when the program was installed. For example, .doc files usually open in Microsoft Word (if you have it installed).

So instead of instantiating the object model for every possible attachment type (impossible), this method opens any attachment (as long as Windows knows what program to use). I haven't tested this with unknown attachments, so if anyone would like to try and let me know what happens (hopefully the file association dialog box appears), I'd be glad to hear it.

Sub OpenAttachmentInNativeApp()

' based on code posted by Sue Mosher
' http://tinyurl.com/684zg4
Dim myShell As Object
Dim MyItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim i As Long
Dim Att As String

On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
    Case "Explorer"
        Set MyItem = ActiveExplorer.Selection.Item(1)
    Case "Inspector"
        Set MyItem = ActiveInspector.CurrentItem
    Case Else
End Select
On Error GoTo 0
    
If MyItem Is Nothing Then
    GoTo ExitProc
End If

Set myAttachments = MyItem.Attachments

If myAttachments.Count > 0 Then
    For i = 1 To myAttachments.Count
        Att = myAttachments.Item(i).DisplayName
        
        ' delete just in case it exists from before
        On Error Resume Next
        Kill "C:\" & Att
        On Error GoTo 0
        
        myAttachments.Item(i).SaveAsFile "C:\" & Att
    Next i
End If

' Windows Script Host Object
Set myShell = CreateObject("WScript.Shell")
myShell.Run "C:\" & Att

ExitProc:
Set myAttachments = Nothing
Set MyItem = Nothing
Set myShell = Nothing

End Sub

And here's a link to the newsgroup thread:

Open an attachment in a new window using its native application

Enjoy,
JP

---

XML Parsing Function

In keeping with my Website XML parsing post, here is a short function that returns the body text from a webpage as a string, on which you can use other functions like Instr, Left$, Mid$, etc, to extract necessary data.

Function URLText(sURL As String) As String

' check website.com using xml
' early bound
Dim xSite As XMLHTTP60

Set xSite = New XMLHTTP60
xSite.Open "GET", sURL, False
xSite.Send

Do Until xSite.readyState = 4
Loop

URLText = xSite.responseText

End Function

And it's as simple as that. You could even use it as a UDF. Here's a sample sub:

Sub CheckMySite()
Dim MyString As String
Dim i As Long

MyString = URLText("http://www.google.com")

If Instr(MyString, "Hello!") > 0 Then
    Cells(5,1).Value = Mid$(MyString, 1, 5)
End If

End Sub

This sub checks if the returned string contains the word "Hello" and if so, pulls the first five characters of the text and puts it in cell A5. It's just an arbitrary routine to show you what you can do with the XML response.

For my next trick, I will be showing you a routine that takes a snapshot of your Outlook inbox and writes it to a spreadsheet. I'm also planning on demonstrating some code that exports Contacts from Outlook to Excel.

Enjoy,
JP

---

Save Incoming Attachments, Choose Your Folder

Here's a sub and a function for Outlook that let's you save attachments from a selected or open email. It's similar to the File>Save Attachments menu option, which lets you save all of the attachments from a particular email, or the FindControl Method, which can be used to find and execute "Save Attachments".

Now I realize that there is already a ton of code available that you can use to save attachments, but I found some code that uses the Shell application to let you pick the folder you want to use as the destination. So my purpose here is to demonstrate how to use this code in a simple routine.

It's a great technique and, since it is a separate function, it is very portable to other subs where you want to let the user pick a folder on their hard drive or network drive as the destination for files you want to save or use in a routine. It simply returns the full path as a string.

As you can see below, the String variable "SelectedFolder" is used to store the returned results from the SelectFolder() Function.

As usual, we set an object reference to a Mail Item, then loop through the attachments collection of that mail item and save each one. Notice that the bulk of the work is done inside the If statement (but outside the loop) to avoid costly object references that might be useless if the email doesn't have attachments (for example, if you run this code on the wrong email by mistake).

Sub GoThroughAttachments()
Dim MyItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim i As Long
Dim Att As String
Dim SelectedFolder As String

On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
    Case "Explorer"
        Set MyItem = ActiveExplorer.Selection.Item(1)
    Case "Inspector"
        Set MyItem = ActiveInspector.CurrentItem
    Case Else
End Select
On Error GoTo 0
    
If MyItem Is Nothing Then
    GoTo ExitProc
End If

If MyItem.Attachments.Count > 0 Then
    SelectedFolder = SelectFolder()

    If SelectedFolder <> "" Then     ' user didn't press Cancel

    Set myAttachments = MyItem.Attachments

    For i = 1 To myAttachments.Count
        Att = myAttachments.Item(i).DisplayName
        myAttachments.Item(i).SaveAsFile SelectedFolder & "\" & Att
    Next i
    End If
End If

ExitProc:
Set myAttachments = Nothing
Set MyItem = Nothing
End Sub

---

Private Function SelectFolder(Optional i_RootFolder As String) As String
' from http://vba-corner.livejournal.com/
Dim myShell As Object
Dim MyFolder As Object

  Set myShell = CreateObject("Shell.Application")
  If i_RootFolder = "" Then
    'no root folder given, use default (which is Desktop)
    Set MyFolder = myShell.BrowseForFolder(0, "Please select a folder:", 1)
  ElseIf Not (i_RootFolder Like "*[!0123456789]*") Then
    'number for special folder given
    Set MyFolder = myShell.BrowseForFolder(0, _
                   "Please select a folder:", 1, CInt(i_RootFolder))
  Else
    'path for root folder given
    Set MyFolder = myShell.BrowseForFolder(0, _
                   "Please select a folder:", 1, CStr(i_RootFolder))
  End If
  If Not MyFolder Is Nothing Then
    SelectFolder = MyFolder.self.Path
  End If
End Function

Notice that the SelectFolder() Function does not include the trailing slash at the end, so we need to add this when saving the file.

If you wanted to simply use the same folder every time (for example, C:\MyFiles\), just comment out this line:

SelectedFolder = SelectFolder()

Remove the inner If-End-If statement (If SelectedFolder <> "" Then) and change this line:

myAttachments.Item(i).SaveAsFile SelectedFolder & "\" & Att

to this:

myAttachments.Item(i).SaveAsFile "C:\MyFiles\" & Att

But why would you want to do a thing like that, when the Shell automation is so much cooler?

Click here to see how to add this code to a toolbar button in Outlook.


Enjoy,
JP

---

Read those image files in Outlook

I recently discovered a new object library that seems pretty useful - the Microsoft Office Document Imaging Type Library. From the MSDN site:

The Microsoft® Office Document Imaging 2003 (MODI) object model makes it possible to develop custom applications for managing document images (such as scanned and faxed documents) and the recognizable text that they contain.

(end quote)

This object model is not available in Office 2002(XP).

Continuing with the Outlook theme from my latest blog posts, I can already see the potential for automating Outlook by handling incoming emails, opening image file attachments, then filing, saving or forwarding them depending on the image content, or using Excel to browse through a folder and doing the same thing. I wrote a short sub that can show you how to read an image file that is sent to you via Outlook attachment.


First you must set a reference to the Microsoft Office Document Imaging 11.0 Type Library. Here's mine on the left. And below is the code.

Sub ReadAttachment()

Dim MyItem As Outlook.MailItem
Dim MyAttach As Outlook.Attachments
Dim MyDoc As MODI.Document
Dim MyLayout As MODI.Layout
Dim Att As String
Dim strWords As String
Dim int As Long

    On Error Resume Next
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set MyItem = ActiveExplorer.Selection.Item(1)
        Case "Inspector"
            Set MyItem = ActiveInspector.CurrentItem
        Case Else
    End Select
    On Error GoTo 0
    
    If MyItem Is Nothing Then
        GoTo ExitProc
    End If

This bit of code should be familiar, it is the stock code I always use to get a reference
to an item in a mail folder. Of course, the code assumes that you are acting on a mail item; to make sure, change "Dim MyItem As Outlook.MailItem" to
"Dim MyItem As Object" and then wrap the rest of the code below in a
"If MyItem.Class = olMail" statement. Or, just make sure you select or open a mail item before running this code.

Set MyAttach = MyItem.Attachments

If MyAttach.Count > 0 Then

    Att = "C:\" & MyAttach.Item(1).DisplayName
    MyAttach.Item(1).SaveAsFile Att
    
    Set MyDoc = New MODI.Document
    
    MyDoc.create (Att)

    MyDoc.images(0).OCR
    
    Set MyLayout = MyDoc.images(0).Layout
    
    For int = 0 To (MyLayout.NumWords - 1)
        strWords = strWords & " " & MyLayout.Words(int).Text
    Next int
    
    MsgBox strWords
    
Else
    GoTo ExitProc
End If


ExitProc:
Set MyItem = Nothing
Set MyAttach = Nothing
Set MyDoc = Nothing
Set MyLayout = Nothing

On Error Resume Next
    Kill Att
On Error GoTo 0

End Sub

The rest of the code simply saves the attachment to a file on the hard drive, creates a new image document, then builds a string consisting of the readable characters in the image file. That string could be parsed to see if it contains something you are looking for, for example

If Instr(strWords, "My String") > 0 Then
' your code here
End If

Enjoy,
JP

---

Outlook Automated File Request System

Here is the finished code for the Outlook File Request System.

File Server Code

This is event code which acts as an automated file server, putting files into the specified folder, and sending files back to the requestor, all via email. Once the code is installed, another user requests a file from you by using the following syntax in the Subject line:

Subject: FILEGET drive:path\filename

Where 'drive' is is a valid drive letter, 'path' is the folder and 'filename' is the name of the file requested.

For example,

Subject: FILEGET E:\Files\MyFile.doc

To send a file to another user's folder, subject should be:

Subject: FILEPUT D:\

And there should be at least one attachment to the email. All attachments will be saved to the same folder.

Note that for security reasons, I limited access to the C: drive, however if you set up a folder called 'Shared' on your desktop (Windows 2000/XP), you can place files there that you want to share, and others can request files from there. Of course if this isn't a concern for you, you can simply remove those restrictions.

Due to object model restrictions, I decided to remove the feature that replies to the group (if applicable). For example, if you CC: five other people on your request, the code simply ignores the other recipients. Originally, I had it set up to reply to them all. However, accessing the Recipients collection of the mail item triggered the object model guard, which was contrary to the intent of the code which is to fully automate the request process. You'd have to sit there clicking 'OK' every time someone sent you a request!

All of this is done via Outlook automation. Due to the blogging software I use (which I'll probably change soon) I cannot post the full code here. I will be demonstrating some of the techniques used to produce this code; click the link above to get the entire thing in the proper format.

First, we initialize the event code using the following code which should be familiar to you by now, if you have browsed the Outlook page. This is stock code which should be part of your code library to be reused over and over.

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()

Dim objNS As Outlook.NameSpace
  Set objNS = GetNamespace("MAPI")
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub

All of this code should be placed in ThisOutlookSession module in your Outlook VBE. Just press Alt-F11, double-click on ThisOutlookSession and paste in the above code. Remember, after you install (or edit) the code in this module, you have to restart Outlook for any changes to take effect. Also, any existing code you have seems to stop working until you save and restart Outlook.

Here is the ItemAdd event code that is checking the Inbox for new mail items. We first set an object reference to the mail item that is passed as an argument. Then we determine the full path of the user's desktop folder, to figure out where the 'Shared' folder should sit. Keep in mind this will only work pre-Windows Vista. Then we check the subject line to see if it fits what we are looking for.

Private Sub Items_ItemAdd(ByVal item As Object)

If TypeName(item) = "MailItem" Then
    Dim ToDo As String
    Dim WhatAndWhere As String
    Dim Msg As Outlook.MailItem
    Dim MsgAttach As Outlook.Attachments
    Dim MsgReply As Outlook.MailItem
    Dim SlashSign As Long
    Dim sPath As String
    Dim sFile As String
    Dim fso As Object
    Dim UserN As String
    Dim DeskTopSharedFolder As String
    Dim strHelpText As String

' the mailitem is passed to the event code as an argument
Set Msg = item

' get current username so we can figure out the desktop folder name
UserN = Environ("username")
DeskTopSharedFolder = "C:\Documents And Settings\" & _
UserN & "\Desktop\Shared\"

On Error Resume Next
ToDo = Left$(Msg.Subject, 7)
WhatAndWhere = Right$(Msg.Subject, Len(Msg.Subject) - 8)
On Error GoTo 0

At this point we either have a FILEGET request or a FILEPUT request (or maybe neither). Then we cycle through every possible error condition (no attachments, malformed subject) and, if no errors occurred, call the sending sub which sends back the file requested (or saves the attachments to the specified folder).

In the interest of modular programming, there are actually four separate subs which each do something different:

1) The File Server Sub, acting as an intermediary sub which takes the validated user-input and passes it to the sending sub.

2) The Sending Sub, which takes four arguments (two optional) and is responsible for sending out all emails.

3) The Logging Sub, which (if you allow it) logs all file requests and sends to a CSV file, and

4) A String Checking Sub which checks if one string is found within another and returns a Boolean value (True or False).

Check out the full code here.

File Server Code

Enjoy,
JP

---

Free VB/C#/C++ Software

In keeping with the theme from my last post, I started looking for tutorials or software for other programming languages that might make an eventual transition to VSTO easier. I found some useful software from Microsoft that is actually free.

Check out:
http://www.microsoft.com/express/download/

You can download "lite" versions of VB 2008, C# 2008, C++ 2008, SQL Server 2005, as well as something called Visual Web Developer 2008.

The one that looks most interesting to me is SQL Server, which I installed but have yet to try out. My plan is to use it to further understand the syntax and commands of SQL. I also installed VB Express, and sooner or later I'll get around to trying it.

One of the blurbs that actually made me laugh said "Control your home lights using Visual Basic Express". I just use the Off switch.

Unfortunately you need to install their Silverlight technology to properly view the page. Whatever happened to plain old HTML?

They also offer a DVD image you can download and burn. Seems convenient, but why anyone would want to do this, I don't know.

So check it out and let me know if you download or try any of them out.

Enjoy,
JP

---

VBA Rant #1

I'm sure by now you are aware that Microsoft has been distancing itself from VBA.

As of July 1, 2007, they are no longer licensing VBA (to new clients). For example, if you wrote a program today and wanted to integrate VBA in it, you are out of luck.

And the new version of Office for Mac will not support VBA at all.

They seem to be pushing their other technologies like VSTO for automating Office programs.

I feel I have to weigh in on this subject because it seems like Microsoft used VBA to lock users into using their software, and is now abandoning them.

Now I realize VBA will still be around for many years. Hell, XLM macros are still working in Excel even after VBA was introduced 13 years ago to replace it. I still see newsgroup posts from people asking for help with macros in Excel 97 (11 years old!). But how long will Microsoft maintain backward compatibility with a language it is discouraging people from using?

I have browsed other languages like .NET, C++, and realize just how familiar VBA is. Maybe it's "Grumpy Old Man Syndrome" setting in early; I can already hear myself saying things like "Back in my day, you could write a macro in 5 minutes to list all the files in a folder!"

This is going to mean that, in general, code will start getting harder to find. VBA is the People's Code; simple to integrate and implement by almost anyone with a little experience. In contrast, you would almost have to be a professional developer to get code working in another language to interact with Office.

"Visual Studio Tools for Applications makes it easier to write reliable, robust, and secure customizations by providing managed extensibility for both COM and .NET applications." [From VSTO Site]

It reminds me of the Mission Statement Generator from Dilbert's website.

Will VBA eventually be deprecated? Will people still be coding in VBA 20 years from now? Or am I just completely off it and worrying about nothing? I will accept any suggestions for what programming language I should start learning.


--JP

---

Excel training class and Outlook file request system updates

I've been mentioning the Excel training class for some time now, finally the curriculum is coming together and I finally got off my lazy butt and have almost completed the handouts and sample worksheets. I am hoping with fingers crossed to have this finally completed by the end of next week. Then I have to start taking public speaking classes to learn how to present this material in a way that won't put people to sleep. For practice, try explaining to someone an INDEX/MATCH array in a way that makes it sound fun!

In other news, the file request code I have been working on for Outlook is coming along quite nicely. It consists of three subs in a standard module, and the event code which would be placed in ThisOutlookSession. It's designed for business users (AKA "office drones like me") with network shares, who have better things to do than act as a human file server pushing files around all day. Let your lazy co-workers do it themselves! It would make you a hero while at the same time give you less work to do! Here are some of the features:

Request from, or submit files to, another user's computer or network share

C: drive is blocked, but you can create a folder on your desktop called "Shared" and place files in there that you want others to have access to (or place files in)

Friendly error messages in case of malformed requests (i.e. invalid path, no attachments).

Requestor can CC: others, and the code will send the requested file (along with confirmation email), to each of the recipients of the original message. However, ugly error messages are sent back to the original requestor only.

It also includes a built-in Help system, via the event code where users get an email back with instructions on how to request or send files to a folder. The instructions are also included at the bottom of every confirmation email.

All file sends and requests are logged to a .CSV file, the location and filename of which you can change as needed. The filename is passed as an argument to the function, so for example, you could update the code to write to different log files depending on whether someone was sending a file, or requesting a file. However the code as it stands will distinguish between the two when logging the request. Errors are also logged.

It's almost like a Kazaa or Limewire type setup where you have a shared folder and you trade files with other users, only in this case it's via email. And (hopefully, in your case) it's legal.


Enjoy,
JP

---