Link to home
Start Free TrialLog in
Avatar of f19l

asked on

What is the Excel VB code to select one Outlook mailbox over another?


I currently have some Excel VB code that searches for an email with a specified attachment in Outlook and then opens it up. I now have been told that there will be a new group mailbox (London MO) into which this email will be dropped. I have tried to change the code (shown below) myself but with no success.
Public Sub GetAttachments_CSLDELRP()
On Error GoTo GetAttachments_err
 Dim olkAPp As Object
 Dim ns As Object
 Dim Inbox As Object
 Dim Item As Object
 Dim Atmt As Object
 Dim FileName As String
 Dim I As Integer
 Dim timestamp As Date
 Dim filecheck As Boolean
 Dim ASK As String
 Dim TRYBOOK As String
 Dim Txt As String
 Dim count_emails As Integer
 Application.ScreenUpdating = False
 TRYBOOK = ActiveWorkbook.Name
 filecheck = False
 Set olkAPp = CreateObject("outlook.application")
 Set ns = olkAPp.GetNamespace("MAPI")
 Set Inbox = ns.GetDefaultFolder(6)
 I = 0
 count_emails = 0
 If Inbox.Items.Count = 0 Then
    MsgBox "There are no messages in the Inbox.", vbInformation, _
           "Nothing Found"
    Exit Sub
 End If
 For Each Item In Inbox.Items
   For Each Atmt In Item.Attachments
      If Atmt.FileName = "CSLDELRP" & Format(Workbooks(TRYBOOK).Sheets("Reference").Range("Report_Date").Value, "yyyymmdd") & ".csv" Then
        Kill "\\EMEA\Root\Shared2\London Rates\Money Market\CSLDELRP.csv"
        FileName = "\\EMEA\Root\Shared2\London Rates\Money Market\CSLDELRP.csv"
        Atmt.SaveAsFile FileName
        Exit Sub
      End If
   Next Atmt
count_emails = count_emails + 1
Next Item
   Set Atmt = Nothing
   Set Item = Nothing
   Set ns = Nothing
   GoTo Line1
If count_emails > 10000 Then
     MsgBox "There is no CSL14R5 Delimited Excel CSLDELRP email for the previous business day in the inbox.", vbInformation, _
           "Nothing Found!!!"
End If
Resume GetAttachments_exit
Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Should be as simple as:

olkapp.session.folders(1) ... through folders(n)
assuming for example the PSTs are uniquely named and they are named CRB1 through CRB4 then:

olkapp.session.folders("CRB1") so that for example:

set myInbox = olkapp.session.folders("CRB4")  'will access the specific PST.

added Excel zone

aikimark -- zone advisor
Avatar of f19l


Actually, the emails will be dropped into the Inbox (sub-folder) of Mailbox - London MO. What would be the code for that?
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial