Solved

Running VBS script unattended with no Outlook open.

Posted on 2011-03-14
4
764 Views
Last Modified: 2012-05-11
Hi.

I've just built a small util for sorting and filing reports received as emails in our public folders.

The util was initially an Outlook macro.

With help (See Having an Outlook 2003 SP3 macro run every day), I've now got this as a VBS file which I can run via Windows Scheduler.

But I need to have Outlook open.

With it closed, I get an error ...

SortRecharges.vbs(23, 5) Microsoft Office Outlook: The messaging interface has returned an unknown error. If the problem persists, restart Outlook.

Open in new window


My VBS code is ...

SortRecharges
WScript.Quit

Sub SortRecharges()
    Dim _
        o_App, _
        o_RechargeFolder, _
        o_ContractFolder, _
        o_YearFolder, _
        o_MonthFolder, _
        o_DayFolder, _
        o_Item
    Dim _
        b_YMDFolder, _
        dt_Received, _
        i_Item, _
        i_Items, _
        s_RechargeFolderID

    ' Public folder Unique ID - determine using code 
    s_RechargeFolderID = "000000001A447390AA6611CD9BC800AA002FC45A030085FB38B65F840A438204B33E62CC2FC4000000169FB80000"

    Set o_App = CreateObject("Outlook.Application")
    Set o_RechargeFolder = o_App.GetNamespace("Mapi").GetFolderFromID(s_RechargeFolderID)

    ' Verify Recharge Folder.
    If o_RechargeFolder.Name <> "Recharge Reports" Then
        m_Failure = MsgBox("Folder is not the Recharge Reports folder." & Chr(13) & Chr(13) & "Selected folder is : " & o_RechargeFolder.Name, vbExclamation + vbOKOnly, "ERROR : Invalid folder selected")
        Exit Sub
    End If

    ' Iterate the current contracts
    For Each o_ContractFolder In o_RechargeFolder.Folders
        ' We ignore "Retired"
        If "Retired" <> o_ContractFolder.Name Then

            ' Determine if we need to look at Year\Month\Day or Year only.
            If "ASDA" = o_ContractFolder.Name Or "Hartshorne" = o_ContractFolder.Name Or "Hill Hire" = o_ContractFolder.Name Then
                b_YMDFolder = True
            Else
                b_YMDFolder = False
            End If

            ' Debug.Print "Examining " & o_ContractFolder.Name
            ' Wscript.StdOut.WriteLine("Examining " & o_ContractFolder.Name)

            ' Iterate the items.
            i_Items = o_ContractFolder.Items.Count
            i_Item = 0
            ' NOTE : Use a reverse for loop rather than for each as the pointer is updated to the next item immediately after the move and then Next skips an item.
            For i_Item = i_Items To 1 Step of - 1

                Set o_Item = o_ContractFolder.Items(i_Item)

                ' Debug.Print "Processing item #" & i_Item & " of " & i_Items & " reports : " & o_Item.Subject
                ' Wscript.StdOut.WriteLine("Processing item #" & i_Item & " of " & i_Items & " reports : " & o_Item.Subject) 

                ' Mark as read
                If True = o_Item.UnRead Then
                    o_Item.UnRead = False
                End If

                ' File the reports generated today in yesterday's folder.
                dt_Received = o_Item.ReceivedTime - 1

                ' Find the appropriate destination file
                If b_YMDFolder = True Then
                    Set o_YearFolder = CheckFolder(o_ContractFolder, CStr(Year(dt_Received)))
                    Set o_MonthFolder = CheckFolder(o_YearFolder, PadDigits(Month(dt_Received), 2))
                    Set o_DayFolder = CheckFolder(o_MonthFolder, PadDigits(Day(dt_Received), 2))
                Else
                    Set o_DayFolder = CheckFolder(o_ContractFolder, CStr(Year(dt_Received)))
                End If

                o_Item.Move o_DayFolder

            Next

        End If

    Next

End Sub

Function CheckFolder(o_Folder, s_SubFolder)
    Dim o_SubFolder

    Set CheckFolder = Nothing
    On Error Resume Next
    Set CheckFolder = o_Folder.Folders.Add(s_SubFolder)
    On Error GoTo 0

    If Not CheckFolder Is Nothing Then
    Else
        Set CheckFolder = o_Folder.Folders(s_SubFolder)
    End If
End Function

' As the VBS environment has no Format() function, this code will provide the same effect as Format(i_Value, "00")
Function PadDigits(i_NumberToPad, i_TotalDigits) 
    PadDigits = Right(String(i_TotalDigits, "0") & i_NumberToPad, i_TotalDigits) 
End Function

Open in new window

.

What do I need to add to this to get Outlook to open and close and to automatically select the correct mail profile (I've done some reading, but got nowhere with this actually working - which goes to show I'm missing something obvious).

Regards,

Richard Quadling.
0
Comment
Question by:Richard Quadling
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 35127370
To start it up try :

set sh = createobject("wscript.shell")
shell.run "outlook.exe"

your code

o_App.quit

Chris
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 35137293
Hi Chris,

I've decided to keep the code within Outlook VBA. As it is my account that has to receive and file the mail using user level rules, it seemed pointless in trying to get all of that unattended. It is just a key press in the morning when I have Outlook open now.

Thank you for your efforts.

Richard.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35146451
Richard

I registered a request for attention since according to my past experience on the site you should not be able to close the question accepting your own post without it being registered for my consideration.

In this case I would have objected as indeed the post I made answers the original request and in my view is a full and correct answer.  Closing the question with a change in policy that bypasses the need for the question does not invalidate the accuracy of my prior input and therefore I believe my post is a valid answer and deserves appropriate recognition of the fact.

Chris
0
 
LVL 40

Author Closing Comment

by:Richard Quadling
ID: 35147977
Excellent answer and sorry for the premature closing. I had initially abandoned the question as the solution won't be used, but it does work perfectly.
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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

772 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