Excel Cells to Email (Outlook) - Cells that only have values

Hi,

Can someone please help me out. I am having problems in editing a VB code.

What I currently need now is a macro that will copy cells MainPage!B9:K85 into the email body. And the thing is I only need to copy from that range all cells with values.. and also retain all formats from the excel worksheet.
Please help me out.....

Sample email body/output needed

Hi All,

See details below.

Attendance Summary
Expected Agents 10
Agents Present 6
Absent 4
Absenteeism Rate 40.00%


Unexcused -
Excused -
NCNS -
Late 2


Agents Absent
Agent's Name Supervisor Manager Remarks Status Contact Number Authorized Absence Unauthorized Absence AWOL Total Absences
John Joe Mad Fever Unexcused 12345 0 1 2 3
Chris Joe Mad Emergency Unexcused 12345 2 1 1 4
Johnson Joe Mad Fever Unexcused 543123 2 2 0 4
Clark Joe Mad Influenze Unexcused 7654345 0 0 0 0



Agents Tardy
Agent's Name Supervisor Manager Schedule (PST) Log in time Duration Remarks
Ariel Joe Mad 3:00 AM 3:07 AM 0:07:00 PC problem/ Unable to log in on time
Mira Joe Mad 3:00 AM 3:15 AM 0:15:00 Encountered Traffic


Thanks,

Cittie

See attached file:
testemail.xlsm

Try the attached workbook - you will need to modify it to your needs - I have just set it to display so you will need to change the code to send the email - I removed your code - try to keep these types of functions and subs within modules and worksheet type events within worksheets. This will generate a HTML worksheet in a temporary workbook (which subsequently gets deleted) and then copies it over to outlook in HTML format. Due to the fact there are links to other workbooks in the example you uploaded I had to deal with raising some events so this should not affect other code you may need to add.
As I say you will need to change to your needs - I am happy to help if you have specific questions on how to achieve what you are after.