Please help me understand this code.

[thardin]

Ok, so I have an email with an attachment I receive everyday and it is automatically sent to an Outlook folder named "Voya".
What I want to do is write a macro where I can open the file from today's email only on a daily basis.
I tested this code and a file is saved to a folder on my desktop, but here are my questions:
1. How do I make this so it just opens instead of saves?
2 . This code doesn't specify a date or anything, so is it just extracting the attachment of the most recent email?
3. I see this code uses a "For Each OlMail In OlItem." Does that mean it is attempting to extract all the attachments from all the emails in that folder? Because if so, it only extracted one(like I want) and I have several, so I'm confused.


I'm sharing this code with others so I want to make sure it's written correctly. Any other feedback is appreciated.
Thanks.

Sub Download_test()
Dim OlApp As Object
Dim OlMail As Object
Dim OlItems As Object
Dim OlFolder As Object
Dim i As Integer
Dim strFolder As String


Set OlApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.Application")
End If

strFolder = "...............\Desktop\New folder"

Set OlFolder = OlApp.GetNamespace("MAPI").Folders(MY_EMAIL).Folders("Voya")

Set OlItems = OlFolder.Items

For Each OlMail In OlItems
If OlMail.Attachments.Count > 0 Then
For j = 1 To OlMail.Attachments.Count
OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).FileName
Next j
End If
Next

Set OlFolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlApp = Nothing

End Sub

 

[mackc557]

Two things:
First, you have posted this in the Excel Questions section. You can post questions about Outlook in the General Discussion & Other Applications section.
Second, yes, this code is going through each mail item within folder "Voya" and then within each of those items it is saving each attachment.

Try this:

Sub Display_Most_Recent()
Dim OlApp As Object
Dim OlMail As Object
Dim OlItems As Object
Dim OlFolder As Object
Dim i As Long


Set OlApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.Application")
End If

Set OlFolder = OlApp.GetNamespace("MAPI").Folders(MY_EMAIL).Folders("Voya")

Set OlItems = OlFolder.Items

i = OlItems.Count

OlFolder.Items(i).Display


Set OlFolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlApp = Nothing

End Sub

 

[thardin]

Thank you for responding.
What I'm trying to do is open the attachment from the email in Excel, so that's why I put it in the Excel questions.

Your code works well for opening the most recent email. However, sorry if I didn't explain clearly, but how do I make it so it OPENs the attachment in Excel for just the most recent email.

Thank you.

 

[mackc557]

Ah! That makes more sense; see if this works.

Sub Download_test()
Dim OlApp As Object
Dim OlMail As Object
Dim OlItems As Object
Dim OlFolder As Object
Dim i As Long
Dim strFolder As String


Set OlApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.Application")
End If

strFolder = "...............\Desktop\New folder"

Set OlFolder = OlApp.GetNamespace("MAPI").Folders(MY_EMAIL).Folders("Voya")

Set OlItems = OlFolder.Items

i = OlItems.Count

OlFolder.Items(i).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(i).Filename

Workbooks.Open (strFolder & "\" & OlMail.Attachments.Item(i).Filename)

Set OlFolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlApp = Nothing

End Sub

 

[thardin]

I get a run-time error '91': "Object variable or With block variable not set" for the line:

OlFolder.Items(i).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(i).Filename

 

[mackc557]

My mistake! Typo! It should be:

OlFolder.Items(i).SaveAsFile strFolder & "\" & OlMail.Attachments.Items(i).Filename

I was missing the 's' at the end of Items

 

[thardin]

Still didn't work

 

[thardin]

My mistake! Typo! It should be:

OlFolder.Items(i).SaveAsFile strFolder & "\" & OlMail.Attachments.Items(i).Filename

I was missing the 's' at the end of Items

Am I supposed to Set OlMail to anything?
Why is "Set OlFolder" stated twice?

 

[mackc557]

I'm sorry, forgot a couple of corrections. Here is the code.

Sub Download_test()
Dim OlApp As Object
Dim OlMail As Object
Dim OlItems As Object
Dim OlFolder As Object
Dim i As Long
Dim strFolder As String


Set OlApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.Application")
End If

strFolder = "...............\Desktop\New folder"

Set OlFolder = OlApp.GetNamespace("MAPI").Folders(MY_EMAIL).Folders("Voya")

Set OlItems = OlFolder.Items

i = OlItems.Count

OlFolder.Items(i).SaveAsFile strFolder & "\" & OlFolder.Items(i).Attachments.Item(1).Filename

Workbooks.Open (strFolder & "\" & OlFolder.Items(i).Attachments.Item(1).Filename)

Set OlFolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlApp = Nothing

End Sub

 

[thardin]

I'm sorry, forgot a couple of corrections. Here is the code.

Sub Download_test()
Dim OlApp As Object
Dim OlMail As Object
Dim OlItems As Object
Dim OlFolder As Object
Dim i As Long
Dim strFolder As String


Set OlApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.Application")
End If

strFolder = "...............\Desktop\New folder"

Set OlFolder = OlApp.GetNamespace("MAPI").Folders(MY_EMAIL).Folders("Voya")

Set OlItems = OlFolder.Items

i = OlItems.Count

OlFolder.Items(i).SaveAsFile strFolder & "\" & OlFolder.Items(i).Attachments.Item(1).Filename

Workbooks.Open (strFolder & "\" & OlFolder.Items(i).Attachments.Item(1).Filename)

Set OlFolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlApp = Nothing

End Sub

Now I'm getting a Run-time error '438': Object doesn't support this property or method.