Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Object loop isn't working 2

Status
Not open for further replies.

vba317

Programmer
Mar 5, 2009
708
US
I have tried another 10 ideas since my post this morning. I get sent a file every day with an attachment and what I need to do is is save that attachment to a folder. I only want the email from today saved in the folder. The code I have does not give me any errors but It isn't looping through the files either. When I step through I get a message of the oldest file in the folder. The message doesn't change it keeps on reading the same file, the code does not update. I only want the file to be saved if it was the file sent today.

Tom


Code:
'10th try

Dim objOutlook As Object
Dim objNamespace As Namespace
Dim objInbox As Object
Dim strFolderName As String
Dim objMailbox As Object
Dim objFolder As Object
Dim objItem As Object
Dim File_Path As String
Dim itm As MailItem
Dim atch As Object
Dim FileName As String
Dim strDate1 As String
Dim strDate2 As String
Dim strDate3 As String
Dim strDateType1 As String
Dim strDateType2 As String
Dim strDateType3 As String
Dim strFileName As String

Const olFolderInbox = 6

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNamespace.GetDefaultFolder(olFolderInbox)
strFolderName = objInbox.Parent
Set objMailbox = objNamespace.Folders(strFolderName)
Set objFolder = objMailbox.Folders("DailyNetAddsLegacy")

    File_Path = "\\cable\ncd-shared\DIV-FIN-Business-Analytics-Shared\Adhoc\Tom\DOR\Daily_Net_Adds_Legacy\"
    strDateType1 = "C_MM/DD/YYYY"
    strDateType2 = "C_MMDDYYYY"
    strDateType3 = ""
    Call DefineDateType(strDate1, strDate2, strDate3, strDateType1, strDateType2, strDateType3)
    
'    '''''Loop Thru Each Mail Item
            
    For Each itm In objFolder.Items
    '''''Loop Thru Each Attachment
    
        For Each atch In itm.Attachments
            On Error Resume Next
            Set itm = atch
            For Each myMail In Outlook.ActiveExplorer.Selection
                MsgBox "Message was received at: " & myMail.ReceivedTime
                If itm.ReceivedTime = strDate1 Then atch.SaveAsFile File_Path & atch.FileName
           Next myMail
        Next atch
    Next itm
 
Hi,

Set itm = atch

REALLY??? Does that make sense???

But this looks like a problem too...

If Itm.ReceivedTime = strDate1.......

Date/Time is not string, I do believe. And if it is, is it in the same format as your strDate1. And where does strDate1 get assigned?

 

