remove the cell selection border after copying cell

[michibahn]

hello... i have a code below which works perfectly copying the contents in rage B4 to C4 to the email body but I need to remove the cells copy selection border from being shown in the excel sheet after copying the cells . Also, how can I control the row selection in this code? I think I need to change the xlDown to let say stop the selection at B30 and C30

any chance you can help me out guys? thanks in advance!

Please Login or Register  to view this content.

[nigelbloomy]

Here is how to stop the copy selection border:

Please Login or Register  to view this content.

The rest I don't know about.

[michibahn]

Here is how to stop the copy selection border:

Please Login or Register  to view this content.

The rest I don't know about.

thanks so much nigel! just need to know how to control the row selection because it copies infinitely downwards

[nigelbloomy]

I have used this link to find the last row: (I hope it is OK to lead people to results in other forums.)
http://stackoverflow.com/questions/1...ed-cell-in-vba
It is basically using xlUp instead of xlDown. For some reason that changes the results sometimes.

Please Login or Register  to view this content.

[michibahn]

I have used this link to find the last row: (I hope it is OK to lead people to results in other forums.)
http://stackoverflow.com/questions/1...ed-cell-in-vba
It is basically using xlUp instead of xlDown. For some reason that changes the results sometimes.

Please Login or Register  to view this content.

would you mind showing how to integrate the xlUp in code? I would like to just copy cells starting from B4 to C4 and ends to B30 to C30. thanks again!

[nigelbloomy]

You can hard code that in by using this. As long as the cells don't change very often this would work.

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[michibahn]

You can hard code that in by using this. As long as the cells don't change very often this would work.

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

thank you so much! that did the trick

[michibahn]

hey nigel.. just need another favor if you don't mind.. how do I put a range to capture the email addresses on cells B1 up to B20? currently the code below on gets the address on B1. thanks again!

Please Login or Register  to view this content.

[nigelbloomy]

Please Login or Register  to view this content.

[michibahn]

Please Login or Register  to view this content.

I actually tried that before I post it here but didn't work. I get an "automation error"

[nigelbloomy]

You have to create the string for the To field by looping through your list. I think this would work for you.

Please Login or Register  to view this content.

[nigelbloomy]

Sorry, I forgot that you should declare Cell as a range. Also, the first time through the loop, stTo is empty so you need to add another line to clean out that first 2 semicolons.

Please Login or Register  to view this content.

[nigelbloomy]

This actually works better for the loop because it makes sure to skip any blanks. You also don't need the line stTo = Mid(stTo,3) because now there is nothing to trim.

Please Login or Register  to view this content.

[michibahn]

thanks nigel.. i had to add a space on the email address in B1 since it removes the first letter of the email is there a fix for this?

[michibahn]

thanks nigel.. i had to add a space on the email address in B1 since it removes the first letter of the email is there a fix for this?

disregard my message above.. your second code fixed it! thanks a lot!

[nigelbloomy]

What is your code now? Sorry, I made so many corrections that I am not sure what your code looks like now.

Never mind. Glad that last part fixed it.

[michibahn]

What is your code now? Sorry, I made so many corrections that I am not sure what your code looks like now.

Never mind. Glad that last part fixed it.

don't be sorry everything is fine with the code. I just had to move the Application.CutCopyMode = False below the SendKeys to clear the cell selection box thanks so much for your help

Please Login or Register  to view this content.

[michibahn]

ok i found a little bug in the code.. i have to run the macro twice for the selection to be pasted on the email body. the first run will not paste the selection. if i remove the Application.CutCopyMode = False code, it will work the first time but i need that code to remove the selection border in the sheet. looks like i need to put a delay on this entry for it to wait before it selection gets pasted?

by the way i have a realtime running clock macro also in the sheet. not sure if it affecting this issue..

Please Login or Register  to view this content.

[nigelbloomy]

I tested this code out and it seems to work. It uses a subroutine called RangetoHTML.

Please Login or Register  to view this content.

[michibahn]

I tried the code but it doesn't paste the pictures and charts to the email body

[nigelbloomy]

You might need to start a new thread on how to paste everythng into an email body using sendkeys. You have passed my limit on VBA knowledge, but I know there are lots of others on here who would be able to help. I found the post you used to get the original code, but I have the same problem as you, the body part of the email is not selected when the sendkeys code is trying to paste everything. It looks like there is a way to do this using word as the editor, but I couldn't understand how to make it work.

[michibahn]

You might need to start a new thread on how to paste everythng into an email body using sendkeys. You have passed my limit on VBA knowledge, but I know there are lots of others on here who would be able to help. I found the post you used to get the original code, but I have the same problem as you, the body part of the email is not selected when the sendkeys code is trying to paste everything. It looks like there is a way to do this using word as the editor, but I couldn't understand how to make it work.

thanks so much nigel for all the efforts... i'm playing around with the code as well and trying to make it work thanks again!