You are here
Spreadsheet to quickly rename .MSG files
Thu, 2022-11-10 21:26 — Admin1
Research articles :
Available for download is a spreadsheet which can be used to quickly rename .msg files, which are native file format emails as stored in Microsoft Outlook.
The spreadsheet contains a macro that can quickly rename the .msg files according to their date, time, sender, and email subject line, with the data arranged in the file name such that the files sort into date order.
It is common for lawyers (and others) to receive copies of .msg files to be reviewed for discussion. The recipient may put those .msg 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).msg
UPDATE AS OF MARCH 2023: The updated file available for download has an updated settings page that allows lots of 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}
- {To}
- {CC}
- {BCC}
- {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.
Excel and Microsoft Outlook are required
To use the spreadsheet, one must have Microsoft Excel installed on the PC, and also Microsoft Outlook must be open and running on the PC when the macro runs. This is because the macros use Outlook to help extract the email metadata used when renaming the files.
Put the .msg files to be renamed into a folder in Windows Explorer
The .msg files must be in Windows Explorer for the macro to rename them, so if the .msg files were received as email attachments then drag and drop them from the email message into a Windows Explorer Folder.
Renaming all .msg files in the folder
To have the spreadsheet rename all of the .msg 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:
-
Highlighting the folder path by clicking on the address bar in Windows Explorer.
-
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 .msg 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 .msg files will be ignored, and will not be harmed.
The macro will not change the names of any .msg files in subfolders i.e. each folder must be processed individually.
Renaming select .msg files in a folder
In some cases one may wish to rename just select .msg files in a folder e.g. certain .msg files may already have been given custom names which the user does not want altered.
To rename just specific .msg files in a folder, put the paths of those files onto the clipboard:
-
Highlight the .msg files in Windows Explorer (e.g. by holding down the Ctrl key and clicking on the files to be renamed).
-
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 .msg file paths on the Windows clipboard, open the renaming spreadsheet and the file names of the .msg files whose paths were on the clipboard will be renamed by date and description.
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 .msg 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 .msg files in that folder.
How the macro deals with duplicate .msg files
If there are duplicate copies of the same .msg file when the renaming process is run the macro will put number suffixes on the duplicate copies, and then the user can delete the .msg 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 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).msg
However, that file naming format is not mandator and on the settings page the user can design the file name for the renamed .msg files to consist of any or all of the following fields in any order:
- {Year}
- {Month}
- {Day}
- {Hour}
- {Minute}
- {Second}
- {SenderName}
- {SenderEmail}
- {To}
- {CC}
- {BCC}
- {EmailSubject}
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.
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 .msg 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.
