Solved

Running VBS script unattended with no Outlook open.

Posted on 2011-03-14
4
759 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:RQuadling
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
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…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

728 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

9 Experts available now in Live!

Get 1:1 Help Now