VBA to search a shared outlook inbox and import the pdf attachment into excel

yarmulke

New Member
Joined
Jul 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Sub macro1()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim FolderPath As String
Dim FileName As String
FolderPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
Dim DmToday As Date
DmToday = Format(Date, "mmmm dd yyyy")


Dim EmAttach As Outlook.Attachments
Dim AttachCount As Long
Dim EmAttFile As Object
Dim sFileType As String
Dim oOutlook As Object
Dim oNS As Object
Dim oItems As Object
Dim oFilterItems As Object
Dim oFilterItem As Object
Dim sFilter As String
Dim bOutlookOpened As Boolean
Dim i As Long
Const olFolderInbox = 6


Set oOutlook = GetObject(, "Outlook.Application") 'Bind to existing instance of Outlook

Set oNS = oOutlook.GetNamespace("MAPI")
Set oItems = oNS.GetDefaultFolder(olFolderInbox)

'Set oItems = oNS.Folders("my email@email.com" - "Inbox")
' Set oItems = oItems.Folders("Inbox")



sFilter = "@SQL=""urn:schemas:httpmail:subject"" ci_phrasematch 'Daily Prod Report'"

'Concatenate with filter for todays date
sFilter = sFilter & "@SQL=""urn:schemas:httpmail:datereceived"" = DmToday"

'FROM Email Address Filter (exact match)
sFilter = sFilter & """urn:schemas:httpmail:fromemail"" ci_phrasematch 'data-studio-noreply@google.com'"

Set EmAttFile = oItems.Items.Restrict(sFilter)


' Get the file name.
EmAttFile = EmAttach.Item(1).FileName


' Combine with the path to the folder.
EmAttFile = FolderPath & EmAttFile

' Save the attachment as a file.
EmAttach.Item(1).SaveAsFile EmAttFile




End Sub



Before i get to work on importing the pdf into excel which is the easy part of the macro i cant seem to be able to search and find the file i need and am getting errors.

Any help will be appreciated!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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