Solved

Changing Default Outlook folder using VBA

Posted on 2013-01-03
10
894 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

803 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