+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    08-31-2015
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    8

    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

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    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.
    Last edited by daffodil11; 09-09-2015 at 05:45 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    08-31-2015
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    8

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

    Quote Originally Posted by daffodil11 View Post
    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

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    Works pretty well for me. I'd recommend attaching a desensitized sample of your work so we can diagnose what the hangups are.

  5. #5
    Registered User
    Join Date
    08-31-2015
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    8

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

    hello there,

    I have uploaded a file for you to look at.

    Thank you.


    Chris
    Attached Files Attached Files

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    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.

  7. #7
    Registered User
    Join Date
    08-31-2015
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    8

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

    Quote Originally Posted by daffodil11 View Post
    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

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

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

  9. #9
    Registered User
    Join Date
    08-31-2015
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    8

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

    Quote Originally Posted by daffodil11 View Post
    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

  10. #10
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    Sweetness! So glad it all worked out!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-18-2014, 03:13 AM
  2. Launch outlook and Email specific range of cells
    By Jvollmer33 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 09-19-2013, 07:09 PM
  3. VBA to select a Range of cells as Email Body In Outlook
    By TC922 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2013, 11:06 AM
  4. [SOLVED] VBA Code to select Range of cells as Email Body In Outlook
    By naveenmarapaka in forum Outlook Programming / VBA / Macros
    Replies: 5
    Last Post: 09-16-2013, 06:43 AM
  5. send selected range in email with default outlook email signature included
    By mdsickler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2013, 10:50 PM
  6. Macro to PDF a sheet in workbook and email (outlook) to an email address in a cell
    By paul_sykes00 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-17-2012, 12:54 AM
  7. [SOLVED] sending Email (via outlook) from Excel with cell range and image from external file
    By Kramxel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2012, 11:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1