. Introduction

1.1 Purpose

This document outlines the Automated ITR Document Management System, a VBA-powered Excel tool designed to streamline the renaming, compression, and sharing of ITR-related documents generated by Genius Software. The tool eliminates manual file renaming, reduces errors, and enhances efficiency in client communication.

1.2 Scope

The system automates:

✅ File & Folder Renaming (from complex Genius Software names to client-friendly formats)

✅ Compression (converting folders into ZIP/RAR archives)

✅ Email Composition (auto-generating client emails with attachments)

1.3 Tools Used

1. Microsoft Excel (VBA Macros)
2. WinRAR (for compression)
3. Google Chrome (for email automation)
4. AI Assistance (ChatGPT, DeepSeek, Claude AI for VBA code generation)

2. Current Challenges

2.1 Problem Statement

1. Genius Software generates files/folders with complex, non-intuitive names (e.g., 26AS_AVVPS5191C_2024.pdf /            AIS_AVVPS5191C_2024 / Computation_AVVPS5191C_2024 / ITR-V_AVVPS5191C_2024 / ReturnForm_ITD_AVVPS5191C_2024 / TIS_AVVPS5191C_2024).
2. Manual renaming is time-consuming and error-prone.
3. Sharing multiple files requires manual compression and email drafting.

2.2 Impact

1. Time wastage in repetitive tasks.
2. Risk of miscommunication due to unclear file names.
3. Inefficient document management before sharing with clients.

3. Solution Overview

3.1 Automated Workflow

1. Update Links → Scans folder structure and lists files.

Generates the Updated links

2.Generate New Names → Converts complex names into client-friendly formats.

its generates new names on single click.

3.Change Name → Renames files/folders in bulk.

Older status of folder

New names after execution of macro

1. Convert to ZIP/RAR → Compresses folders for easy sharing.

1. Compose Email → Opens Gmail with a pre-filled template.

3.2 Key Features

4. Detailed Functionality

4.1 Macro: UPDATE LINKS

Purpose: Scans a root folder and lists all files/subfolders in Excel.

Steps:

1. Check if Workbook is Saved → Prompts user to save if unsaved.
2. Folder Selection → Opens a folder picker dialog.
3. List Files/Folders → Populates columns:
4. Column A: File/Folder Name
5. Column B: Full Path
6. Column C: Hyperlink (clickable)
7. Column F: File Extension
8. Error Handling → Skips inaccessible files.