Results 1 to 8 of 8

Thread: [RESOLVED] Problem with loop the VBA

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Resolved [RESOLVED] Problem with loop the VBA

    Hi Everyone,
    I have a VBA to save the attachment from unread emails one by one on shared mailbox. but this VBA looping only 2 times. (It will check and save the attachments with 2 mails only). I want to save the attachments from all the unread emails on shared mailbox. can anyone help me to fix this issue.

    Code:
    Option Explicit
    
         Sub saveattachment()
    
        Dim ol As Outlook.Application
    
        Dim ns As Outlook.NameSpace
    
        Dim fol As Outlook.Folder
    
            Dim folco As Outlook.MAPIFolder
    
        Dim i As Object
    
        Dim mi As Outlook.MailItem
    
        Dim at As Outlook.Attachment
    
        Dim fso As Scripting.FileSystemObject
    
        Dim dir As Scripting.Folder
    
        Dim dirName As String
    
        Dim dirpath As String
    
        Set fso = New Scripting.FileSystemObject
    
     
    
        Set ol = New Outlook.Application
    
        Set ns = ol.GetNamespace("MAPI")
    
        'Set fol = ns.Folders(1).Folders("inbox")
    
        Set fol = ns.Folders("local mailbox").Folders("Inbox")
    
            Set folco = ns.Folders("local mailbox").Folders("Deleted Items")
    
           
    
    ' 'Set fol = ns.Folders("Shared folder").Folders("Inbox")
    
    ' 'Set folco = ns.Folders("Shared folder").Folders("Deleted Items")
    
         'ns.Folders(1).Folders(1) ns.Folders("Personal Folders").Folders("Deleted Items")
    
     
    
     
    
        For Each i In fol.Items
    
       
    
            If i.Class = 43 Then
    
           
    
             Set mi = i
    
    '            If mi.UnRead = True Then
    
                If mi.Attachments.Count > 0 Then
    
                    'Debug.Print mi.SenderName, mi.ReceivedTime, mi.Attachments.Count
    
                   
    
                    dirName = _
    
                        "C:\users\Input\" & _
    
                        Format(mi.ReceivedTime, "DD-mm-YYYY hh-nn-ss ") & _
    
                        Left(Replace(mi.Subject, ":", ""), 10)
    
                   
    
    '                If fso.FolderExists(dirName) Then
    
    '                    Set dir = fso.GetFolder(dirName)
    
                       dirpath = "C:\users\Input\"
    
    '                Else
    
    '                    Set dir = fso.CreateFolder(dirName)
    
    '                End If
    
                   
    
                    For Each at In mi.Attachments
    
                    If InStr(at.DisplayName, ".xlsx") Then
    
                        'Debug.Print vbTab, at.DisplayName, at.Size
    
    '                    at.SaveAsFile dirpath & "\" & at.Filename & "\" & Format(Date, "ddmmyyyy") & "_" & at.DisplayName
    
                                at.SaveAsFile dirpath & "\" & Format(Date, "dd-mm-yyyy") & "_" & at.DisplayName
    
                                mi.Move folco
    
    '                            mi.UnRead = False
    
                                End If
    
                    Next at
    
                   
    
                End If
    
               
    
            End If
    
    '    End If
    
     
    
        Next i
    
    
    
    End Sub
    Thanks in Advance
    Last edited by gmmdinesh; Aug 5th, 2021 at 04:52 PM.

  2. #2
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Problem with loop the VBA

    Since nobody can see your inbox, we can only assume certain things.

    1, The loop is looping through 'Items' in the inbox - What exactly is in the inbox folder

    2 the If statement (Class = 43)is targeting only Emails - again what is in this inbox folder

    these are the only 2 things i can see in the code block that would be giving you problems outside of some weird error that might throw you out of the loop.

    Try testing the loop without code and do a debug.print of what it actually loops through to see if its actually looping through what you think it is and take note of the item.class part to see if its working the way you intended it to work.

    Apart from that i dont see anything wrong with the code, but im not testing it so it maybe a problem there.
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Problem with loop the VBA

    as you move (delete) your items during your for each loop, it probably screws the number of emails processed

    you should try like

    Code:
        For i = fol.items.count to 1 step = -1  '  if the items collection is 0 based reduce both ends of the loop by 1
            If fol.items(i).Class = 43 Then
             Set mi = fol.items(i)
    you could also use the outlook restrict method for items to only have items in the collection that meet the criteria, would save looping through ones that are not wanted
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Problem with loop the VBA

    Hi Westconn,
    Thanks for your code, when i try to your code i'm getting Type mismatch error with i on below line,
    For i = fol.items.count to 1 step = -1
    Thanks

  5. #5
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Problem with loop the VBA

    you set i as an object, westconn's example is using i numerically. try changing the type of i to a numerical one or make another variable 'j' maybe and use that instead
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  6. #6

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Problem with loop the VBA

    Hi GBeats,
    Thanks for your reply, It works fine.
    But it move all the (Read and Unread) to Deleted Items, I want to move only Unread Items.

    Thanks again

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Problem with loop the VBA

    Hi GBeats and Westconn,
    I have resolved the above issue myself.
    Thank you so much for your Code and help.

  8. #8
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Problem with loop the VBA

    mark as resolved, glad you fixed your issue
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width