0

I have a code that works perfectly when I run it in VBA/EXCEL. I created a module, and wrote the code below.

When I run the macro, It takes every e-mail in the outlook folder and create a new folder for each email with it's own attachments.

It's fine, but I have to run the macro to see the results. I want the process to be automatic, I dont want to have to run something. I want the automatic folder creation and attachments storage to be automaticaly done when I receive an email.

Thank you for your help. 

Option Explicit

Sub Application_Startup()

Dim ol As Outlook.Application
Dim ns As Outlook.Namespace
Dim fol As Outlook.Folder
Dim i As Object
Dim mi As Outlook.MailItem
Dim at As Outlook.Attachment
Dim rootfol As Outlook.Folder
Dim fso As Scripting.FileSystemObject
Dim dir As Scripting.Folder
Dim dirName As String



Set fso = New Scripting.FileSystemObject

Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set rootfol = ns.Folders(1)
Set fol = rootfol.Folders("boîte de réception").Folders("test")

For Each i In fol.Items
        If i.Class = olMail Then
                Set mi = i
                If mi.Attachments.Count > 0 Then

               dirName = "C:\Users\chadi\OneDrive\Documents\VBA\" & Format(mi.ReceivedTime, "yyyy-mm-dd hh-nn-ss ") & Left(Replace(mi.Subject, ":", ""), 20)

              If fso.FolderExists(dirName) Then
          Set dir = fso.GetFolder(dirName)
              Else
          Set dir = fso.CreateFolder(dirName)

          Dim mySpecialWordDocument As String
          mySpecialWordDocument = "C:\Users\chadi\OneDrive\Documents\Scanned Documents\CHADICV.docx"
          fso.CopyFile mySpecialWordDocument, dirName & "\" & Split(mySpecialWordDocument, "\")(UBound(Split(mySpecialWordDocument, "\")))

       End If

                For Each at In mi.Attachments
                at.SaveAsFile dir.Path & "\" & at.Filename


                Next at

                End If     
        End If

    Next i


End Sub
Codingnoob
  • 65
  • 9
  • 3
    In that case you can move your code to Outlook and set up a rule to run it when a new mail is received. https://www.pixelchef.net/content/rule-autosave-attachment-outlook or https://stackoverflow.com/questions/26225014/save-outlook-attachment-to-disk?rq=1 – Tim Williams Nov 22 '19 at 20:08
  • 1
    Possible duplicate of [How do I trigger a macro to run after a new mail is received in Outlook?](https://stackoverflow.com/questions/11263483/how-do-i-trigger-a-macro-to-run-after-a-new-mail-is-received-in-outlook) – niton Nov 23 '19 at 14:33
  • Possible responders may bypass a post if the title is of no interest to them. Try to make the title match the question. That as well means future searches do not bypass the post. – niton Nov 23 '19 at 14:36
  • @niton thank you for the link. But it seems like my code need to change in order to have it ran automatically in Outlook. And this is where I dont know where to start. When I run my code, it creates a new folder for every e-mail and store it's attachments inside the new created folder. It also add an existing word document to it. The way my code is set up, Will I need to change a lot of the core to match the automation of Outlook? Sorry for my english. – Codingnoob Nov 23 '19 at 21:29
  • Signatures, logos and images all count as attachments for a VBA macro. I have never investigated how Outlook can tell the difference between these attachments and those explicitly added by the sender. Perhaps you can recognise the interesting attachments by their extension. – Tony Dallimore Nov 25 '19 at 09:48
  • How many emails with interesting attachments do you get per day? Before I retired, I received many such emails every day. With your macro I would soon have had hundreds of folders and I do not know how I would have found the one I needed. Are you sure this is a good idea? – Tony Dallimore Nov 25 '19 at 09:49
  • The question referenced by niton is relevant but I do not find the accepted answer very helpful. However, I agree that using a new item event is the most obvious approach. Look at this answer of mine: https://stackoverflow.com/a/53035703/973283. The question is slightly relevant. My answer explains there are four methods of selecting emails for processing. It discusses how to code a processing routine that will work with any of the four methods. – Tony Dallimore Nov 25 '19 at 09:50
  • For your new, Outlook routine you will need most of the statements before `For Each i In fol.Items` although, since your new code is within Outlook, you do not need `ol`. The code within the For-loop will form the rest of the code. If you make `i` the name of the MailItem parameter, it should work unchanged. – Tony Dallimore Nov 25 '19 at 09:51

0 Answers0