Create email using excel vba including formatting

Dear all,

I have a piece of VBA code which generates an email for me:

Please Login or Register  to view this content.

Works like a charm. RangetoHTML(rng) is a variable excel table which he needs to put in between StrbodyA1 and StrbodyA2.
Also works without problems.

However now I want to be able to format the text in StrbodyA1 and A2.

What if I want the text in StrbodyA1 to be all in bold and in letter type Arial size 10?
And then have StrbodyA2 to be all in red with size 8?

Anybody who can put me on my way?
Thanks.

Yes, look at HTML on google, font size=8; color=red, something like that

What you are building there is a HTML page

I think you need to add it to the .HTMLBody line what you want the formatting to be. Something like

Please Login or Register  to view this content.

Afraid I don't know the specifics for the formatting you need.

Thanks both. With suggestions i can search further.
However the code of yudlugar is not cutting it so I will have to experiment.

Anybody who knows how to implement the html codes I need to the original code I posted?
Thanks.

Well I think I have a leas on how to do it.
But anyone please feel free to correct if it can be done more efficient.
This is not yet what I want but have to play around with it some more but it seems to be doing what I want:

Please Login or Register  to view this content.

Doing rather well with this but still if someone could help me with this html/css coding with regards to amounts.
Like I have an amount that I want to copy paste into a mail but the format in excel is accounting but when i paste it to mail is leaves out the formatting.
And 100.000 is better to look at then 100000 if you know what I mean.
Thanks

I think if you did something like this:

Please Login or Register  to view this content.

then the number would be formatted when put into outlook.

Ok thanks but I also have text in my mail and how would that then fit in.
Also I am using already outlook app etc.
But to make it more visible this is the line which contains the number and you can see the other formatting for those lines:

Please Login or Register  to view this content.

It is StrbodyA3 which also contains a number. So there is where I want it to show in this format 100.000,00

I assume StrbodyA3 is a string with text and a number? Maybe set the format of the number before you add it to the string? For example:

Please Login or Register  to view this content.

Yes it is but even more the amount number is variable and it takes that from another place on the sheet.
I tried your code on the variable but does not seem to work.
Maybe when I post StrbodyA3 you can have a look? Thanks

Please Login or Register  to view this content.

So variable amount is what it is all about.
And on the sheet this variable is in the correct formatting.
However when it is pasted into the html body of the email it loses its formatting

Ok, so you will need to apply the number format to the amount variable. If you have this variable on the worksheet with the correct formatting do it like this (you need to change Range("A1") to the cell where the amoutn variable is saved):

Please Login or Register  to view this content.

Nice yudlugar, this seems to work...however lets say the number is 10000 then it shows it like 10.000_
Notice the _ at the back? Whats up with that do you know?
And it does not matter what number I take always he put that "_" at the back.

Strange, not sure why that would happen. Not the most elegant solution but you could just trim the last character from the string which would solve the problem:

Please Login or Register  to view this content.

....Norie I knew what you ment and I tried it with changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

It worked like a charm.

Thanks yudlugar for your effort I at least learned new things from you

Norie since you are in the thread now i have an additional question :p

My htmlbody is build like this:

Please Login or Register  to view this content.

I noticed that at every new body unit (StrBodyA1, A2, A3, A4) it automatically leave so free space (rows).
Not that big of a deal but the StrbodyA4 and RangetoHTML(rng) would be nice to have directly after eachother so without spacing.
Any idea how that can be achieved?
Thanks.

Indeed thats not what I mean, sorry will try to explain better.
I am not directly talking about the code but about the outcome in the mail.
So uptill now I have 4 body text parts and after these 4 body text parts it pastes in the table from my excel sheet.
This all work fine with all of your help.

However I noticed in the mail that the macro compiles that between the last text part (StrbodyA4) and the table there is a rather big gap (blank space).
Between the body text parts (StrbodyA1-A3) that does not really matter, but in this case it would look nicer if the excel table (RangetoHTML(rng)) would come directly after StrbodyA4 with a blank "gap" in the email compiled.

Hope this is clearer.

Hmm ok.
Well maybe a solution is to add the text before the tabel when compiling the table range.

So here the range is determined:

Please Login or Register  to view this content.

Would it work and how would I make it work if this piece of code:

Please Login or Register  to view this content.

was changed to:

Please Login or Register  to view this content.

BUT then the text would need to be Arial 8 and in red....
Could that be done?

I don't understand what you mean with add it above the range.
Because that was what i tried to do in the email body but then the gap came.
Or do you mean fysically first have VBA write the text to the sheet which also has the table and then include it in the range?
Unfortunately this would not be an option as there is no room.

Hmm ok I can work whit that. Thanks for the help.