SQL Server to MS Exchange and Office integration via VBA form

In advance of a major upgrade to the CRM function at Performance Research Centre the business is undertaking a proof of concept (POC) exercise to assess potential candidates for the main project and confirm the feasibility of integrating and automating a number of functions between the SQL back-end, MS Exchange and MS Office via the in-house VBA front-end. POC Development will take place locally using VBA / MS Access 2010 and via remote desktop access to a single server running: * Microsoft SBS Server: Exchange 2008 * Microsoft SQL Server 2005 The project is due to start in approx two weeks time. The exact start date will be decided over the next week. We look forward to receiving your bid submissions. ## Deliverables The project summary explains the purpose behind the proof of concept and how development will take place. Below please find detailed requirements which outline some tools that the could be helpful for the work and then provides details of each task that needs to be completed. Please review the attached documentation which includes for reference a wireframe and set of database tables. 2. Additional Tools and Components In order to achieve the required level of integration with MS Exchange and Office our research has indicated that the use of two particular software toolkits and a component called MAPIProp will be required to meet our objectives. If candidate developers are aware of other tools and/or components they are invited to utilise them provided they have given us prior notice, all licensing requirements are understood and we have approved their use within our system. Information on two software toolkits and component as follows: ? VB Outlook Security: [login to view URL] A very helpful article which takes one through how you avoid the security message and outlines the details of potentially useful toolkits (below). ? MAPIProp: [login to view URL] designed to overcome the security permission messages that occur when calling an MS Exchange or Outlook from another application. ? Redemption: [login to view URL] A tool that circumvents the MS security object and opens up access to a wide range of outlook functionality. ? OutlookSpy: [login to view URL] A developer tool kit that allows one to view Outlook features, objects and properties. 3. MS Exchange and Office Integration Requirements Combining these tools with the relevant VB, VBA and MAPI development experience should allow us to satisfy the core e-mail, calendar and document creation requirements: ? Invoke new e-mail composition from a specified contact from the VBA Sales form, held in the SQL database in MS Outlook, via MS Exchange. ? Invoke a view of all mail for a specified contact from the VBA Sales form, held in the SQL database in MS Outlook. ? Send an automated e-mail to a pre-defined recipient following a field update. The details of the recipient and updated field both being held within the same SQL database. ? Create a new automated appointment (calendar entry) in a shared MS Exchange calendar, for presentation in MS Outlook, following an update within the MS SQL database. ? Update a value in the SQL database from an update to a calendar entry on the shared MS Exchange calendar, via MS Outlook. ? Create a new appointment (calendar entry) in a shared MS Exchange calendar, presented in MS Outlook, following a user request via the POC form. ? Create a new MS Office document via a pre-defined template using data from the SQL database. 4. Basic Schema and Form The basic schema needed to fulfil all of the proof of concept tasks has been created and populated with an appropriate data set. A wire frame has been created outlining the layout and design of the POC form, which will be created in order to prove and demonstrate the functions described in this speciation. See Appendix 1: POC Form Wireframe and Appendix 2: POC Schema 5. General Form Requirements ? Create three tabs to display data ? Search for and select a company ? Select one or more products and assign them to a company ? Validate and define the term (length of time) for the product subscription. ? Display the address of the company 6. POC Form: Company Search & Selection The POC form will allow the user to either search for a company or use the dropdown field to scroll through all companies alphabetically (AtoZ). The search function should be invoked when the user enters one or more characters into the (dropdown) field and display the nearest matching company. ? If no match is found and the user hits the return key they will be presented with a message stating 'No Match Found. Please Revise Your Search'. ? Where a match is found and the user hit the return key the form will be populated with the details for the selected company. When used via the dropdown AtoZ function the user will scroll down and select the company by highlighting a company and using the return key. This action will populate the form with details of the selected company. Both methods of selection will also populate the POC form with the Sales Exec associated to the company in question. The data required to fulfil this task is located in: ? tblCompanies / intCompanyID / StrCompanyName / IntSalesPerson See Appendix 2: POC Schema for further details of the related fields. 7. People Tab Create a tab which includes a grid populated with all contacts (people) associated to the selected company and a set of four function buttons. 7.1. People Table The people grid will allow the user to select a row representing a specific contact. 7.2. New e-Mail Message: Create E-Mail From the people grid the user will be able to select a contact and invoke a new e-mail message in MS Outlook. The new e-mail message will be addressed to the contact, using their e-mail address, and include the relevant salutation + contact name within the e-mail body. The create e-mail function will utilise the following: ? User's session ID to ascertain their identity and associated MS Outlook profile. User Session ID, ADUserName and StrEmployeeName ? Contacts E-mail address for the selected contact. e-mail address: tblPeople / StrEmail ? Salutation and contacts first name to populate the e-mail body text. For 'Dear X' and salutation: tblPeople / intPersonID / StrSalutation / StrFirstName 7.3. Contact e-mail History: View E-Mail From the POC form the user will be able to select a contact and invoke a command to search for and display all e-mails from/to the selected contact in question. The view e-mail function will utilise the following: ? User's session ID to ascertain their identity and associated MS Outlook profile. User Session ID, ADUserName and StrEmployeeName ? E-mail address for the selected contact. e-mail address: tblPeople / StrEmail By default the e-mail search should be made against the users MS Exchange / Outlook profile Inbox and Sent Mail. If feasible the user should be presented with the option to extend the search to include their mail archive (PST). The path for the users ST will be: tbd 7.4. New Outlook Calendar Entry: Create Appointment The new appointment function should present the user with the ability to select a date from a calendar picker. The calendar picker can be native to the form or utilised from MS Exchange / Outlook. The developer is invited to present the most appropriate option and explain their rationale accordingly. Notwithstanding the date selection, the creation of a new appointment should invoke the users MS Outlook application and present the user with a new appointment form which includes a subject line displaying the selected contacts name. The creation of a new appointment will require the following: ? User's session ID to ascertain their identity and associated MS Outlook profile. User Session ID, ADUserName and StrEmployeeName ? Name of the selected contact: tblPeople / strPerson See Appendix1: Wireframe 1 People Tab. See Appendix 2: POC Schema. 7.5. Create Document The create document function when invoked will initiate the creation of an MS Word document based on a pre-defined template. The template will include fields relating to the selected Company, Company Address, Contact and Sales Exec associated to the Company in question. The creation of a new MS Word document will require the following: ? The identity of the Sales Exec and associated with the Company: tblCompany / IntSalesPerson ? Details of the Company and Contact: tblCompany / intCompanyID and intCompanyID / tblPeople / intPersonID / strPerson ? Details of the Company Address: tblCompany / intCompanyID and intCompanyID / tblAddresses / intAddress1 (2 &3) / intTown / intPostcode / intCountry The template will reside in: path tbd 8. Product Tab Create a tab which includes a grid based product selection mechanism, a grid populated with all products associated with the selected company and two function buttons. 8.1. Product Selector Table The left hand side of the tab will present the user with a grid of all products which include selection boxes and an Add Selected Products function. The Add Selected Products button when invoked will associate all of the selected products to the selected company. The product selector function will utilise the following: ? The product list: tblProducts / intProducts ? Products to be associated to the company via the creation of a subscription using a new intSubscriptionID within tblSubscriptions. The intSubscriptonID will be associated with the relevant intProductID and relevant intCompanyID. ? The subscription start date, held in tblSubscriptions / datStart will be set to the next working day (based on a Monday to Friday working week) from the subscription creation date. ? Each subscription will also be associated to an intMonth value which represents the length of time (term) that the company will have access to the product. The user will be required to set this value once the product has been assigned to the company. The act of assigning products to a company will present the user with a message reminding them to set the term value for product subscription. The message will read: "Please remember to set the Subscription Term value for all Products" 8.2. Company Product Table and Term Validation The company product grid will display all products associated with the selected company, including any products transferred via the Add Selected Products function. The company product grid will allow the user to set the subscription term that relates to the product in question. The default subscription term for the assigned products will be null, presented to the user in the dropdown as 'Select Term'. The term field will be validated and required the user to set a value before they can navigate away from the product tab. If the user tries to move away from tab before they have confirmed the term the user will be presented with a message that reads: "You must set a term value for all assigned products" 8.3. Automated E-mail Generation from Field Update The action of adding a product to a company and setting the term value will update the tblSubscriptons with details of the new product subscription and its term. The creation of a new subscription will initiate the creation of an automated e-mail to the Sales Exec associated to the Company in question. The automated e-mail will require the following: ? The identity of the Sales Exec and associated with the Company: tblCompany / IntSalesPerson ? The E-mail address of the Sales Exec associated with the Company: tblEmployee / IntEmployeeID / StrEmployeeEmail ? The details of the product associated with the Company: o tblSubscriptions / intSubscriptionID / intProductID and tblProducts / intProductID / intproduct The automated e-mail to the Sales Exec will contain the following information: ? Subject Line: [Company Name]: New [Term Value] Subscription for [Product Name] ? Body Text Line 1: [Company Name]: New [Term Value] Subscription for [Product Name] ? Body Text Line 2: Subscription Start Date: [datStart] (in dd/mm/yyyy format) ? Body Text Line 3: Subscription End Date: [calculated from datStart and intMonth] (dd/mm/yyyy format) 8.4. Automated Calendar Entry from a field Update The creation of a new subscription will initiate a new appointment in a shared MS Exchange calendar for display in MS Outlook. The new appointment entry date will be the same as the subscription start date. If feasible the entry of the new appointment should update a suitable field within the MS Exchange record that will identify the entry as a new subscription appointment. See section E in this document for more details. The new appointment subject line will include the Sales Exec, Company, Contact and Product name, entered into the subject line as: ? [Sales Exec name] New Subscription for [Product] for [Contact name] at [Company name] Automated new appointment entry will utilise the following: ? Identity of the Sales Exec and associated with the Company: tblCompany / IntSalesPerson ? Details of the subscription associated to the product and company:tblSubscriptions / intSubscriptionID / datStart ? Details of the product associated with the Company: tblSubscriptions / intSubscriptionID / intProductID and tblProducts / intProductID / intproduct ? Details the Company and Contact: tblCompany / intCompanyID and intCompanyID / tblPeople / intPersonID / strPerson 8.5. Updating a Subscription Start Date from MS Outlook Calendar If we can identify an MS Exchange calendar entry as being related to a subscription, we would like to explore the idea of using the identifier to help facilitate the updating of subscription records help in the SQL database from a user action in the shard MS Outlook calendar. Ideally, the action of changing a subscription start date calendar from the originally entered date to a new date entry should update the subscriptions start date within the SQL database. 9. Address Tab Create a tab to display the addresses associated with a company. The address shown should be concatenated from address fields 1, 2, & 3 and the postcode. No other functionality is required for this tab. The address tab will utilise the following information: ? Details of the Company Address: tblCompany / intCompanyID and intCompanyID / tblAddresses / intAddress1 (2 &3) / intTown / intPostcode / intCountry and tblAddresses /StrTelephone 10. Exit Function The exit function should be presented on all tabs. Invoking the exit function should check that no fields a locked and close the form. NB: Also see attached document for wireframe and table descriptions.