[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Changing Default Outlook folder using VBA

Posted on 2013-01-03
10
Medium Priority
?
920 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 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