Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel — AttachmentFetcher — Download all the Attachments from All the Mails of Specific Subject in Microsoft Outlook .

Down­load Link :Attach­ment­Fetcher

If you want to down­load the attach­ments from all the mails in your Microsoft out­look which has a spe­cific sub­ject name. I am very sure if you have 1000 of mails then you don’t want to do it man­u­ally. So here is the Attach­ment­fetcher which does exactly the same.

How to use it:

  1. Down­load the Attachmentfetcher.xlsm from the link pro­vided at the top and at the bot­tom of this article.
  2. Open the Attachmentfetcher.xlsm
  3. Enter the Mail sub­ject key­word here I have entered “Very Spe­cific Subject”
  4. Enter the Local path in your sys­tem where you want to down­load all the attachments
  5. Click the “Fetch” but­ton
  6. That’s it „ its done. You don’t actu­ally need this step J


NOTE: All the Attach­ments will have a ran­dom num­ber appended at the name , just to avoid the col­li­sions if two or more attach­ment has the same name.

Exam­ple :

  • All Mails
Mails

Mails

  • Set the sub­ject name and local path
Settings

Set­tings

  • Results

Down­load Link :Attach­ment­Fetcher

List of mails

List of mails

Attachments

Attach­ments

Com­plete Code:

Sub sumit()

    readMails

End Sub


Function readMails()
    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace    
    Dim olItem As Outlook.MailItem
    Dim i As Integer
    Dim b As Integer
    Dim olInbox  As Outlook.MAPIFolder
    Dim olFolder As Outlook.MAPIFolder
    Dim lngCol As Long
    Dim oMsg As Outlook.MailItem
    Dim mainWB As Workbook
    Dim keyword
    Dim Path
    Dim Count
    Dim Atmt
    Dim f_random
    Dim Filename
    'Dim olInbox As inbo
    Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")
     
     Set mainWB = ActiveWorkbook
     
    Set olInbox = olNamespace.GetDefaultFolder(Outlook.olFolderInbox)
    Dim oItems As Outlook.Items
   Set oItems = olInbox.Items
    mainWB.Sheets("Main").Range("A:A").Clear
    mainWB.Sheets("Main").Range("B:B").Clear
    mainWB.Sheets("Main").Range("A1,B1").Interior.ColorIndex = 46
    Path = mainWB.Sheets("Main").Range("J5").Value
    keyword = mainWB.Sheets("Main").Range("J3").Value
    mainWB.Sheets("Main").Range("A1").Value = "Number"
    mainWB.Sheets("Main").Range("B1").Value = "Subject"
    mainWB.Sheets("Main").Range("A1,B1").Borders.Value = 1
    
    
    
    'MsgBox olInbox.Items.Count
   Count = 2
    For i = 1 To oItems.Count
        If TypeName(oItems.Item(i)) = "MailItem" Then
            Set oMsg = oItems.Item(i)
             
             If InStr(1, oMsg.Subject, keyword, vbTextCompare) > 0 Then
             'MsgBox "asfsdfsdf"
                    'MsgBox oMsg.Subject
                    mainWB.Sheets("Main").Range("A" & Count).Value = Count - 1
                    mainWB.Sheets("Main").Range("B" & Count).Value = oMsg.Subject
                    For Each Atmt In oMsg.Attachments
                    f_random = Replace(Replace(Replace(Now, " ", ""), "/", ""), ":", "") & "_"
                    Filename = Path & f_random & Atmt.Filename
                    'MsgBox Filename
                    Atmt.SaveAsFile Filename
                    FnWait (1)
                    Next Atmt
                    Count = Count + 1
             End If
        End If       
        
    Next 
   

End Function
Function FnWait(intTime)

    Dim newHour
    Dim NewMinute
    Dim newSecond
    Dim waitTime
    

    newHour = Hour(Now())

    NewMinute = Minute(Now())

    newSecond = Second(Now()) + intTime

     waitTime = TimeSerial(newHour, NewMinute, newSecond)

 Application.Wait waitTime

End Function

 

You may also like...

5 Responses

  1. akhil parameswaran says:

    you are doing a great job 🙂

  2. Pankaj Gupta says:

    Dear Sumit ji,

    is there any way to fetch the attachemnt from yahoo or gmail.

    Thanks,
    Pankaj Gupta

  3. uday says:

    Can we add the time stamp to the attachment?

    • SJ says:

      The exam­ple men­tioned in the post already adding the time stamp to the attach­ments, are you talk­ing about the same?

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: