Send Table/Range of Cells As Part of Body through Outlook(HTML) using VBA

Hi Guys,

I have been searching this forum and google for hours over 3 days to have this done, and yes, I have checked on Ronde Bruin's site and I cant figure it out. All I want is to include 5 dynamic tables that I have in excel as part of a body of an email in outlook, exactly as how they look like in excel. I have already figured out everything else like sending the email in "on behalf", sending it to multiple recipients, having the subject to be time specific, having a hyperlink as part of the body with signature, and I'm now stuck with just the tables that is the main purpose of the email.

The file contains 5 sheets. 4 tables would come from "On today" sheet which is arranged vertically separated by an empty row after each table, and the remaining table would come from a different sheet labelled as "individual". All tables are 7 cells wide, and would grow indefinitely down the table.

The table would look something like the sample below. The underlined word is a hyperlink which I already know how to do.

Please check on the data below.

Date	1	2	3	4	5	6
1/1	a	b	c	d	e	f
1/2	g	h	i	j	k	l
1/3	m	n	o	p	q	r

Date	1	2	3	4	5	6
2/1	a	b	c	d	e	f
2/2	g	h	i	j	k	l
2/3	m	n	o	p	q	r

Additionally, here's part of the code that I used. I am hoping that your solution would pick up from here since this is more understandable to me. Hoping that I have posted all the information needed. And thanks in advance

Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem

Set olApp = New Outlook.Application

Set olEmail = olApp.CreateItem(olMailItem)

With olEmail

.SentOnBehalfOfName = "name"

.BodyFormat = olFormatHTML

.Display

.HTMLBody = "Body" & .HTMLBody

.To = "To"

.Subject = "Subject"

End With

did you read this section from rons site?
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

doesnt look like it from the code you have thus far

you need to adjust is the range for RangeToHtml

ie instead of using

Please Login or Register  to view this content.

set rng yourself

if you dont know how to set your own rng, post a sample file of your data setup
so i can have a look at how this can be achieved

then the next part is figuring out how to loop it so all the tables get added

took it basically all from Ron's code
changed slightly to represent your data

cant update the code directly due to the html references
this forum doesnt like it

and im not re-attaching a 1.8mb file

so attached the code in text file

this is the important piece of code

Please Login or Register  to view this content.

change to suit

Thank you so much for the code.

I have realized two things. First, I wanna cry because it did what it's supposed to do, and not only that, it also did an "auto fit" function which was supposed to be my next concern. This really made my day. Thank you so much. My second realization is, there is so much for me to learn. I really don't understand the code that would put the range of cells in excel as part of the body of the outlook email. Everything in the code is something I can understand, thus I can also recreate, but the rest of the code, it made me nauseous. But nonetheless, thank so much.

If only it's possible, I would send you a beer

glad it it helped

there is so much for me to learn

this would go for pretty much most people here, myself included

the majority of the code was Ron's so really credit to him for creating it and posting for the world to reference
i wouldn't remember how to write it personally i knows what each section does based on the comments on the file
so i just have the link handy for when i need it to copy

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.