Here is the DefineDateType Sub. I commented out the Set itm = atch line
In the sub I convert the date to a string, but it should be the same as the date.
I changed the names to make more sense to me. atch to Attachment and itm to Item
The code is looping but not going to the next email, instead the loop is staying on the first email.
Code:
Public Sub DefineDateType(strDate1 As String, strDate2 As String, strDate3 As String, strDateType1 As String, strDateType2 As String, strDateType3 As String)
'Call DefineDateType(strDate1, strDate2, strDate3, strDateType1, strDateType2, strDateType3)
Dim datemin As Date
datemin = Date
    Select Case strDateType1
        Case "C_MMDDYYYY"
            'Format 04022015
            strDate1 = Format(CDate(datemin), "MMDDYYYY")
        Case "C_MM"
            'Format Number 03 for the month of March
            '03
            strDate1 = Format(CDate(datemin), "MM")
        Case "C_MMMMYYYY"
            'Format March Text for the month of March and 4 number year
            'April2015
            strDate1 = Format(CDate(datemin), "MMMMYYYY")
        Case "C_MMMM YYYY"
            'Format March Text for the month of March and 4 number year
            'April2015
            strDate1 = Format(CDate(datemin), "MMMM YYYY")
        Case "C_YYYY"
            'Format 4 number Year only
            '2015
            strDate1 = Format(CDate(datemin), "YYYY")
        Case "C_MMM"
            'Format 3 letter month
            'Apr
            strDate1 = Format(CDate(datemin), "MMM")
        Case "C_MMMM"
            'Format long Month Name
            'April
            strDate1 = Format(CDate(datemin), "MMMM")
        Case "C_MM/DD/YYYY"
            'Format Date format 04/21/2015
            'Apr
            strDate1 = Format(CDate(datemin), "MM/DD/YYYY")
        Case "T_DDD"
            'Format today's day
            '10
            strDate1 = Format(CDate(datemin), "DDD")
        Case "C_MMMMDDYYYY"
            'Format today's day
            'Apri102015
            strDate1 = Format(CDate(datemin), "MMMMDDYYYY")
        Case "Y_MMDDYYYY"
            'Format 04022015
            strDate1 = Format(CDate(datemin - 1), "MMDDYYYY")
        Case "Y_M/DD/YYYY"
            'Format 4/02/2015
            strDate1 = Format(CDate(datemin - 1), "M/DD/YYYY")
        Case "Y_MM/DD/YYYY"
            'Format 04/02/2015
            strDate1 = Format(CDate(datemin - 1), "MM/DD/YYYY")
        Case "Y_MM_MMM"
            'Format 04_Apr
            strDate1 = Format(CDate(datemin - 1), "MM_MMM")
        Case "C_M/D"
            strDate1 = Format(CDate(datemin), "M/D")
        Case "C_MM/DD/YYYY"
            strDate1 = Format(CDate(datemin), "MM/DD/YYYY")
        Case "3_DDD"
            'Format 3 days ago day
            strDate1 = Format(CDate(datemin - 3), "DDD")
        Case "3_MMDDYYYY"
            'Format 3 days ago day
            strDate1 = Format(CDate(datemin - 3), "MMDDYYYY")
   End Select
   
     Select Case strDateType2
         Case "C_MMDDYYYY"
            'Format 04022015
            strDate2 = Format(CDate(datemin), "MMDDYYYY")
        Case "C_MM"
            'Format Number 03 for the month of March
            '03
            strDate2 = Format(CDate(datemin), "MM")
        Case "C_MMMMYYYY"
            'Format March Text for the month of March and 4 number year
            'April2015
            strDate2 = Format(CDate(datemin), "MMMMYYYY")
        Case "C_YYYY"
            'Format 4 number Year only
            '2015
            strDate2 = Format(CDate(datemin), "YYYY")
        Case "C_MMM"
            'Format 3 letter month
            'Apr
            strDate2 = Format(CDate(datemin), "MMM")
        Case "C_MMMM"
            'Format long Month Name
            'April
            strDate2 = Format(CDate(datemin), "MMMM")
        Case "C_MM/DD/YYYY"
            'Format Date format 04/21/2015
            'Apr
            strDate2 = Format(CDate(datemin), "MM/DD/YYYY")
        Case "T_DDD"
            'Format today's day
            '10
            strDate2 = Format(CDate(datemin), "DDD")
        Case "C_MMMMDDYYYY"
            'Format today's day
            'Apri102015
            strDate2 = Format(CDate(datemin), "MMMMDDYYYY")
        Case "Y_MMDDYYYY"
            'Format 04/02/2015
            strDate2 = Format(CDate(datemin - 1), "MMDDYYYY")
        Case "Y_MM/DD/YYYY"
            'Format 04/02/2015
            strDate2 = Format(CDate(datemin - 1), "MM/DD/YYYY")
        Case "Y_M/DD/YYYY"
            'Format 4/02/2015
            strDate2 = Format(CDate(datemin - 1), "M/DD/YYYY")
        Case "Y_MM_MMM"
            'Format 04_Apr
            strDate2 = Format(CDate(datemin - 1), "MM_MMM")
        Case "C_M/D"
            strDate2 = Format(CDate(datemin), "M/D")
        Case "C_MM/DD/YYYY"
            strDate2 = Format(CDate(datemin), "MM/DD/YYYY")
        Case "3_DDD"
            'Format 3 days ago day
            strDate2 = Format(CDate(datemin - 3), "DDD")
        Case "3_MMDDYYYY"
            'Format 3 days ago day
            strDate2 = Format(CDate(datemin - 3), "MMDDYYYY")
   End Select
     
   Select Case strDateType3
        Case "C_MMDDYYYY"
            'Format 04022015
            strDate3 = Format(CDate(datemin), "MMDDYYYY")
        Case "C_MM"
            'Format Number 03 for the month of March
            '03
            strDate3 = Format(CDate(datemin), "MM")
        Case "C_MMMMYYYY"
            'Format March Text for the month of March and 4 number year
            'April2015
            strDate3 = Format(CDate(datemin), "MMMMYYYY")
        Case "C_YYYY"
            'Format 4 number Year only
            '2015
            strDate3 = Format(CDate(datemin), "YYYY")
        Case "C_MMM"
            'Format 3 letter month
            'Apr
            strDate3 = Format(CDate(datemin), "MMM")
        Case "C_MMMM"
            'Format long Month Name
            'April
            strDate3 = Format(CDate(datemin), "MMMM")
        Case "C_MM/DD/YYYY"
            'Format Date format 04/21/2015
            'Apr
            strDate3 = Format(CDate(datemin), "MM/DD/YYYY")
        Case "T_DDD"
            'Format today's day
            '10
            strDate3 = Format(CDate(datemin), "DDD")
        Case "C_MMMMDDYYYY"
            'Format today's day
            'Apri102015
            strDate3 = Format(CDate(datemin), "MMMMDDYYYY")
        Case "Y_MM/DD/YYYY"
            'Format 04/02/2015
            strDate3 = Format(CDate(datemin - 1), "MM/DD/YYYY")
        Case "Y_MMDDYYYY"
            'Format 04/02/2015
            strDate3 = Format(CDate(datemin - 1), "MMDDYYYY")
        Case "Y_M/DD/YYYY"
            'Format 4/02/2015
            strDate3 = Format(CDate(datemin - 1), "M/DD/YYYY")
        Case "Y_MM_MMM"
            'Format 04_Apr
            strDate3 = Format(CDate(datemin - 1), "MM_MMM")
        Case "C_M/D"
            strDate3 = Format(CDate(datemin), "M/D")
        Case "C_MM/DD/YYYY"
            strDate3 = Format(CDate(datemin), "MM/DD/YYYY")
        Case "3_DDD"
            'Format 3 days ago day
            strDate3 = Format(CDate(datemin - 3), "DDD")
        Case "3_MMDDYYYY"
            'Format 3 days ago day
            strDate3 = Format(CDate(datemin - 3), "MMDDYYYY")
   End Select
        
