Email a range of cells in outlook if a cell is marked with x

Hello,

This is a very hard one and I am unsure if it is possible to make this happen in excel.

I am looking to have an automatic email sent from excel if a product is sold. The email would need to automatically include a range of cells.

So I thought I could possibly have a column in the sheet which is blank, and if a product sells then I could simply enter an "X" in the cell of that column.

So I am basically looking for -

if X is entered in R9, then email range C9:H9. The subject would have to read "Joe Bloggs has sold a product"

The difficult thing is, that I would need it somehow to automatically change the cell range, dependant on where I put the "X". so if I put the X in R10, it would automatically go for the range c10:h10.

I use outlook 2010 as an email client and excel 2010.

Hoping someone can help out.


Thanks


Chris

Originally Posted by daffodil11

I've done this, very doable. Here's the code which goes directly on the worksheet in VB Editor:

Please Login or Register  to view this content.

Thank you for all your help, it's much appreciated. The code nearly works but doesn't include the copied range into the body of the email. It seems to select the range but not include in email.

Any ideas?


Chris

hello there,

I have uploaded a file for you to look at.

Thank you.


Chris

Originally Posted by daffodil11

I see the issue, you're using the code I had published in the first post. I edited the code again 20 minutes later to include cycling through Display prior to Send.

The code above and here works fine.

Please Login or Register  to view this content.

hi Thank you for that, sadly I am still having difficulties.

The problem i have with the code is that most of the time it is pasting the information into the cell below where i put the "X".

On rare occasions it does put it in the email. Sometimes it doesn't paste it into a cell nor does it paste into the body of the email.

Does the code maybe need a pause or something to allow the paste function to work? I wondered if it is pasting too quick to catch the email but really I have no idea.


Chris

Originally Posted by daffodil11

That could be. You can add Application.Wait(Now + #0:00:01#) to the line right after Display to see if the pause helps.

This would appear to have solved the problem delighted as I have had some struggle with this.

Thank you very much for all your help