asked on

How to automatically save attachments from outlook email into a designated folder that has the same name as the email subject?

It seems like there is no solution for this question using Power Automate - https://www.experts-exchange.com/questions/29198075/Power-Automate-Save-email-attachments-from-outlook-to-a-sharepoint-document-library-and-create-1-folder-for-each-email-received-with-attachments.html

Thus I am thinking of an alternative with Outlook.

I know how to create rule in Outlook but I just need the script.

The script should do the following:

Save in a specified directory "C:\Users\UserID\OneDrive - ABC\Attachment Folder"
Sub Folders created specially for the email attachments
Sub Folders Name should be same as the email subject just remove the "FW:" for example:

Email 1 - Attachments 1, 2, 3, 4, 5 with email subject "FW: Email 1 attachment ABCDEFG"
Email 2 - Attachments 6, 7, 8, 9 with email subject "FW: Email 2 attachment Place Name Purpose"
and so on .......

and the attachments shall saved in the designated folder with the folder name and attachments like this:

Folder Name = "Email 1 attachment ABCDEFG" > Attachments 1, 2, 3, 4, 5 are saved in this folder name
Folder Name = "Email 2 attachment Place Name Purpose" > Attachments 6, 7, 8, 9 are saved in this folder name
and so on. ...............

Thus one should have:
C:\Users\UserID\OneDrive - ABC\Attachment Folder
and then all the attachments saved in this format:

C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 1 attachment ABCDEFG\attachment 1
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 1 attachment ABCDEFG\attachment 2
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 1 attachment ABCDEFG\attachment 3
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 1 attachment ABCDEFG\attachment 4
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 1 attachment ABCDEFG\attachment 5

C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 2 attachment Place Name Purpose\attachment 6
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 2 attachment Place Name Purpose\attachment 7
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 2 attachment Place Name Purpose\attachment 8
C:\Users\UserID\OneDrive - ABC\Attachment Folder\Email 2 attachment Place Name Purpose\attachment 9

Thanks.

Bill Prew

This could be done. One thing you didn't address was how to deal with multiple emails with the same subject? That can certainly happen, and if they have attachments, do you want them all to "merge" into a single folder for that email subject? And how do you want to handle duplicate attachments across those multiple emails with the same subject?

»bp

Karen Falandays

Just an addendum to this: I am curious as to why you would want each in a separate folder? This will become extremely unwieldy to manage.

bluue s

ASKER

@Bill Pew - No there would not be cases where there are same subjects for each email.

@Karen Falendays - Thanks for pointing out.

Could I also add that the the creation of the sub-folder name to also do some tweaking in such a way that it states the YY-MM-DD followed by underscore _ and then the subject excluding the "Fw:"

For example,
Email subject 1: "FW: Email 1 attachment ABCDEFG 26/10" with 5 attachments.
The sub-folder name should be created as "201026_Email 1 attachment ABCDEFG"
Thus, the listing should look like:

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 1 attachment ABCDEFG\attachment 1

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 1 attachment ABCDEFG\attachment 2

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 1 attachment ABCDEFG\attachment 3

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 1 attachment ABCDEFG\attachment 4

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 1 attachment ABCDEFG\attachment 5

Email subject 2: "FW: Email 2 attachment Place Name Purpose 26/10" with 4 attachments.

The sub-folder name should be created as "201026_Email 2 attachment Place Name Purpose"
Thus, the listing should look like:

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 2 attachment Place Name Purpose\attachment 6

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 2 attachment Place Name Purpose\attachment 7

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 2 attachment Place Name Purpose\attachment 8

C:\Users\UserID\OneDrive - ABC\Attachment Folder\201026_Email 2 attachment Place Name Purpose\attachment 9

NOTE: The year will always be the year that the email is sent.

In this way, it is easier to manage.

To answer your question why are there multiple sub-folders:

Frontline staff sent multiple photos of what they sent out to the customers.
The emails are forwarded to office.
Each email can contain multiple photos.
Each email subject will always be different since it is for different customer but date can be the same.
Thus, with the date in front of each sub-folder, it is easier to sort and manage.
Office staff would like to store these photos in case of complaints.

It can also be used for other purposes, for example:
Each email subject is different.
By storing in various sub-folders with the date in front of each sub-folder, it is easy to identify the attachments for each different issues.

