+ Reply to Thread
Results 1 to 4 of 4

Excel 2013 and Outlook 2013

  1. #1
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Excel 2013 and Outlook 2013

    Hi,

    Is it possible to create forms in Excel which can be sent to managers automatically using outlook to collect their responses and update the table accordingly? Their email addresses are in the main spreadsheet

    The spreadsheet contains over 10,000 entries and these entries need to be sent to be verified by managers. Please see attachment which includes a mock up of sample spreadsheet (TABLE 1) and a form template (FORM 1) that I'm hoping to create in order to collect their responses. Their replies should be reflected in the main spreadsheet table (Column K to P)

    excel.docx

    Any suggestions and guidance would be welcome and appreciated.

    Thank you in advance

  2. #2
    Registered User
    Join Date
    11-13-2006
    Posts
    87

    Re: 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.
    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files
    Last edited by SamT; 03-24-2014 at 12:39 PM. Reason: typpppos

  3. #3
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel 2013 and Outlook 2013

    Thank you very much SamT for your input.

    This has at least given me some idea and glimmer of hope that it is possible, and will have to look at Outlook integration at a later stage.

    With regards to response received, ideally I'd like it to update the master worksheet (table 1 in doc previously attached ) and reflect the responses there.

    This form would than be used on an half yearly basis to update the records as needed. What do you suggest is the best way of capturing/storing the reponses in the master spreadsheet?

  4. #4
    Registered User
    Join Date
    11-13-2006
    Posts
    87

    Re: Excel 2013 and Outlook 2013

    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.
    Last edited by SamT; 03-26-2014 at 12:18 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simple formula to display a date (10/19/2013) as text (October 2013)
    By benyben123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2013, 03:47 AM
  2. excel 2013 and autocad 2013 LT
    By drailed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-25-2013, 03:05 PM
  3. Replies: 3
    Last Post: 09-12-2013, 06:23 PM
  4. Link Excel 2013 to Word 2013
    By drosen99 in forum Word Formatting & General
    Replies: 20
    Last Post: 02-26-2013, 08:20 PM
  5. Replies: 2
    Last Post: 11-04-2012, 04:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06235 seconds
  • Memory Usage 8,957KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (50):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1