-
Aug 5th, 2021, 04:49 PM
#1
Thread Starter
Lively Member
[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.
-
Aug 5th, 2021, 06:00 PM
#2
Fanatic Member
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
-
Aug 6th, 2021, 04:03 AM
#3
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
-
Aug 6th, 2021, 08:00 AM
#4
Thread Starter
Lively Member
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
-
Aug 6th, 2021, 09:23 AM
#5
Fanatic Member
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
-
Aug 6th, 2021, 09:53 AM
#6
Thread Starter
Lively Member
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
-
Aug 6th, 2021, 02:05 PM
#7
Thread Starter
Lively Member
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.
-
Aug 9th, 2021, 08:20 AM
#8
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|