Email from Excel - Hide blank rows before send to show the signature

**nasrulla** · 01-03-2019, 03:17 AM

Dear Gents,

I am attaching a file which contain a macro to send an email from excel.

Email body range is B2:G20. Signature on B22.

i want to modify this code to hide the blank rows and send so the signature can be easily visible.

It always send all range regardless of blank rows. So i want send the non blank rows only in the email body range.

Please Login or Register  to view this content.

**Keebellah** · 01-03-2019, 03:29 AM

Hi, instead of

Please Login or Register  to view this content.

checkuot the use of SpecialCells

Please Login or Register  to view this content.

I'm not using my co,puter now so yous google fot SpecialCells there you can see what types there are.

Another way is to hide the blank rows before , then use SpecialCells for visible rows only and the unhide them after completion.

Hope this helps and gives you some ideas

**nasrulla** · 01-03-2019, 04:23 PM

Thank you for the quick reply, i will google it for more on SpecialCells

**Keebellah** · 01-03-2019, 05:58 PM

I modified your code,

Please Login or Register  to view this content.

Works but need fine-tuning.
Happy coding

**Keebellah** · 01-03-2019, 05:59 PM

Just a link that might help,

https://excelribbon.tips.net/T008524...n_a_Macro.html

**nasrulla** · 01-04-2019, 04:09 AM

Thank you Keebellah, when i try to change the range (b20 to b25 and g20 to g25) some how it did not work for me. its start picking adjacent columns data as well.

I found another similar code i tweak a bit to work. kindly let me know i am making any mistake here,

Please Login or Register  to view this content.

**Keebellah** · 01-04-2019, 04:33 AM

If you change from B to G then make sure you edit the vba code too.
The test is on column B so make that G and set the range accordingly.

Hard-coding ranges makes it very un-versatile
I haven't looked at the other code but try correcting the code I sent first

**nasrulla** · 01-04-2019, 04:58 AM

I change to code accordingly. B2:B20 represent the rows and b2:g20 is range we are sending. it is checking non black cells in the range. then with specailcells you select the data to send email.
if run the code without sending the email, i can see it is hiding black rows and selecting correctly.

while sending email it pick the next column data. very strange.

**Keebellah** · 01-04-2019, 10:08 AM

No it's not, it's because you're using the envelope method which send the selected worksheet
You should add code to hide all not wanted columns

**Keebellah** · 01-04-2019, 11:59 AM

I made some modifications
Fill the to and cc emails in the yellow cells in sheet2
go to sheet1 and select the range you want to mail and rightclick
Let me know