Automate Your Work
Download and organize attached reports in Outlook while you sleep using Macros - VBA Scripts and automate working with Microsoft Excel using Python.
Download reports while you sleep, and use Python to automate working with Microsoft Excel
There’s nothing better in life than finding a shortcut, especially when it’s for something tedious at work. In my current role, I spend a great deal of time analyzing data and creating, completing, and distributing Microsoft Excel reports. This includes customer sales, labor data, pricing, participation, and much more. A great deal of my mornings for the past several years has consisted of me staring into dual monitors wishing to automate this process.
●●●
Recently, I completed a class for Python at Jefferson State Community College and discovered all sorts of useful Python libraries. One Python library in particular that piqued my interest is Openpyxl. Openpyxl is a python library to read/write Excel xlsx/xlsm/xltx/xltm files. There are others that are similar, but this one does exactly what I need and maybe it will for you too.
●●●
My company utilizes KRONOS for labor management among other management systems. In these systems, you can have different reports emailed to you whenever you’d like. I have several sent to me overnight each day reporting on the previous day. This is where utilizing Microsoft Outlook VBA scripts and Python’s Openpyxl library comes into play…
Download & Sort Email Attachments Automatically
STEP 1: Enable Macros in Microsoft Outlook.
Microsoft Warning: Never enable macros in Microsoft 365 unless you're sure what those macros do. Unexpected macros can pose a significant security risk.
● STEP 2: Press Alt F11 to open Microsoft Visual Basic for Applications
● STEP 3: Go to View - Project Explorer. This will open up the panel with all of the details of the project.
● STEP 4: Right-click on Modules in the left panel. Go to Insert > Module. A blank Module window will open.
● STEP 5: Copy and paste the VBA script below in the module window to automatically download attachments, add the date to the name, and save them to a designated folder. I recommend saving them to a cloud service directory for access anywhere if possible. I use Microsoft One Drive. Go to File > Save VBAProject.OTM
● STEP 6: Create a new Outlook rule to run the script downloading the attachments automatically
File > Manage Rules and Alerts > New Rule > Apply Rule on Messages I Recieve > Next
Either choose “from people or public group” or “with specific words in the subject” > Next
move it to the specified folder > run a script. (Select the script we’ve created below) > Finish
Public Sub YourNameForTheScript(itm As Outlook.MailItem)
Dim strSubject As String, strExt As String
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim enviro As String
enviro = CStr(Environ("Cprickett"))
saveFolder = enviro & "C:\Users\TheLocationWhereYouWouldLikeToSaveTheAttachments\"
For Each objAtt In itm.Attachments
DateFormat = Format(Date, "mm-dd-yyyy ")
file = saveFolder & DateFormat & objAtt.DisplayName
objAtt.SaveAsFile file
Next
Set objAtt = Nothing
End Sub
Copying and Pasting Data Between Excel Files
STEP 1: Copy the Python code from my GitHub GIST HERE. Paste into your file on Visual Code Studio or wherever you choose to work with Python
STEP 2: Locate the Excel files you would like to work with. Copy and paste the location of the file
from which you would like to copy data and the file to which you would like to paste data.
wb = openpyxl.load_workbook("/THE LOCATION FOR YOUR EXCEL FILE/")
STEP 3: Type the name of the sheets for each of the files you are working with
sheet = wb.get_sheet_by_name("FY23") #Add Sheet name
STEP 4: Select the columns and rows you want to copy and paste between the two files.
STEP 5: Choose the location and name you would like to save the file as and include it in the lower
portion of the code.
template.save("/THE LOCATION AND FILE NAME FOR WHICH YOU WANT SAVED/")
STEP 6: That’s It. You’re set. Just change the columns and rows you want to copy and paste.
You’re all set. Run your code. Check your Excel file to make sure it’s executing properly. For more, visit Openpyxl - Simple Usage