Excel VBA Automation Project | Send Reports and Reconcile Responses | 2-Step Workflow
We are going to build an end to end project designed to address a real-life business problem. How to send information out to team members and how to collect back responses?
Imagine we are in the Accounting Department and there are a lot of overdue invoices. And our biggest question is? Are we are going to get the money back or do we need to write these invoices off. So, to find out,
we will split our master dataset of overdue invoices into individual reports for each account,
email each of our offsite Sales Team member the report related to their accounts,
they will need to confirm the statuses of the invoices within that spreadsheet,
send the file back to us,
and we will need to compile and reconcile all their responses back to our main data set.
Skills covered in this video:
Advanced Filter
FOR Loop
Locking and Unlocking Files
Range.Resize and Range.Offset
Send Emails & Extract Attachments
Vlookups using Find function
Download Code from my blogsite:
https://skillsandautomation.com/proje...
Download Macro File from Github:
https://github.com/skillsandautomatio...
Instructions on how to Download the Macro File from Github:
https://skillsandautomation.com/how-t...
Links to Deep Dive Videos on topics covered in this video
1. Lookups using Find Function
• A Practical Introduction to Using FIN...
2. Lock Unlock - Files and Worksheets
• Excel VBA | Lock & Unlock Excel Files...
3. Split and Merge Data
• Excel VBA || Split and Merge Files
4. Two ways to download attachments
• Excel VBA | Two Ways to Download Atta...
00:00 Intro
01:27 Demo
05:31 High Level Flow
08:45 Outbound (Send) Macro: Create Parent Sub
12:17 Outbound (Send) Macro: Find Unique Values
14:25 Outbound (Send) Macro: Load List of Comments
17:18 Outbound (Send) Macro: Create FOR Loop
19:10 Outbound (Send) Macro: Isolate Data
22:18 Outbound (Send) Macro: Create Individual Files
27:54 Outbound (Send) Macro: Get Email Id
30:48 Outbound (Send) Macro: Send Emails
33:20 Outbound (Send) Macro: Test Part 1
34:17 Inbound (Receive) Macro: Create Parent Sub
35:15 Inbound (Receive) Macro: Extract Attachments
40:45 Inbound (Receive) Macro: Compile Data
47:24 Inbound (Receive) Macro: Reconcile Responses
