Excel 2013 and Outlook 2013

I can't speak in-re Outlook. but in general:

Consider how you will import the responses into Excel:

Will you import the returned forms into a common folder and read them from there?
What will you do with the returned Forms after you import the responses?
How often will these be sent out?

Since I am basically an Excel VBA programer, I came up with this attachment as a starting point.

It has all the details needed to add employees to the form and programatically add and read information into and from the form.

Note that I would use both Row and Col offsets = 0 in any code in order to provide "Code Smell" visual indicators.

I would suggest that, rather than have a manager indicate new employees in some comment cell, that you include a few blank EmployeeSections at the bottom of the form.

I left the Grid visible to show how I laid out the form. All text inputs and labels Cells have been Merged, so it is important that all FormDescriptors reference the Top-Left cell of all merges.

Sample code to reference the Support Staff CheckBox of the third employee (EmpSectionNumber = 3) listed on the Form. This would work for any number of Employees/Sections.

'Assumes the code is tracking the number of sections on the Form
Range(EmpSectionsStartCell).Offset _
(((EmpSectionNumber - 1) * EmpSectionRowOffset) + EmpPositionRowOffset), _
SupportStaffColOffset)

If you ever modify the layout of the form, merely edit the Form Descriptors to suit. I would use an Enum in the code to hold them, but you can "Find" them on their sheet or use the named Ranges from the same sheet. It really depends if a user or a programmer will maintain the form.

Not just possible. Do-able!

We will assume that the 5 Practice and Manager Details columns are always up to date when the request forms are sent. However the code will verify and throw a message to the user if that fails.

The Employee name section of the Form needs to be updated to two cells, first and last names.

I suggest naming the sent forms "Employee Update Form|DateString-PracticeID_ManagerName.xls"

Have an OutLook programmer add code to OutLook that sends all attachments named Like "Employee Update Form|*" to a folder named "Employee Update Returns" located in a fixed location on your file storage system. Perhaps under the folder that holds the Master spreadsheet.

Note the format of the suggested sent form name; there are five different separators in it, "|", "_", "-", and ".". This allows all code to differentiate the different parts of the name. Only the first part is fixed, (for the use of the OutLook code.) The PracticeId and ManagerName parts allow the check in Para2 above. The DateString part allows the returns to be permanently stored in the "Employee Update Returns" folder and they will be sorted chronologically. Other general file attributes will allow for the repeated running of the program against only late returns.

Anybody who codes this for you will need to know:

• The exact String (name) of the first part of the file name
• The order you want to have the Returned forms appear in the Returns folder, Chronologically, by Practice, or by Manager. This will be determined by the order the Name parts after part 1 are used.
• The full path and name of the Returns folder.
• The full path and name of the Master file.
• The full path and name of the file with the code in it. This can be the master file.

Note that I have not mentioned any manner of checking or verifying the Manager ID. Is this a requirement? This can be incorporated into the Request form. I think the Request form name is getting a bit overloaded.