Automate Email Attachment Processing with Power Query
Author: CA Subachandran M
Tools Used
- Excel / Power BI - Power Query
- Data Geniee
Use Case Video - https://www.youtube.com/watch?v=RhZT-DRFrbE
What we receive in email; why its important
- All ERP reports right from Tally to SAP to CRM daily emails received For Example- GRIR data for month.
- Payment Advice from bank in PDF.
- For Audit client data from Tally or ERP directly which helps on ensuring data integrity; one time work and automated for life.
- Customer or Vendor or consultant team who send standard reports; BRC report.
Process to Follow Use Case
Access Mailbox - Securely connect Power Query to your Outlook account.
Fetch Emails - Extract email data, including attachments, using Power Query.
Filter Attachments - Identify and isolate the relevant email attachments.
Open Power Query Excel
Connect to Outlook
Paste the following in to formula bar = Exchange.Contents(“your email id xxxx@xxx.xxx")
Connect to Outlook
Click sign using Microsoft account, Generally your email is already signed in your system or in MSOffice.
Expand the content of email
Post sign in you will see a table like below showing the contents of the email. Click on the Data double arrow to expand the table.
Show the email ids
Click on the Data double arrow to expand the table For the email ids To, CC, Bcc. This helps on filtering specific Emails ids from or to or bcc items.
Show the Attachments
Click on the double arrow to expand attachments of the Emails. This will show the attachment names of the email along with attachment type.
Filter the attachments
Filter the attachment type xlsx or xlsb or csv or pdf etc., Also you can filter attachment full name, starts with or any Combination like regular excel filter.
Parse Attachment Data
Attachment Type - Determine the file format of each attachment. Xlsx ; Xlsb ; CSV; Pd
Content Analysis - Interpret the data within the attachments.
Metadata Extraction - Capture relevant metadata, such as file name and size.
Expand the attachments
Click the double arrow (Down side facing), to expand the Contents of the attachements i.e pages if pdf or sheets or tables if Excel files.
Select the sheets or pages required
Select the specific sheet or table you want to expand.
Expand the selected sheets or table
Click the double arrow of the table shown for the Selected page or sheets, this will show the contents of Tables i.e for example if I selected a sheet or table Of excel, it will show rows and columns of the excel sheet selected.
Close and load to excel file
Click the Close and load in the left top corner to load the Selected sheet data to your excel file in a new sheet.
Append and Consolidate
Combine Attachments - Merge the parsed attachment data into a single dataset.
Standardize Format - Ensure a consistent data structure for further analysis.
Enrich Data - Add any additional context or metadata as needed.
Refresh in future to get updated data
Right click on the loaded table and click refresh to get any New files received via email in the same format or From same email or with same subject or any condition You specified in earlier steps in power query.
AI Powered Data Analytics to get insight
Load to Genieepro’s DataGeniee
Login to Genieepro.org - Securely transfer the consolidated data to Genie.
Upload your excel file - Securely transfer the consolidated data to Genie.
Analyse effortlessly - Leverage Genie's powerful analytics capabilities.
Sign up / Login to Genieepro
Click https://genieepro.org To login to Genieepro to access Data Geniee, An AI Powered Data Analytics Platform.
Follow Guidelines
Once you login, Datageniee guidelines will be displayed, You are required to follow instructions to get accurate Output.
Upload your excel file
Click on browse files from device to select the excel file And click open., Not only Power query files, Any excel, csv Files can be loaded with table structure data. i.e Headers, rows and columns.
Upload your excel file
Once loaded, Click Upload button to upload the selected Files data. Post that you will get upload successful message.
Select Sheets
Select sheets from drop down for the sheet(s) you want to analyse. You can select up to 10 sheets at a time. Once sheet selected , you will be shown top 5 lines of the selected table to get the glimpse of content.
Ask your Questions and Get Insights
Ask your questions related to your loaded data and Get answers or insights you are looking for in real time. The insights can be simple explanations, table or Charts visualisation. You can ask simply provide chart or Specific commonly used chart name like, Bar, Line, Pie, scatter plot, histogram etc., or even table format.
Benefits of Automated Email Attachment Processing along with AI powered Data Analytics
- Time Savings - Eliminate manual email attachment handling.
- Improved Accuracy - Reduce the risk of human error in data processing.
- Enhanced Productivity - Focus on strategic tasks instead of repetitive work.
- Scalable Solution - Handle growing volumes of email attachments efficiently.
- Natural Language AI Powered Analytics No Need of knowing complex formulas or spending Long hours with excels to prepare for presentation or meetings, Simply ask, get insights, tables, visuals and save time.
