Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Automaticly MOVE event from one calendar to another...

Posted on 2010-11-11
32
1,371 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
ID: 34119603
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
ID: 34120069
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
ID: 34121658
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:CCG3
ID: 34121919
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
ID: 34122092
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
ID: 34123745
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
ID: 34123766
I think I can force it to automatically accept those items.  Would that work?
0
 

Author Comment

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

Expert Comment

by:David Lee
ID: 34133092
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
ID: 34146285
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
ID: 34147675
This was for a newly received meeting request, right?
0
 

Author Comment

by:CCG3
ID: 34149863
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
ID: 34153572
Are there any other rules which might have interfered with this one?
0
 

Author Comment

by:CCG3
ID: 34154429
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
ID: 34154457
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
ID: 34154823
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
 
LVL 76

Expert Comment

by:David Lee
ID: 34155073
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
ID: 34155612
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
ID: 34187811
Any suggestions on how to stop this from looping through that routine more than one time?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34188658
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
ID: 34189186
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
ID: 34189363
Try adding the word Private before Declare.
0
 

Author Comment

by:CCG3
ID: 34189582
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
ID: 34189732
Private not Public.
0
 

Author Comment

by:CCG3
ID: 34189896
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
ID: 34190566
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
ID: 34190702
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
ID: 34191891
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
ID: 34192195
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
ID: 34192229
Yes, you can string together multiple InStr functions with or's.  
0
 

Author Comment

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

Expert Comment

by:David Lee
ID: 34218541
You're welcome.  Glad I could help.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

860 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