Solved

Changing Default Outlook folder using VBA

Posted on 2013-01-03
10
902 Views
Last Modified: 2013-01-04
Hello Experts,

I have the following function which is adding an Outlook calendar appointment event from Microsoft Access using late binding. It is working just fine but I would like to change the location to a shared calendar folder called "\\Calendar\Shared".

Could someone show me how that would be accomplished?

'~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub ScheduleInOutlookCalendar(lngSONum As Long, strCustName As String, datApptDate As Date, datApptTime, datPromDate As Date, _
     datPromTime As Date, lngEstTime As Long, strYearMakeModel As String, strTech As String, strPriority As String, strMsg As String, _
     strContactNumbers As String, strParts As String, strLabour As String, dblTotHrs As Double, dblActHrs As Double)

    Dim olApp As Object
    Dim olNs As Object
    Dim olApt As Object
    Dim olBusy As Object
    Dim olTentative As Object
   
    Dim strEstTime As String
    Dim strApptTime As String
    Dim lngRemTime As Long
   
   
    'Convert the estimated time in minutes to the correct format
    strEstTime = Format(Int(([lngEstTime]) / 60), "00") & ":" & Format(([lngEstTime] Mod 60), "00") & ":00"
    strApptTime = Format(datApptTime, "HH:MM:SS")

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")

    If Err.Number = 429 Then
        Set olApp = CreateObject("Outlook.application")
    End If

    On Error GoTo 0

    Set olNs = olApp.GetNamespace("MAPI")

    If olApp.ActiveExplorer Is Nothing Then
        olApp.Explorers.Add _
            (olNs.GetDefaultFolder(9), 0).Activate
    Else
        Set olApp.ActiveExplorer.CurrentFolder = _
            olNs.GetDefaultFolder(9)
        olApp.ActiveExplorer.Display
    End If
   
    'Dim olApt As Outlook.AppointmentItem
    Set olApt = olApp.CreateItem(1)

    With olApt
        .start = datApptDate + TimeValue(strApptTime)
        .End = .start + TimeValue(strEstTime)
        .Subject = "SO: " & lngSONum & ";  Customer: " & strCustName & ";  Vehicle: " & strYearMakeModel & ""
        .Location = "Tech: " & strTech
        'Mod_D0102_2013(3) - added Actual Hours
        'Mod_D1231_2012(8) - added more SO details
        .Body = "Customer: " & strCustName & vbCrLf & _
                "Vehicle: " & strYearMakeModel & vbCrLf & _
                "Priority: " & strPriority & vbCrLf & _
                "Estimated Hours: " & dblTotHrs & vbCrLf & _
                "Actual Hours: " & dblActHrs & vbCrLf & _
                "Assigned Technician: " & strTech & vbCrLf & vbCrLf & _
                "Contact Numbers: " & strContactNumbers & vbCrLf & _
                "Promised Date: " & datPromDate & vbCrLf & _
                "Promised Time: " & datPromTime & vbCrLf & vbCrLf & _
                "Parts Information: " & strParts & vbCrLf & vbCrLf & _
                "Labour Information: " & strLabour & vbCrLf & vbCrLf & _
                "Notes: " & strMsg
        'Mod_D1231_2012(9) - new
        If strPriority = "High" Then
            .importance = 2
        Else
            .importance = 0
        End If
        '.ReminderMinutesBeforeStart = 120
        '.MeetingStatus = 0
        .BusyStatus = 0
        '.ReminderSet = True
        .Save
    End With
    Set olApt = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
0
Comment
Question by:DonGarry
[X]
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
  • 5
  • 5
10 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38742947
Try
olns.folders("Calendar").folders("Shared") or
olNs.GetDefaultFolder(9).folders("Shared")

Chris
0
 

Author Comment

by:DonGarry
ID: 38744110
Hi Chris,

I tried both of your suggestions and it still loads into the default calendar...

Any other suggestions?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38744132
You can use .move within the 'With olApt' construct i.e.

.move olNs.GetDefaultFolder(9).folders("Shared")

Chris
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:DonGarry
ID: 38744380
Thanks Chris,

The .move you suggested moves the item into the 'shared' folder successfully.

If the appointment is in the 'default' folder, I can access it programmatically to see if the start time has changed or if it has been deleted so then am able to synchronize back to our database.

When we move it to the 'shared' folder is there a way of getting access to it?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38744721
It is as accessible in the 'shared' folder as it is anywhere else, you just need to address the correct folder.

Chris
0
 

Author Comment

by:DonGarry
ID: 38744776
Thanks Chris,

I guess I'm back to my fundamental problem of not knowing how to gain access to the 'Shared' Calendar programmatically even thougth your
.move olNs.GetDefaultFolder(9).folders("Shared") suggestion worked just fine.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38744924
What is it you want to do with the folder since as I say

olNs.GetDefaultFolder(9).folders("Shared")
accesses the folder in question just as
olNs.GetDefaultFolder(9)
accesses the default calendar.

Chris
0
 

Author Comment

by:DonGarry
ID: 38745005
Hi Chris,

Thank you for your patience!!

OK, I'm able to open up the shared calendar by using olNs.GetDefaultFolder(9).folders("Shared")

When I used Set olApt = olApp.CreateItem(1) it still adds an appointment to the default calendar...is there a way I can add the appointment directly into the 'Shared' Calendar?

Thx,
Don
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 38745213
createitem always creates in the default folder however you can create in sub folders via the add method for example:

Set olApt = olApp.Session.GetDefaultFolder(9).Folders("Shared").Items.Add

Chris
0
 

Author Closing Comment

by:DonGarry
ID: 38745525
Hi Chris,

Excellent!

Thank you for taking the time to assist me!

Cheers,
Don
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

707 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