VBA Script for Outlook Email & Calendar Data

Create a VBA script that extracts all available data from emails and calendar events in Outlook desktop and stores it in a local MySQL database. The script must efficiently handle email and calendar data, ensure all records are logged, and provide the ability to call its core functions with custom parameters. Requirements Overview 1. Data Sources: o Emails: Extract all available data from each email, including metadata like date/time, subject, body, sender, and recipients. o Calendar Events: Extract all available data from each calendar event, including recurring events that need to be treated as individual instances. 2. Time Storage: o All date/time fields must be converted to and stored in UTC. o Attachments do not need to be saved. 3. Database Schema: o All data must be stored in a local MySQL database. o Normalization should be applied to the database structure, which includes separate tables for Emails, Events, and People. o Log Table to capture errors or issues encountered during execution. 4. Logging: o The database must include a log table for general operation logs. o In the event that a write to the database log fails, errors must be logged to a local text file. 5. Data Handling and Deduplication: o Emails and Calendar Events must be uniquely identified and updated if they already exist in the database.  o Any duplicated entry based on unique identifiers should be overwritten. Functional Requirements 1. Core Functions • There will be two main functions: one for handling Emails and one for handling Calendar Events. • Each function should be designed in a way that allows modularity and can be easily called with specific parameters from other VBA scripts. 2. Calendar Event Handling: • The function should extract all available fields. • Recurring events should be treated as individual rows, meaning each instance of a repeating event should be recorded separately. • Parameters for Calendar Function: o Start Date: Beginning date of the timeframe to retrieve events. o Stop Date: End date of the timeframe to retrieve events. o Link to Calendar: The specific calendar being queried. o Tag: A custom text tag to be appended to each row inserted in the calendar event table. o Max Number of Events: The maximum number of events to process. 3. Email Handling: • The function should extract all available fields. • Parameters for Email Function: o Start Date: Beginning date of the timeframe to retrieve emails. o Stop Date: End date of the timeframe to retrieve emails. o Email: The email address of the account to be queried. o Tag: A custom text tag to be appended to each row inserted in the email table. o Max Number of Emails: The maximum number of emails to process. 4. Database Structure • Emails and Calendar events must have a unique identifier to ensure data consistency and prevent duplication. • Email Table: o Includes fields for Subject, Body, Date/Time (UTC), and Tag and every other field that can be extracted from the email object. o Reference to People Table for senders and recipients. • People Table: o Includes details of people involved in both emails and calendar events. For example, email address (this is the identifier), First name, Last name, and company (basically what can be extracted). o Senders, Recipients, Organizers, and Attendees are all stored here, and Emails and Events reference this table. • Calendar Event Table: o Includes fields for Event Name, Date/Time (UTC), Recurrence Info, and Tag and every other field that can be extracted from the event object. o Reference to People Table for organizers and attendees. Error Handling • If an error occurs while writing to the MySQL database: o Attempt to write the error message and details to the Log Table. o If logging to the database fails, write the error message and details to a local text file named error_log.txt. General Requirements • Unique Identifiers: Both emails and calendar events must include a unique identifier to determine if the item already exists in the database for overwriting. • Data Overwrite: If an email or calendar event already exists in the database, overwrite the existing entry with the new data. • Modular Functions: The calendar and email functions must be written to allow easy reuse and parameter customization in future scripts. This ensures that new functions can be created and tailored with specific parameters without major code changes. • VBA code is to be commented extensively. • Testing: You are responsible for testing, so, when you deliver the result, we expect it to work (some glitches can happen, but overall we expect it to work). • Delivery: This needs to be delivered Friday, November 15 2024. • Questions: If you have any questions, these needs to be asked prior to the start of the contract. • Pricing: We will pay a fixed price. What we need from you • Database schema • VBA script • Instructions on how to test. If you can do this, let us know. We would like to get started Om Monday, January 11 2024.