Solved

Automaticly MOVE event from one calendar to another...

Posted on 2010-11-11
32
1,367 Views
Last Modified: 2013-11-05
We are running Outlook 2007 SP2, I have a user that has two different calendars and when certain events come in (Live Meeting Requests) she wants to move the item from one calendar to the other (NOT COPY). Every time I set this up as a rule it will create a new event on the 2nd calendar but it won't automatically remove the event on the other calendar.

Can anyone suggest a way to do this other than manually moving it? This is a training manager so the volume of events we are talking about is too much to manually do it all the time.

Thanks for any help that you can provide!
0
Comment
Question by:CCG3
  • 16
  • 16
32 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, CCG3.

I believe I can do it with a bit of scripting.  If that's an option, then I can post the script and instructions on how to use it.
0
 

Author Comment

by:CCG3
Comment Utility
Thanks for your reply BDF, Yes that is an option if you can post that I would love to try it. Thanks so much for helping me with this!
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
Here's the code for doing this.  Three points to keep in mind about this solution.

1.  Outlook must be running for it to work.
2.  It cannot handle updates.  If a meeting is updated, then it'll appear as a second meeting on the target calendar.
3.  When the meeting is moved Outlook prepends "Copy:" to the subject

Follow these instructions to the code to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
5.  Edit the code as needed.  I included comment lines wherever something needs to or can change
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools > Trust Center
9.  Click Macro Security
10. Set Macro Security to "Warnings for all macros"
11. Click OK
12. Close Outlook
13. Start Outlook.  Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.

One of the edits you have to make is the path to the target calendar.  A folder path in Outlook is essentially the same as a folder path in the file system.  The one difference being that Outlook folder paths do not include a drive letter.  The path to a folder is a list of all the folders from the root to the target folder with each folder name separated from the preceding folder name by a backslash (i.e. \).  Consider the following folder structure:

Mailbox - Doe, John
    - Calendar
    - Inbox
    - Tasks
Personal Folders
    + Marketing
        + Proposals
        + Reviews
    + Projects
        + Project 1
        + Project 2

The path to "Inbox" is "Mailbox - Doe, John\Inbox".
The path to "Reviews" is "Personal Folders\Marketing\Reviews".
The path to "Project 1" is "Personal Folders\Projects\Project 1".
Dim WithEvents olkCalendar As Outlook.Items

Private Sub Application_Quit()
    Set olkCalendar = Nothing
End Sub

