Auto sending email from Excel

[jimmisavage]

Good day!
I have some code which created an email with data taken from excel. The only problem is, it doesn't seem to keep all the formatting. Dates are numbers, left align moves to right align etc.
Can anyone suggest how i can fix this please?

Please Login or Register  to view this content.

It would also be useful to find the last row with a value (not just a formula that returns "") instead of 'Set SendingRng = ActiveWorkbook.Sheets("Summary").Range("A1:B400")'. Something like .Range("A1:B" & Last Cell), except something that works!

Best wishes
Jimmi

[jimmisavage]

To follow up my post - it looks like this got a bit more complicated. My formatting is a conditional format - so i want to copy the conditional formatting without copying the rules into the email.
I don't know if this is possible but maybe there is a way to copy the data and conditional formatting without copying the rules into another sheet? Then I can use that sheet as the email range?

I am completely stumped on this! Using Excel 2010.

Any help is appreciated.

[AlphaFrog]

1.) edit your original thread and obfuscate the actual TO: email address

2.) Set SendingRng = Sheets("Summary").Range("A1", Sheets("Summary").Range("B" & Rows.Count).End(xlUP))

3.) The code would have to copy the sheet and apply the actual formatting to each cell the same as the conditional formatting rules do.

[jimmisavage]

1.) edit your original thread and obfuscate the actual TO: email address

2.) Set SendingRng = Sheets("Summary").Range("A1", Sheets("Summary").Range("B" & Rows.Count).End(xlUP))

3.) The code would have to copy the sheet and apply the actual formatting to each cell the same as the conditional formatting rules do.

Hi AlphaFrog,
Thanks for the reply. I have amended my original post - thanks for pointing that out.

2: No matter what code I put in it always seems to think row 400 is my last cell. There is an {INDEX} formula in the cells, which is probably why, but is there a way around that? I guess I need to find the last cell that has an actual value that isn't #NUM! or #N/A?

3. The formatting issue is a real stumbling block from what i have found online because its formatted almost entirely on conditional formatting (each cell changes and the results need different formatting). To overcome this, i think i'm going to try and save/email the sheet as a pdf. That way it will be sent exactly as it's seen in the spreadsheet.

[nigelog]

For point number 2. Last row excluding formulas try

Please Login or Register  to view this content.

[jimmisavage]

Hi Nigel,
Thanks for your input. This seems to work well for a copy/paste job (as I needed in my first post) but I can't seem to make it work for my pdf alternative. I'm searching the range - how would i use that code in here?
The range should be A1:B last used row

Please Login or Register  to view this content.

Thanks again
Jimmi

[AlphaFrog]

ActiveWorkbook.Sheets("Summary").Range("A1:B" & lr).ExportAsFixedFormat ...

[nigelog]

Please Login or Register  to view this content.

[jimmisavage]

Thank you Nigel and Alpha for all the help. I have achieved what I needed to achieve now
I hope you both have a great day!

[nigelog]

Glad you got it sorted. Many thanks for the rep points