You are here

Spreadsheet to quickly rename .EML files

Research articles : 
Available for download is a spreadsheet which can be used to quickly rename .eml files, which are native file format emails used in certain email software e.g. Gmail. 
 

 
The spreadsheet contains a macro that can quickly rename the .eml files according to their date, time, sender, and email subject line, with the option of arranging the data in the file names such that the files sort into date order.
 
It is common for lawyers (and others) to receive copies of .eml files to be reviewed for discussion. The recipient may put those .eml files into a folder in Windows Explorer, and those files will often default to having names based on the subject lines of the emails. But it may be preferred to give those files more useful names e.g.
 
         2022-08-17_Email sent 23h44.36 by John Smith (Meeting tomorrow).eml
 
The settings page allows flexibility as to how the file is named i.e. the following terms can be presented in any order, with any constant specified text between them.
  • {Year}
  • {Month}
  • {Day}
  • {Hour}
  • {Minute}
  • {Second}
  • {SenderName}
  • {SenderEmail}
  • {EmailSubject}
Note: Modern versions of Excel are very protective in shielding users from files with macros, and you may need to save the spreadsheet in a "trusted location" in order for the macros to run. See "Security Issues" section below.
 
Put the .eml files to be renamed into a folder in Windows Explorer
The .eml files must be in Windows Explorer for the macro to rename them, so if the .eml files were received as email attachments then drag and drop them from the email message into a Windows Explorer Folder.  
 

Renaming all .eml files in the folder

To have the spreadsheet rename all of the .wml files in a Windows Explorer folder, put the name of the folder onto the Windows clipboard e.g. by going to the folder in Windows Explorer and then:
  1. Highlighting the folder path by clicking on the address bar in Windows Explorer.
  2. Pressing Ctrl + C to copy that path to the Windows clipboard, or right click and select “Copy” to achieve the same result.
With the folder path on the Windows clipboard, open the renaming spreadsheet. The macro will be triggered automatically as the spreadsheet opens, all of the .eml files in that folder will be renamed by date and description, and then the spreadsheet will automatically shut itself down.
 
Any files in the folder that are not .eml files will be ignored, and will not be harmed.
 
The macro will not change the names of any .eml files in subfolders i.e. only the current folder will be processed if renaming by folder path. But if you wish to process .eml files that are in subfolders then you can perform a search (using the search field at the top right in Windows Explorer) in the library for ".eml" and then copy the paths of the search hits to the clipboard and then the macro will rename all of those files. 
 

Renaming select .eml files in a folder

In some cases one may wish to rename just select .eml files in a folder e.g. certain .eml files may already have been given custom names which the user does not want altered.
 
To rename just specific .eml files in a folder, put the paths of those files onto the clipboard:
  1. Highlight the .eml files in Windows Explorer (e.g. by holding down the Ctrl key and clicking on the files to be renamed).
  2. In Windows 11, right click on one of the highlighted messages and then select “Copy as path” from the right click menu. In Windows 10, hold down the Shift key, when right clicking on one of the highlighted messages, and that will ensure that the “Copy as path” command appears on the right click menu.
With the .eml file paths on the Windows clipboard, open the renaming spreadsheet and the file names of the .eml files whose paths were on the clipboard will be renamed.
 
In summary regarding how the macro processes clipboard contents: the macro checks whether the clipboard content is a folder path, or whether it is one or more file paths, and if it is a collection of file paths then the macro checks through those file paths and renames any that are .eml files. If on the other hand there is a folder path on the clipboard then the macro looks through that specific folder and renames all .eml files in that folder. 
 

How the macro deals with duplicate .eml files

If there are duplicate copies of the same .eml file when the renaming process is run the macro will put number suffixes on the duplicate copies, and then the user can delete the .eml files with suffixes in their names if appropriate.
 

Consider pinning the spreadsheet to the Excel icon on the taskbar

The renaming spreadsheet can be saved anywhere on your computer and opened just like any other Excel file is opened.
 
To allow quick access to the renaming spreadsheet consider dragging and dropping it onto the Excel icon on the taskbar in Windows, which will result in the renaming spreadsheet being pinned to the jump list that is displayed when right clicking on the Excel icon on the taskbar.
 

Editing the spreadsheet settings

How to access the settings page
The renaming spreadsheet has a settings page which allows the user to define certain aspects of how the renaming is done.
 
If there is a folder path, or file path(s), on the clipboard when the macro runs then the user will not be able to access the settings sheet i.e. as noted above the macro will run when the spreadsheet is opened, look for files to rename, and then quickly shut down the workbook.
 
To have the workbook to stay open so that the settings can be adjusted, instead of putting a folder path, or file path(s), on the clipboard, put “SettingsAdjust” on the clipboard (without the quotation marks). Note that there is no space between the two words, and no spaces before or after. If the phrase SettingsAdjust is on the clipboard then the macro will stop on the settings sheet to allow the settings to be adjusted. 
 
