LifesAGarden
New Member
- Joined
- May 2, 2016
- Messages
- 4
I get an hourly report (there is a 30 min range) that is sent to a public folder labeled "TAT Monitor" in Outlook. I have been trying to figure how to run a macro that pulls the excel file attachment off of the email and place it in a specified folder(ran through excel).
The issue is the macro I compiled below only works on a folder in my inbox (I was testing to make sure it worked, which it did but only within my inbox). I created a test folder in my inbox named "Test" and used taht folder to test my macro below. However, I can't seem to get it to point to my public folder. I tried changing the parent folder to public but it errors. Ideally, I want this to point to the "TAT Monitor" folder in the public folder in my outlook.
I have done a fair amount of searching but I have not cracked this one yet.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Public Sub GetAttachments()
Dim Inbox As MAPIFolder
Dim Item As MailItem
Dim Atmt As Attachment
Dim FileName As String
Dim firstDate As Date
Dim secondDate As Date
Dim i As Integer
Set Inbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders.Item("Test")
firstDate = Date - 1 + TimeValue("8:00:00am")
secondDate = Date - 1 + TimeValue("8:30:00am")
For Each Item In Inbox.Items
For Each Atmt In Item.Attachments
If Item.SentOn > firstDate And Item.SentOn < secondDate Then
FileName = "C:\Users\user1\Desktop\TEST2\" & Atmt & ".xlsx"
Atmt.SaveAsFile FileName
Else
End If
Next
Next
Set Inbox = Nothing
End Sub</code>
The issue is the macro I compiled below only works on a folder in my inbox (I was testing to make sure it worked, which it did but only within my inbox). I created a test folder in my inbox named "Test" and used taht folder to test my macro below. However, I can't seem to get it to point to my public folder. I tried changing the parent folder to public but it errors. Ideally, I want this to point to the "TAT Monitor" folder in the public folder in my outlook.
I have done a fair amount of searching but I have not cracked this one yet.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Public Sub GetAttachments()
Dim Inbox As MAPIFolder
Dim Item As MailItem
Dim Atmt As Attachment
Dim FileName As String
Dim firstDate As Date
Dim secondDate As Date
Dim i As Integer
Set Inbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders.Item("Test")
firstDate = Date - 1 + TimeValue("8:00:00am")
secondDate = Date - 1 + TimeValue("8:30:00am")
For Each Item In Inbox.Items
For Each Atmt In Item.Attachments
If Item.SentOn > firstDate And Item.SentOn < secondDate Then
FileName = "C:\Users\user1\Desktop\TEST2\" & Atmt & ".xlsx"
Atmt.SaveAsFile FileName
Else
End If
Next
Next
Set Inbox = Nothing
End Sub</code>