VBA to get attachment from Outlook

wigarth

Board Regular
Joined
Apr 16, 2016
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Hi!

I have a macro that fetches a file from an email in Outlook every day, and then runs a number of other vba codes on it.
The macro runs great, but only gets the file from "today" and if there is multiple emails it only collects the oldest one.

As a workaround, I sometimes have to email myself yesterdays mail (So it will get "Todays date") in order for it to work.
And if i get multiple mails, I have to move them around to folders etc. so the macro will choose the right one.

I suppose making a vba to be able to choose or click on what attachment I would like to use, would be extremely long and complex,
but Is there some way to get it to collect the "newest" one in the inbox from the code provided below?
(I suspect the "%today" is relevant to it?)

VBA Code:
Sub Get_Report()

Dim colItems As Items, rst As Items, j%, att As Attachment, i%, olapp As Outlook.Application
On Error Resume Next
Set olapp = GetObject(, "Outlook.Application")
Err.Clear: On Error GoTo 0
If olapp Is Nothing Then Set olapp = CreateObject("Outlook.Application")
Set colItems = olapp.Session.GetDefaultFolder(olFolderInbox).Items ' desired folder

Set rst = colItems.Restrict("@SQL=" & "%today(" & AddQ("urn:schemas:httpmail:datereceived") & ")%")

If rst.Count = 0 Then
Exit Sub
Else
End If

For i = 1 To rst.Count
    For j = 1 To rst.Item(i).Attachments.Count
        Set att = rst.Item(i).Attachments.Item(j)
        If att.FileName Like "BaRe Oslo 2*" Then
            att.SaveAsFile "L:\kladd.xlsx"
Set att = Nothing: Set rst = Nothing
Set colItems = Nothing: Set olapp = Nothing
Exit Sub
End If

Next j, i
Set att = Nothing: Set rst = Nothing
Set colItems = Nothing: Set olapp = Nothing

'Some more stuff to do after here.

End Sub

Appreciate any help on the subject.
Best reggards:
Wigarth
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi!

I have a macro that fetches a file from an email in Outlook every day, and then runs a number of other vba codes on it.
The macro runs great, but only gets the file from "today" and if there is multiple emails it only collects the oldest one.

As a workaround, I sometimes have to email myself yesterdays mail (So it will get "Todays date") in order for it to work.
And if i get multiple mails, I have to move them around to folders etc. so the macro will choose the right one.

I suppose making a vba to be able to choose or click on what attachment I would like to use, would be extremely long and complex,
but Is there some way to get it to collect the "newest" one in the inbox from the code provided below?
(I suspect the "%today" is relevant to it?)

VBA Code:
Sub Get_Report()

Dim colItems As Items, rst As Items, j%, att As Attachment, i%, olapp As Outlook.Application
On Error Resume Next
Set olapp = GetObject(, "Outlook.Application")
Err.Clear: On Error GoTo 0
If olapp Is Nothing Then Set olapp = CreateObject("Outlook.Application")
Set colItems = olapp.Session.GetDefaultFolder(olFolderInbox).Items ' desired folder

Set rst = colItems.Restrict("@SQL=" & "%today(" & AddQ("urn:schemas:httpmail:datereceived") & ")%")

If rst.Count = 0 Then
Exit Sub
Else
End If

For i = 1 To rst.Count
    For j = 1 To rst.Item(i).Attachments.Count
        Set att = rst.Item(i).Attachments.Item(j)
        If att.FileName Like "BaRe Oslo 2*" Then
            att.SaveAsFile "L:\kladd.xlsx"
Set att = Nothing: Set rst = Nothing
Set colItems = Nothing: Set olapp = Nothing
Exit Sub
End If

Next j, i
Set att = Nothing: Set rst = Nothing
Set colItems = Nothing: Set olapp = Nothing

'Some more stuff to do after here.

End Sub

Appreciate any help on the subject.
Best reggards:
Wigarth
You need to sort the filtered items by ReceivedTime (or urn:schemas:httpmail:datereceived) in descending order so that the first item (rst.Item(1)) will always be the latest one.
VBA Code:
Set rst = colItems.Restrict("@SQL=" & "%today(" & AddQ("urn:schemas:httpmail:datereceived") & ")%")
rst.Sort "[ReceivedTime]", True 'Or, rst.Sort "urn:schemas:httpmail:datereceived", True
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top