VBA to save attachments (based on specific criteria) from email with multiple accounts

Situation: I have a code that, given the sender's email input, will download all attachments from the Outlook email (if a sender is specified, it saves the .xls attachments).

Problem 1: In my forecast, I have access to 2 accounts (say private and public). I want to be able to choose from which of these accounts the code should download attachments.

Question 1: Can such a choice be made? From previous research I was able to find criteria for attachment type and more, but nothing for multiple mailboxes.

Problem 2: . Among the attachments in this second mailbox (public), I only want to select files that have a worksheet with a specific "NAME". I know how to do this if you need to take into account this, but I do not know if you can read the file (and check if it has the desired sheet) and only then download it.

Question 2: Can I access a file like this? Is it possible to perform such a test of the criteria?

Code:

Sub email()

Application.ScreenUpdating = False

On Error Resume Next

Dim olApp As New Outlook.Application
Dim olNameSpace As Object
Dim olMailItem As Outlook.MailItem
Dim olFolder As Object
Dim olFolderName As String
Dim olAtt As Outlook.Attachments
Dim strName As String
Dim sPath As String
Dim i As Long
Dim j As Integer
Dim olSubject As String
Dim olSender As String
Dim sh As Worksheet
Dim LastRow As Integer

ThisWorkbook.Worksheets("FileNames").Rows(2 & ":" & ThisWorkbook.Worksheets("FileNames").Rows.count).Delete

olFolderName = ThisWorkbook.Worksheets("Control").Range("D10")
olSender = ThisWorkbook.Worksheets("Control").Range("D16")

sPath = Application.FileDialog(msoFileDialogFolderPicker).Show
sPathstr = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

Set olNameSpace = olApp.GetNamespace("MAPI")

'check if folder is subfolder or not and choose olFolder accordingly
Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox).Folders(olFolderName)
If (olFolder = "") Then
    Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox).Parent.Folders(olFolderName)
End If

'loop through mails
h = 2
For i = 1 To olFolder.Items.count
    Set olMailItem = olFolder.Items(i)

    If (InStr(1, olMailItem.SenderEmailAddress, olSender, vbTextCompare) <> 0) Then

        With olMailItem

            'loop through attachments
            For j = 1 To .Attachments.count

                strName = .Attachments.Item(j).DisplayName

                'check if file already exists
                If Not Dir(sPathstr & "\" & strName) = "" Then
                .Attachments(j).SaveAsFile sPathstr & "\" & "(1)" & strName
                ThisWorkbook.Worksheets("FileNames").Range("A" & h) = "(1)" & strName
                Else
                .Attachments(j).SaveAsFile sPathstr & "\" & strName
                ThisWorkbook.Worksheets("FileNames").Range("A" & h) = strName
                End If

                h = h + 1
            Next

        End With

    End If
Next

Application.ScreenUpdating = True
MsgBox "Download complete!", vbInformation + vbOKOnly, "Done"

End Sub

      

0


source to share


1 answer


Each folder in Outlook has a unique path. Even if both are named Inbox, the path to them is different. Select the first inbox in Outlook and go to the Immediate window (Alt + F11 then Ctrl + G). Type this and hit enter

?application.ActiveExplorer.CurrentFolder.FolderPath

      

You will get something like

\\dkusleika@copmany.com\Inbox

      

Now go back to Outlook and select a different inbox. Go back to the Immediate window and run the same command. You will now have a path to each inbox. Maybe the second one looks like



\\DKPersonal\Inbox

      

You are using GetDefaultFolder

, which is very convenient. But you can get to any folder, even the default folders, by following their path directly.

Set olFolder = Application.GetNamespace("MAPI").Folders("dkusleika@company.com").Folders("Inbox")

      

Just concatenate the properties Folders

until you get the one you want.

As for Question 2, you cannot check the Excel file without opening it. You will need to download it to a temporary location, open it to see if it contains a worksheet, and move it to its final location if that happens. Or download it to its final location and delete it if it doesn't have a sheet.

0


source







All Articles