End Sub


Current Code

Code:
Dim objOutlook As Object
Dim objNamespace As Namespace
Dim objInbox As Object
Dim strFolderName As String
Dim objMailbox As Object
Dim objFolder As Object
Dim objItem As Object
Dim File_Path As String
Dim Item As MailItem
Dim Attachment As Object
Dim FileName As String
Dim strDate1 As String
Dim strDate2 As String
Dim strDate3 As String
Dim strDateType1 As String
Dim strDateType2 As String
Dim strDateType3 As String
Dim strFileName As String
Dim strOldName As String
Dim strNewName As String
Dim strOutputFile As String

Const olFolderInbox = 6

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNamespace.GetDefaultFolder(olFolderInbox)
strFolderName = objInbox.Parent
Set objMailbox = objNamespace.Folders(strFolderName)
Set objFolder = objMailbox.Folders("DailyNetAddsLegacy")

    File_Path = "\\cable\ncd-shared\DIV-FIN-Business-Analytics-Shared\Adhoc\Tom\DOR\Daily_Net_Adds_Legacy\"
    strDateType1 = "C_MM/DD/YYYY"
    strDateType2 = "C_MMDDYYYY"
    strDateType3 = ""
    Call DefineDateType(strDate1, strDate2, strDate3, strDateType1, strDateType2, strDateType3)
    
'    '''''Loop Thru Each Mail Item
            
    For Each Item In objFolder.Items
    '''''Loop Thru Each Attachment
    
        For Each Attachment In itm.Attachments
          On Error Resume Next
            For Each myMail In Outlook.ActiveExplorer.Selection
                MsgBox "Message was received at: " & myMail.ReceivedTime
                If Item.ReceivedTime = strDate1 Then Attachment.SaveAsFile File_Path & Attachment.FileName
            Next myMail
        Next Attachment
    Next Item
    
    'Rename File with today's date
    strOutputFile = "Daily Net Adds - Current Week - Legacy View"
    strOldName = strPath & strOutputFile & ".xls"     '   "C:\MYDIR\OLDFILE"
    strNewName = strPath & strOutputFile & strDate2 & ".xls"       '  "C:\YOURDIR\NEWFILE"
    Name strOldName As strNewName    ' Move and rename file.