One can put the phrase SettingsAdjust onto the clipboard by typing it out in Notepad, in Word, in an email, or anywhere else, and then selecting it and pressing Ctrl + C to copy it to the clipboard.
 
Characters used in date formatting
 
The following format is often useful to create readable file names that sort in date order: 2022-08-17_Email sent 23h44.36 by John Smith (Meeting tomorrow).eml
 
However, that file naming format is not mandatory and on the settings page the user can design the file name for the renamed .eml files to consist of any or all of the following metadata fields in any order:
  • {Year}
  • {Month}
  • {Day}
  • {Hour}
  • {Minute}
  • {Second}
  • {SenderName}
  • {SenderEmail}
  • {EmailSubject}
At present the macro does not support use of the To, CC, or BCC fields. 
 
For example, to achieve the file naming convention suggested above, one would enter the following content in the applicable section of the Settings page:
 
{Year}
-
{Month}
-
{Day}
_
Email sent 
{Hour}
h
{Minute}
.
{Second}
 by 
{SenderName}
 (
{EmailSubject}
)
 
Replacing prohibited or undesirable characters
There are certain characters that are prohibited in file names e.g. : \ / * ? " | < >
 
Those characters may be included in email subject lines, but must be filtered out / replaced when renaming files. On the settings page of the spreadsheet one can specify what those prohibited characters should be replaced by. If there are any further characters which the user wishes to exclude from file names there are additional spaces on the settings sheet where it can be specified what those additional characters should be replaced with.
 
When the macro runs it will use the email metadata, including the date and time sent, the sender name, and the email subject line, to rename the file, but it will follow the character replacement rules specified to remove any unacceptable or unwanted characters.
 
Potential date and time error
A .eml file is actually just a text file (specifically, a text file that conforms to the Multipurpose Internet Mail Extensions protocol used by email systems) and the macro reads content out of the text file to identify the sender name, date of sending, etc. It sometimes occurs that some mail programs (seems to be mostly Macs) do not correctly encode the "Date" information. Date information should include a reference to the offeset from UTC / Greenwich Mean Time e.g. in the following Date the offset from Greenwich Mean Time is 7 hours:
 
Date: Thu, 28 Mar 2024 14:21:17 -0700
 
However, if the .eml file does not have the Date information coded correctly and shows just zeros such as in the example below then the macro may rename the file with the wrong date / time information: 
 
Date: Tue, 31 Jan 2023 00:08:16 +0000
 
You may find that email software like Outlook correctly detects that the "Date" information is incomplete and so uses "Received" or other data when displaying time information when the email is opened, but this macro simply reads out the information from the "Date" line in the .eml file and so if that is wrong then the macro will not state the correct date / time in the file name. This is just a limitation of this relatively simple software. So do keep an eye on the date and time information in the renamed files as compared to what is visible if the files are opened in email software. 
 
Options to limit the subject line length
On the settings sheet the user can specify the maximum length of characters for the subject line, and then the macro will truncate subject lines to that length, and so avoid very long file names. 
 
After editing the settings be sure to save the changes, shut down the workbook, and then put a folder path, or file path(s), onto the clipboard so that the renaming macro runs when the file is next opened.
 
If you ever want to further edit the settings, just put SettingsAdjust back onto the clipboard and open the spreadsheet.
 
Conclusion
Although the renaming spreadsheet takes a few minutes to set up with the users preferred settings, it can save a lot of time renaming .eml files and can be useful for users who like to review emails in chronological order.
 
Security issues
The spreadsheet was developed using macros (programmed in visual basic) and in order for the spreadsheet to operate your security settings in Microsoft Excel must be set to allow macros to run. 
 
Macros can pose a security risk because they make your computer perform certain steps automatically, but are typically only dangerous when made to be that way by a computer programmer with a malicious intention – hence the warning when you change the security settings in Excel. However, the macros available in the spreadsheet available for download on this page are made with good intentions and should not harm your computer. Users downloading and using this spreadsheet do so entirely at their own risk and agree to accept all risks associated with using the spreadsheet. 
 
To enable macros in Excel click:
  • File (i.e. the menu on the top left)
  • Options
  • Trust Centre
  • Trust Centre Settings
  • Macro settings
  • Enable all macros, and on that same page also check the box for "Trust access to the VBA project object model". 
Modern versions of Excel are very protective in shielding users from files with macros, and you may need to save the spreadsheet in a "trusted location" in order for the macros to run.
 
To designate the location the file is saved in as a trusted location, in Excel click:
  • File (i.e. the menu on the top left)
  • Options
  • Trust Centre
  • Trust Centre Settings
  • Trusted Locations
  • Add new location.
  • Copy and paste the path of the folder containing the spreadsheet in the dialog box to specify that folder as a trusted location.