Hope this makes things clearer.

David Favor

This is very simple to do outside any email client.

Said differently, you'll use the right tool for the right job.

Outlook + VBA... ouch... wrong toolset for this job...

1) Use the openssl command line client to simulate an IMAP4 session.

2) Sort all mail you might operate on into a separate folder, as this type of processing can place a strain on IMAP4 resources if done for every message in every folder.

3) Each new message, clone the message to somewhere using openssl or also imap4sync (also another good choice).

4) Once you have your messages somewhere, you'll write some sort of code. Could be in VBA or PERL (likely far faster to code) or any scripted language, to break apart the email, create your folder/file structure, decode your attachment (because they will all be base64 encoded) into attachments with correct extensions, like .pdf or .zip or whatever.

Trivial coding. Just takes a good chunk of time getting all the moving parts working correctly.

David Favor

I do this with DMARC + Loopback Reports via this sequence.

1) All reports land in one Dovecot IMAP4 folder on a server.

2) Each hour, an rsync job on another machine runs to move all reports to where they will be processed, deleting them off the IMAP server.

If you have access to the actual backing store (where physical messages live) you can use rsync/delete rather than openssl or imap4sync.

3) At this point messages are gone from the IMAP server.

4) Then I split apart all MIME parts, running base64decode() as required.

5) At this point, then for any compressed files - .zip or .gz or .xz or .zstd - I uncompress/unarchive these files into their components, which is usually a single XML report, then process the report.

So again, trivial code, just lots of moving parts to end up with... human usable attachments.

Bill Prew

Save in a specified directory "C:\Users\UserID\OneDrive - ABC\Attachment Folder"

Do you want to hard code the <UserId> value in the VBA code, or does it need to get that from WIndows and get the current userid dynamically as each email is processed?

Email subject 1: "FW: Email 1 attachment ABCDEFG 26/10" with 5 attachments.
The sub-folder name should be created as "201026_Email 1 attachment ABCDEFG"

I would strongly suggest making the date format of the sent date from the email being processed to be YYYYMMDD format rather than YYMMDD, I think that is best practice these days. Either will work, but it's a bit more obvious.
If the first 4 characters of the email being processed are "FW: " we remove those. What about "RE: ", does that need to be handled?
I see that you removed some characters from the right of the subject also. What are the rules for that? Is it always " 26/10"? If not how do we recognize what needs to be removed from the right?

»bp

Bill Prew

Okay, here's what I got working here so far. I tried to put enough comments in so most of it should make sense. I also made a couple of assumptions pending your next reply.

I assumed you wanted the UserId of the current user on the computer (dynamically, not hard coded).
Without further info I left the trailing "26/10" on the subject. I did replace the slash with an underscore since that is not a legal file name character. (If there could be other illegal characters in the subject line you may want to replace them as well).

Sub SaveAttachmentsBySubject(NewEmail As MailItem)
    Dim NewAttachments As Outlook.Attachments
    Dim NewAttachment As Outlook.Attachment
    Dim SaveFolder As String
    Dim SavePath As String
    Dim FSO As Object
    Dim BaseFolder As String
        
    ' Base folder for all attachments
    BaseFolder = "C:\Users\" & Environ("USERNAME") & "\OneDrive - ABC\Attachment Folder"
    
    ' Get attachements to this email
    Set NewAttachments = NewEmail.Attachments
    
    ' If no attachments then we're done
    If NewAttachments.Count = 0 Then
        Set NewAttachments = Nothing
        Exit Sub
    End If
    
    ' Build folder for attachments from subject and sent date
    SaveFolder = BaseFolder & "\" & Format(NewEmail.SentOn, "yyyymmdd") & "_" & Replace(NewEmail.Subject, "FW: ", "", 1, -1, vbTextCompare)
    SaveFolder = Replace(SaveFolder, "/", "_")
    
    ' Create folder if it doesn't exist
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If Not FSO.FolderExists(SaveFolder) Then
        MakeDir SaveFolder, FSO
    End If
    
    ' Process all attachments
    For Each NewAttachment In NewAttachments
        
        ' Add attachement file name to folder to get full path to save to
        SavePath = SaveFolder & "\" & NewAttachment.FileName
        
        ' Only save if it doesn't already exist
        If Not FSO.FileExists(SavePath) Then
            NewAttachment.SaveAsFile SavePath
        End If
    
    Next NewAttachment
    
    ' Clean up objects
    Set FSO = Nothing
    Set NewAttachments = Nothing