End Sub
 
[URL unfurl="true" said:
https://msdn.microsoft.com/en-us/library/office/ff867228(v=office.15).aspx[/URL]]MailItem.ReceivedTime Property (Outlook)
Office 2013 Other Versions
Returns a Date indicating the date and time at which the item was received. Read-only.

Date is not STRING!

Code:
         If [highlight #FCE94F]Format(itm.ReceivedTime, "MMDDYYYY") = strDate1[/highlight] Then atch.SaveAsFile File_Path & atch.FileName

And it would net be surprising that there are yet more problems.

I like to build and test and add and test and add and test............

Furthermore I like to build small procedures, that generally can be viewed on the screen without scrolling.
 
You are correct I changed the properties of the Dim statement to Dim Item As Outlook.MailItem and Dim objInbox As Outlook.MAPIFolder and now the loop is working. But as you correctly pointed out the Date function is not working. When I go through the loop the time stamp is included in the Item.ReceivedTime property. Currently at the Blue line I get an run-time error 13 Type mismatch . Any thoughts?


Code:
Dim strRcvDate As String
Dim strRcvDate1 As Date

....

For Each Attachment In Item.Attachments
          'On Error Resume Next
            For Each myMail In Outlook.ActiveExplorer.Selection
                MsgBox "Message was received at: " & Item.ReceivedTime
                'strRcvdate = Format(CDate(Item.ReceivedTime), "MMDDYYYY")
                strRcvDate = Item.ReceivedTime
               strRcvDate1 = Format(CDate(strRcvDate), "MMDDYYYY")
      [Blue]           If strRcvDate = Date Then Attachment.SaveAsFile File_Path & Attachment.FileName [/Blue]
            Next myMail
        Next Attachment
    Next Item
 
OK I got it to work. I changed the Dim statement and the Code.

Code:
Dim strRcvDate As Date
Dim strRcvDate1 As String


or Each Item In objFolder.Items
    '''''Loop Thru Each Attachment
    
        For Each Attachment In Item.Attachments
          'On Error Resume Next
            For Each myMail In Outlook.ActiveExplorer.Selection
                MsgBox "Message was received at: " & Item.ReceivedTime
                'strRcvdate = Format(CDate(Item.ReceivedTime), "MMDDYYYY")
                strRcvDate = Item.ReceivedTime
                strRcvDate1 = Format(CDate(strRcvDate), "MM/DD/YYYY")
                If strRcvDate1 = Date Then Attachment.SaveAsFile File_Path & Attachment.FileName
            Next myMail
        Next Attachment
    Next Item
 
faq707-4594

This is a prime example of where this technique would be invaluable!

I would break on this statement and then EXPLORE:

is [highlight #FCE94F]strRcvDate = Date[/highlight] TRUE or FLASE?

Does [highlight #FCE94F]File_Path & Attachment.FileName[/highlight] equate to a valid path?

BTW, a minor picky gripe:

Dim strRcvDate As String
Dim strRcvDate1 As Date

I'd use...

Dim sRcvDate As String
Dim [highlight #FCE94F]d[/highlight]RcvDate1 As Date

cuz ones a [highlight #FCE94F]s[/highlight]tring and the other is a [highlight #FCE94F]d[/highlight]ate
 
BTW, a minor picky gripe" in more than one place, changing data type:
[tt]
Dim strRcvDate As String
Dim strRcvDate1 As Date[/tt]
and [tt]
Dim strRcvDate As Date
Dim strRcvDate1 As String[/tt]
:-(


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Skip and Andy you both are correct it was my laziness. I try and designate what type of variable I am dealing with. Just started a new job and a little pressed for time. I want to send out a thank you to Skip, Andy and PHV, for all your help over the years you are truly MVP's!
 
Back in 1992, I met a guy who was an expert in Excel/VBA and I was able to scrape his brain back then and my meager knowledge took a significant step. When I found Tek-Tips in 2001, I experienced yet another signicant step in knowledge.

So I stand on the shoulders of many many others and I hope that you can have a similar experience.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top