Download e-mail attachments depending on subject line

[boyexcel]

Hi folks,

I'm quite a newbie when it comes to creating macros in Outlook and I've been researching on how to go about this as there's no recording app.

The issue that I'm having is that I am receiving several e-mails with the same subject line for the day but they contain different attachments.
For example:
Subject line: Daily activities: 03/26/2013
attached file: a.txt
Subject line: Daily activities: 03/26/2013
attached file: b.txt
Subject line: Daily activities: 03/26/2013
attached file: c.txt
Subject line: Daily activities: 03/26/2013
attached file: d.txt

I only download for example attachment a.txt and c.txt and start my work from there.

So my concern is, I would like to know how to create a macro to download specific attachments depending on the subject line and save it to a folder.

I can make use of a user prompt to enter the date to filter the subject lines containing the entered date and then automatically download the attachments that I needed to a folder.

Would this be possible?

Can it be implemented on Excel?

Please do not hesitate to reply if you need more information from me.

All suggestions will be greatly appreciated.

Thank you!

 

[boyexcel]

any good samaritan out there?

 

[boyexcel]

So it's not possible?

 

[boyexcel]

would like to push this up

 

[John_w]

Try this code, which you should put in a module in an Excel workbook. You also need to set a reference to the Microsoft (Office) Outlook Library in Tools - References in the VB editor before running or compiling the code.

It assumes the emails are in the Inbox folder (change this part of the code where indicated if not, or use the PickFolder method currently commented out to let the user select the Outlook folder). The emails are expected to have the exact subject "Daily activities: mm/dd/yyyy" where mm/dd/yyyy is the date inputted. The attachments are saved in C:\path\to\folder - change this in the code where indicated to the folder where you want them to be saved.

Public Sub Extract_Outlook_Email_Attachments()

    Dim OutlookOpened As Boolean
    Dim outApp As Outlook.Application
    Dim outNs As Outlook.Namespace
    Dim outFolder As Outlook.MAPIFolder
    Dim outAttachment As Outlook.Attachment
    Dim outItem As Object
    Dim outMailItem As Outlook.MailItem
    Dim inputDate As String, subjectFilter As String
    Dim saveInFolder As String
    
    saveInFolder = "C:\path\to\folder\"                                 'CHANGE FOLDER PATH AS NEEDED
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    inputDate = InputBox("Enter date to filter the email subject", "Extract Outlook email attachments")
    If inputDate = "" Then Exit Sub
    
    subjectFilter = "Daily activities: " & Format(inputDate, "dd/mm/yyyy")
    
    'Get or create Outlook object and make sure it exists before continuing
    
    OutlookOpened = False
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set outApp = New Outlook.Application
        OutlookOpened = True
    End If
    On Error GoTo 0
    
    If outApp Is Nothing Then
        MsgBox "Cannot start Outlook.", vbExclamation
        Exit Sub
    End If
    
    Set outNs = outApp.GetNamespace("MAPI")
    
    Set outFolder = outNs.Folders("Personal Folders").Folders("Inbox")  'CHANGE FOLDER AS NEEDED
    'Set outFolder = outNs.PickFolder                                   'OR USER SELECTS FOLDER

    If Not outFolder Is Nothing Then
        For Each outItem In outFolder.Items
            If outItem.Class = Outlook.OlObjectClass.olMail Then
                Set outMailItem = outItem
                If outMailItem.Subject = subjectFilter Then
                    Debug.Print outMailItem.Subject
                    For Each outAttachment In outMailItem.Attachments
                        outAttachment.SaveAsFile saveInFolder & outAttachment.Filename
                    Next
                End If
            End If
        Next
    End If

    If OutlookOpened Then outApp.Quit
    
    Set outApp = Nothing

End Sub

 

[Sleepingweasle]

The above is great. How would I change it to save the entire email with attachments rather than just the attachment?

 

[John_w]

Sleepingweasle,

replace:

                    For Each outAttachment In outMailItem.Attachments
                        outAttachment.SaveAsFile saveInFolder & outAttachment.Filename
                    Next

                    outMailItem.SaveAs saveInFolder & Format(Now, "yyyymmdd hhnnss") & ".msg", olMSG

Change the output file name as needed; I've used the current timestamp.

 

[Sleepingweasle]

Thank you so much. You people are really great! If you have the time, could you recommend a method (recommend books, courses, etc.) for me to learn this for myself? I understand Excel quite well, it's really VBA I would like to know more about, and I don't think it's right to just mooch off the help of others, I'd like to be able to help people as well some day. Thanks again for all your help!

 

[John_w]

I would start by recording macros to do simple tasks then edit the code: make them more efficient by removing Select and Selection, using With ... End With blocks; make the macro loop with different cells; change hard-coded strings and cell references, etc. Always use Option Explicit and declare variables using the most appropriate data type.

Seeing how people solve the questions on this board and having a go yourself is also useful.

 

[Sleepingweasle]

Sounds great. I'll give it a try. Thanks a lot, you were really helpful.