End Sub

Sub MakeDir(strPath As String, objFSO As Object)
' Subroutine to create a folder as well as any needed parent folders

   Dim strAbsPath As String
   Dim strParent As String

   ' Get absolute path to folder to create
   strAbsPath = objFSO.GetAbsolutePathName(strPath)

   ' If it exists already we are done
   If objFSO.FolderExists(strAbsPath) Then
      Exit Sub
   End If

   ' Get the path for the parent of this folder
   strParent = objFSO.GetParentFolderName(strAbsPath)

   ' Stop when we get to the root
   If strParent <> "" Then
      ' If this parent folder doesn't exist create it (recursive to create any additional parents needed)
      If Not objFSO.FolderExists(strParent) Then
         MakeDir strParent, objFSO
      End If
   End If

   ' Now create this folder
   objFSO.CreateFolder (strAbsPath)

End Sub

Open in new window

»bp

bluue s

ASKER

Save in a specified directory "C:\Users\UserID\OneDrive - ABC\Attachment Folder"

Do you want to hard code the <UserId> value in the VBA code, or does it need to get that from WIndows and get the current userid dynamically as each email is processed?

=Yes, dynamic. Good thinking.

Email subject 1: "FW: Email 1 attachment ABCDEFG 26/10" with 5 attachments.
The sub-folder name should be created as "201026_Email 1 attachment ABCDEFG"

I would strongly suggest making the date format of the sent date from the email being processed to be YYYYMMDD format rather than YYMMDD, I think that is best practice these days. Either will work, but it's a bit more obvious.

=Yes, I know. But human behaviour is harder to change than computer coding...sometimes forget, can't be bothered to type.... (not that I am an expert in coding but er.. guess know what I mean)

If the first 4 characters of the email being processed are "FW: " we remove those. What about "RE: ", does that need to be handled?

= Yes, Remove "RE: " as well. Thanks.

I see that you removed some characters from the right of the subject also. What are the rules for that? Is it always " 26/10"? If not how do we recognize what needs to be removed from the right?

= Good question.
If there is no numbers like date ie. "26/10" then just use the whole subject title as it is.

For example, if:
Email subject 1:
"FW: Email 1 attachment ABCDEFG Some subject 123" OR

"FW: Email 1 attachment ABCDEFG Some subject from some country"

Then, the corresponding sub-folder name should be created as "Email 1 attachment ABCDEFG Some subject 123" OR
"Email 1 attachment ABCDEFG Some subject from some country" respectively.

Thanks!

bluue s

ASKER

Without further info I left the trailing "26/10" on the subject. I did replace the slash with an underscore since that is not a legal file name character. (If there could be other illegal characters in the subject line you may want to replace them as well).

May I know what are the illegal characters?

Bill Prew

Per Microsoft:

Naming Files, Paths, and Namespaces - Win32 apps | Microsoft Docs

See the "Naming Conventions" section for the details, including illegal characters.

»bp

Bill Prew

As far as your feedback, I see these things I need to adjust based on that:

Use YYMMDD for the date stamp, not YYYYMMDD. That's a simple change in the Format() function.
Remove "Re: " also if found on the left of the subject.
Remove any other illegal filename characters from the subject and replace with underscore.
Remove the right "word" of the subject if it looks like a date, in the format "nn/nn" where nn can be one or two digit numbers.

I will make those changes, but you certainly could test the code I provided to see if it does what you were looking for as is.

»bp

bluue s

ASKER

Thanks, I just tested your initial code.
It went well, except that:
the email subject name is slightly incorrect.

The date in the email subject is not the same as the time of email.

For example:
Email subject 1: "FW: Email 1 attachment ABCDEFG 26/10" with 5 attachments.
The sub-folder name should be created as "201026_Email 1 attachment ABCDEFG" (CORRECT)

AND NOT:
"20201028_Email 1 attachment ABCDEFG 26_10" (WRONG)

Bill Prew