Private Sub Application_Startup()
    Set olkCalendar = Session.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    'On the next line change Testing to some text that is unique to these appointments'
    If InStr(Item.Subject, "Testing") Then
        'On the next line enter the path to the target calendar'
        Item.Move OpenOutlookFolder("Mailbox - Doe Jane\My Other Calendar"")
    End If
End Sub

Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Open in new window

0
 

Author Comment

by:CCG3
Comment Utility
Reading through this, will it move everything? Is there a way for it to pick out something from the invite and move it based on that?

If her boss or someone sends her an invite (regular meeting request or Live Meeting request) she wants it to go on her regular calendar. But anytime there is a Live Meeting request from her trainers then this is when we would want this to kick in.

Just trying to make sure that we are on the same page before I put this in place. Of course I will test it on my PC before I go live with it on hers.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
That's what the IF statement on line 13 does.  It ignores items that don't have a certain keyword or phrase in the subject.  It there's nothing unique in the subject that we can key on, then you'll have to identify something about these items that is unique and we'll see if we can use it.
0
 

Author Comment

by:CCG3
Comment Utility
Hey BDF, thanks for sharing that it really works well with just one slight issue...

I put that in place and tweaked what you suggested and got a few successful tests. However, it seems to copy it as soon as it comes in and then it puts another copy on the same folder as soon as I click accept. Is there a way to only do it once?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I think I can force it to automatically accept those items.  Would that work?
0
 

Author Comment

by:CCG3
Comment Utility
Yes I think would work fine. Thank you!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Replace the subroutine olkCalendar_ItemAdd with the one below.  It automatically accepts the appointments as the arrive.
Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    'On the next line change Testing to some text that is unique to these appointments'
    If InStr(Item.Subject, "Testing") Then
        Item.Respond olMeetingAccepted
        'On the next line enter the path to the target calendar'
        Item.Move OpenOutlookFolder("Mailbox - Doe Jane\My Other Calendar")
    End If
End Sub

Open in new window

0
 

Author Comment

by:CCG3
Comment Utility
I tried this but I get an error when it his the Item.Respond line....

Run-tim error '-2147221241(80040107)': The operation failed.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
This was for a newly received meeting request, right?
0
 

Author Comment

by:CCG3
Comment Utility
Yes. I had someone send me a new one. I did have an older request with the same name but that is going to happen if we are conditioning on the subject line. Right?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Are there any other rules which might have interfered with this one?
0
 

Author Comment

by:CCG3
Comment Utility
No. I don't have any rules for requests and none that look for subject or any that look for something from the user that sent that request.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Then I don't see how this can fail.  I tested before posting and it worked.  I've automatically accepted meetings in other scripts and that has always worked.  I don't see any reason why it should fail here.
0
 

Author Comment

by:CCG3
Comment Utility
I am still testing it. If I run it with no breakpoints it fails. If I put a breakpoint in and F8 through it then it seems to work fine. If I then run it with a break point on the line Private Sub olkCalendar_ItemAdd(ByVal Item As Object) and F5 through it instead of F8 then it seems to be looping through this routine twice. Could that be the problem?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hmmm.  That might be it I suppose, although I don't know why I wouldn't have run into the same situation when I tested.  

Try this version.  It checks the item to see if it's already been accepted.  If it hasn't then it takes action.  If it has been accepted, then it does nothing.
Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    'On the next line change Testing to some text that is unique to these appointments'
    If InStr(Item.Subject, "Testing") Then
        If Item.ResponseStatus <> olResponseAccepted Then
            Item.Respond olMeetingAccepted
            'On the next line enter the path to the target calendar'
            Item.Move OpenOutlookFolder("Mailbox - Doe Jane\My Other Calendar")
        End If
    End If
End Sub

Open in new window

0
 

Author Comment

by:CCG3
Comment Utility
Same thing. Could this be a timing issue? Is there a way to pause/wait in the code before the End Sub or right after the Item.Move?
0
 

Author Comment

by:CCG3
Comment Utility
Any suggestions on how to stop this from looping through that routine more than one time?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Apologies.  I could have sworn I'd replied.  I doubt that it's a timing issue.  Yes, we can insert a pause, but I doubt that'll help.  Moving the End Sub would not do anything.  If you want to try the pause, then add the declaration below to the top of ThisOutlookSession in the code window.  After the Item.Move line insert

Sleep 1000

This will cause the code to pause for 1 second.  If you want a longer pause then replace 1000 with the number of seconds times 1,000.


Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Open in new window

0
 

Author Comment

by:CCG3
Comment Utility
When I put that declaration in the top of the code window I get this error when I open and close...

Compile error:
Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules.


If you don't think it is a timing issue then can we drop a variable in there and turn it on/off to make is skip the routine? And the reset it once it finishes?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Try adding the word Private before Declare.
0
 

Author Comment

by:CCG3
Comment Utility
I added the work Public before Declare and I am still getting the same error when I open/close outlook. Are we working with the same Outlook versions (2007)? Do I have something setup incorrectly?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Private not Public.
0
 

Author Comment

by:CCG3
Comment Utility
Yes I had Public instead of Private.

I added that and you are correct, it still does the same thing so the delay didn't fix it. it is crazy. I can F8 through it with no problems but if I F5 it fails.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I can't think of any logical explanation for that behavior.  I don't doubt that's what's happening, I'm just a at a loss to understand how or why.  When you step through the code using F8 does code loop through the same procedure twice?
0
 

Author Comment

by:CCG3
Comment Utility
When I step through (F8), it gets to the end sub and just stops like you would expect it to. But if I F5 through it then it comes right back to that routine and if I press F5 again then I get the error. Could be be because of where I have by second calendar? Maybe once it is moved it prompts the code to run again?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
The code is only watching your default calendar.  The move removes the item from the default and moves it to the secondary calendar.  It's impossible for the code to be triggered by the add to the secondary calendar.  Try this version.  This disables monitoring of the default calendar until the process is complete.
Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    'On the next line change Testing to some text that is unique to these appointments'
    If InStr(Item.Subject, "Testing") Then
        If Item.ResponseStatus <> olResponseAccepted Then
            Set olkCalendar = Nothing
            Item.Respond olMeetingAccepted
            'On the next line enter the path to the target calendar'
            Item.Move OpenOutlookFolder("Mailbox - Doe Jane\My Other Calendar")
            Set olkCalendar = Session.GetDefaultFolder(olFolderCalendar).Items
        End If
    End If
End Sub

Open in new window

0
 

Author Comment

by:CCG3
Comment Utility
Thanks BDF! Now I think we are on to something. I think it was switching the current calendar from my default to the secondary when the item was moved and that was causing the issue. My first tests worked much better with no errors so I will have to do more testing tomorrow or the next day.

Is there a way to use a wild card in the subject statement so it doesn't have to be the exact text but they an append more descriptive data to it? and I am guessing in that If instr statement I can do an or statement right? If so, is this the correct way to do it?

 If InStr(Item.Subject, "Test") OR InStr(Item.Subject, "Test 1") OR InStr(Item.Subject, "Test 2) Then
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Yes, you can string together multiple InStr functions with or's.  
0
 

Author Comment

by:CCG3
Comment Utility
This looks to be working great! Thanks for all of your help BDF!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.  Glad I could help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Viewers will learn the different options available in the Backstage view in Excel 2013.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now