Solved

Changing Default Outlook folder using VBA

Posted on 2013-01-03
10
888 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
Comment Utility
Try
olns.folders("Calendar").folders("Shared") or
olNs.GetDefaultFolder(9).folders("Shared")

Chris
0
 

Author Comment

by:DonGarry
Comment Utility
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
Comment Utility
You can use .move within the 'With olApt' construct i.e.

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

Chris
0
 

Author Comment

by:DonGarry
Comment Utility
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
Comment Utility
It is as accessible in the 'shared' folder as it is anywhere else, you just need to address the correct folder.

Chris
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:DonGarry
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Chris,

Excellent!

Thank you for taking the time to assist me!

Cheers,
Don
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

17 Experts available now in Live!

Get 1:1 Help Now