Ah, I missed that on my first read of your requirements. So if there is something that looks like dd/mm at the end of the subject remove it and use it for the MM and DD in the folder name.

What about if there is nothing that looks like dd/mm at the end of the subject, what do we use for MM and DD then?

»bp

bluue s

ASKER

Use YYMMDD for the date stamp, not YYYYMMDD. That's a simple change in the Format() function. = Yes
Remove "Re: " also if found on the left of the subject. = Yes
Remove any other illegal filename characters from the subject and replace with underscore. = Ok.
Remove the right "word" of the subject if it looks like a date, in the format "nn/nn" where nn can be one or two digit numbers. = Yes

= And also the following:

The date stamp YYMMDD in front of the sub-folder name should be the date "nn/nn" found at the end of the email subject, where it is the DD/MM format, ie. 01/10 (is read as 1st October and not be interpreted as 10th January, of course 26/10 is obvious since there is no 26th month, just to highlight for those digits 1-12.) and nn can be one or two digit numbers.

Thanks.

bluue s

ASKER

What about if there is nothing that looks like dd/mm at the end of the subject, what do we use for MM and DD then?

= Then do nothing. Just treat the whole string as the subject.

For example:
Email subject 1 in the following scenarios:

"FW: Email 1 attachment ABCDEFG Some subject 123" >> "Email 1 attachment ABCDEFG Some subject 123"
"FW: Email 1 attachment ABCDEFG Some subject from some country" >> "Email 1 attachment ABCDEFG Some subject from some country"
"FW: Email 1 attachment ABCDEFG Some subject from some country 18-196:123" >> "Email 1 attachment ABCDEFG Some subject from some country 18-196_123" (meaning if there is illegal characters, just convert accordingly, other than that, just keep the whole string as the subject)

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bluue s

ASKER

@Bill Prew. Thanks. It works !

However, why doesn't it appear in my One Drive for Business Online ?
They only appear in my PC.
Is there a special directory I should use for my One Drive for Business Online since I am using this in the script:

C:\Users\" & Environ("USERNAME") & "\OneDrive - Business\Attachment Folder"

Open in new window

Bill Prew

However, why doesn't it appear in my One Drive for Business Online?

I can't really speak to that, it sounds like a OneDrive issue. Are you sure you have syncing enabled? If you place another file in that folder using traditional approaches (drag and drop with Explorer, or Save a document to that location) does it sync to the cloud and show up there?

Also, as I recall, OneDrive allows you to configure which folders are synced between the local computer and the cloud, so check on that setting.

At any point that isn't likely anything the script is causing, the OneDrive mirror folders on the Desktop don't require anything special in the folder.

»bp

bluue s

ASKER

Got it.
How about SharePoint?
I tried with the following directory:
https://abcdefg.sharepoint.com/sites/SiteName/SiteDocumentLibName/Forms/AllItems.aspx?viewid=12f8bb42%2Daf28%2D4603%2D88fd%2Dbe7a9a910072&id=%2Fsites%2FSiteName%2FSiteDocumentLibName%2FFolderName

Where abcdefg = company name, and the rest replaced by generic names such as Sitename, SiteDocumentLibName, FolderName

but doesn't seem to work.
Does the script work with https:// or the script must be redo differently to cater for it ?
if so, I can open up another question for this.
Please advise. Thanks.

Bill Prew

Did you check the OneDrive related things I mentioned, that should work fine if configured properly?

This script can't write to a web URL address, file I/O doesn't work that way. It couldn't easily be changed to do that.

»bp

bluue s

ASKER

Did you check the OneDrive related things I mentioned, that should work fine if configured properly?

Yes that's why i said Got it in my previous reply.

This script can't write to a web URL address, file I/O doesn't work that way. It couldn't easily be changed to do that.

Ok. Thanks
Would you be able to do something similar but write to a Sharepoint Site Folder? Or that is not possible?

Bill Prew

It looks like it is possible to map a drive to a SharePoint folder, but I've never done that, and don't have access to a SharePoint site to test with.

You might talk to your SharePoint administrators, or do some googling (I found hits for "SharePoint mapped drive" no quotes) that seemed to indicate it was possible, and some examples. Once you map a drive to the SharePoint folder you can just use the mapped drive location in the VBS script.

»bp