Thursday, April 29, 2010

VBA Problems in Outlook 2010 part 1

I was testing out the new MS Office 2010 Beta, and trying to update some VBA routines that I had written. These routines work just fine in Outlook 2003 and 2007, but did not work "right out of the box" for 2010.

I needed somewhere to post the problems that I ran into, and solutions for them, but could not find anything by googling "VBA Outlook 2010 problems", so I created this blog. I hope these blogs help other people.

By the way, I did send a frown to Microsoft outlining each of these problems, but they limit you to the number of characters you can send, I don't know if they understood and can fix the problems, and it looks like they have already produced the RTM version of Office 2010 by the time I discovered these problems.

One note of caution: when I first experimented with my vba macros in 2010, at some point after playing around with the code, the macros would not run at all. Outlook would not allow me to enable macros - it would display "An error occurred while attempting to verify the VBA project's signature. Macros will be disabled"

After trying everything included creating another cert by using self-cert, I gave up and went to another computer running 2003. But the macro (my VBAProject.OTM file is in my Redirected Folders, and so the same file is used no matter where I am logged in on the network. This will probably be true of most users.)

The file would no longer run in Outlook 2003, and I could no longer edit the file in 2003. Since there was no way to edit it in 2003, and I could not run it to test it in 2010, then this file became useless.

BACKUP YOUR FILE BEFORE YOU START USING Outlook 2010!!!

This happened to me twice, and I don't know the reason. It could be because of errors in the code, or there may be an incompatibility between Outlook versions. But heed my warning.

The solution to this, at least apparently, was to password protect the file!. After I did that, all of the security issues and file corruption disappeared.

On the Developer tab, click Visual Basic> Tools> Project1 Properties> Protection (tab)>Lock project for viewing (checkbox)> enter the password

By the way, it took me a while to figure out how to edit a VBA project in 2010. You first have to enable the Developer tab on the toolbar by
1. Press the Office logo button next to the Home tab and choose Options.
2. Select the section Customize Ribbon.
3. In the right pane, enable the selection field before "Developer".
4. Press OK to close the open dialog.

The first bit of code that gave me problems had to do with trying to get to a Public Folder.
(sorry for the line breaks - I will see if I can fix that in later edits. This is my first post)

Set objFolder = GetFolder("Public Folders\All Public Folders\" & ProjectListBox.Value)
If objFolder Is Nothing Then MsgBox "Folder does not exist"


The ProjectListBox.Value is simply a value taken from a listbox, and it is a list of Public Folders. In 2010, this piece of code always returned nothing, and the Msgbox would pop up.

It turned out that MS has changed the name of the Public Folders in 2010 (at least when running 2010 under Windows 7 when connected to a Windows Server 2008 (with Exchange 2007) domain - it is Small Business Server 2008.

The public folder now is called Public Folders - user email address\All Public Folders\Foldername
So I created a variable called usermail, and can get the current user's email address.

usermail = Application.Session.CurrentUser.AddressEntry.GetExchangeUser.PrimarySmtpAddress
Set objFolder = GetFolder("Public Folders - " & usermail & "\All Public Folders\" & ProjectListBox.Value)
If objFolder Is Nothing Then MsgBox "Folder does not exist"


Of course, you only want this piece of code working in 2010, not in 2003 or 2007, so that involves another piece of code to get the application version:

If Left(Application.Version, 4) = "14.0" Then
blah blah blah

So the original code gets wrapped in an IF STATEMENT
If Left(Application.Version, 4) = "11.0" or Left(Application.Version, 4) = "12.0" Then

That's all for now, but there may be a VBA Problems in Outlook part 2 at a later time.

3 comments:

  1. Thank you so much, the code works great! The only problem now is that I get the box saying that outlook is trying to access email would you allow it for 1 minute etc. Any way to get around that?

    ReplyDelete
  2. You didn't post YOUR code, so it is hard to know what the offending VBA line contains. In general, if you try to access email addresses, then that warning would pop up - I am sure that lots of other actions would produce the warning as well.

    In the past, I have used this freeware:
    http://www.mapilab.com/outlook/security

    but I have not tested it for 2010.

    ReplyDelete
  3. I use this :
    strFolder = appOutlook.Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Parent

    Wich will return the correct public folder. No need to verify Outlook version or concatenate user to folder name.

    ReplyDelete