Using Outlook email message to create Excel Dashboard

  • I receive daily email message regarding the current census of our hospitals and would like these messages to automatically generate/update an Excel spreadsheet to be used for historical trending etc. I am completely novice at macros/scripting. Can anyone examine a sample email message below and help me create such a process? The messages are not HTML formatted, but arrive in plain text.


    "Census as of midnight on Tuesday, Jun 10, 2014 . . .



    Hospital1 (J250-I) Census 49 (+1), Bud 51 (96%), Cap 59 (83%), MTD Adm 30 (+4), MTD ADC 49.8, Bud 51 (98%), LY MTD ADC 54.5, One Medicare patient at or over RAND, Medicare MTD mix 48%, Medicaid census 1 (+0), Medicaid MTD ADC 0.2, Medicaid Pending census 2 (+0), Medicaid Pending MTD ADC 1.4, Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital2 (J220-I) Census 71 (+4), Bud 57 (125%), Cap 82 (87%), MTD Adm 48 (+7), MTD ADC 69.0, Bud 57 (121%), LY MTD ADC 43.0, Two Medicare patients at or over RAND, Medicare MTD mix 53%, Medicaid census 2 (+0), Medicaid MTD ADC 2.0, Medicaid Pending census 5 (+1), Medicaid Pending MTD ADC 1.6, Charity census 0 (+0), Charity MTD ADC 0.1.



    Hospital3 (J260-I) Census 39 (+2), Bud 34 (115%), Cap 40 (98%), MTD Adm 24 (+4), MTD ADC 36.4, Bud 34 (107%), LY MTD ADC 31.6, No Medicare patients at or over RAND, Medicare MTD mix 66%, Medicaid census 0 (+0), Medicaid Pending census 0 (+0), Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital4 (J270-I) Census 24 (+0), Bud 29 (83%), Cap 40 (60%), MTD Adm 14 (+2), MTD ADC 26.8, Bud 29 (92%), LY MTD ADC 24.1, One Medicare patient at or over RAND, Medicare MTD mix 73%, Medicaid census 0 (+0), Medicaid MTD ADC 0.6, Medicaid Pending census 1 (+0), Medicaid Pending MTD ADC 0.7, Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital5 (J280-I) Census 47 (+0), Bud 45 (104%), Cap 66 (71%), MTD Adm 31 (+3), MTD ADC 47.2, Bud 45 (105%), LY MTD ADC 40.7, No Medicare patients at or over RAND, Medicare MTD mix 58%, Medicaid census 0 (+0), Medicaid Pending census 0 (+0), Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital 6 (J300-I) Census 32 (-6), Bud 38 (84%), Cap 50 (64%), MTD Adm 26 (+1), MTD ADC 36.1, Bud 38 (95%), LY MTD ADC 26.9, One Medicare patient at or over RAND, Medicare MTD mix 65%, Medicaid census 0 (+0), Medicaid MTD ADC 0.0, Medicaid Pending census 0 (+0), Medicaid Pending MTD ADC 0.0, Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital 7 (J310-I) Census 40 (+2), Bud 44 (91%), Cap 50 (80%), MTD Adm 26 (+4), MTD ADC 38.2, Bud 44 (87%), LY MTD ADC 16.1, No Medicare patients at or over RAND, Medicare MTD mix 37%, Medicaid census 1 (+0), Medicaid MTD ADC 1.1, Medicaid Pending census 0 (+0), Medicaid Pending MTD ADC 0.0, Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital 8 (J320-I) Census 39 (-2), Bud 48 (81%), Cap 60 (65%), MTD Adm 30 (+6), MTD ADC 43.9, Bud 48 (91%), LY MTD ADC 0.0, No Medicare patients at or over RAND, Medicare MTD mix 57%, Medicaid census 3 (+0), Medicaid MTD ADC 3.0, Medicaid Pending census 2 (+0), Medicaid Pending MTD ADC 1.5, Charity census 0 (+0), Charity MTD ADC 0.6.



    Hospital 9 (J330-I) Census 21 (+0), Bud 15 (140%), Cap 60 (35%), MTD Adm 20 (+1), MTD ADC 16.7, Bud 15 (111%), No Medicare patients at or over RAND, Medicare MTD mix 65%, Medicaid census 0 (+0), Medicaid MTD ADC 0.0, Medicaid Pending census 0 (+0), Medicaid Pending MTD ADC 0.0, Charity census 0 (+0), Charity MTD ADC 0.0.



    Hospital 10 (J290-I) Census 6 (-3), Bud 15 (40%), Cap 50 (12%), MTD Adm 12 (+0), MTD ADC 8.0, Bud 15 (53%), No Medicare patients at or over RAND, Medicare MTD mix 61%, Medicaid census 0 (+0), Medicaid MTD ADC 0.0, Medicaid Pending census 0 (+0), Medicaid Pending MTD ADC 0.0, Charity census 0 (+0), Charity MTD ADC 0.0.



    Please do not reply to this message. It was sent from job 376129/OWNER/HCCNHL0001"

  • Re: Using Outlook email message to create Excel Dashboard


    I assume, you are trying to pull data from Outlook Email to Excel and create a report.


    If I am correct, please read this article on how to import data from Outlook Email to Excel. Once this is done, you can proceed with creating report.


    If you need help with generating report, please post again about the format of the report that you want to generate, so that someone could help you proceed with.


    Hope this Helps.