1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Sending an email from excel which quotes cell values

Discussion in 'Archive: Business Applications' started by rafterman79, Sep 24, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    Hello,

    I was wondering whether you could help me.

    I work in a particularly IT illiterate environment which means I have to create easy ways of doing things for people.

    The request I have is as follows:

    I have data in Cells A1 to E1 and in cell F1 I want to put a hyperlink that is clickable when someone wants to inform me of the data in cells A1 to E1 they click F1 it loads up a new email in our Lotus Notes Email system and sends the details of the data in cells A1 to E1.

    I have managed to create a hyperlink and using mailto:[email protected]... I have managed to get a new email in lotus to appear.

    Is it possible to press the hyperlink, load up the new mail session and for excel to automatically copy and paste the data in cells A1 to E1 into the email?

    Thanks,

    Sean
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The script in the attached works with various mail clients.

    Click the "@" button to see if it works with Lotus Notes.
     

    Attached Files:

  3. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    Dude, thats amazing.

    Can you explain how you did it so I can learn to do it myself?
     
  4. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    Ive right clicked and gone into assign macro then edit send email, I can see how youve done it. I am right in thinking that If I want to reference the 2nd row I use (2,1) instead of (1,1)?

    And can I output the cell information into a table going horizontally?
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "Can you explain how you did it ... ?"

    I didn't do anything, other than crib some code from John W.

    "I am right in thinking that If I want to reference the 2nd row I use (2,1) instead of (1,1)?"

    (y)

    "And can I output the cell information into a table going horizontally?"

    If you mean you want the range to appear in the email body as a kind of formatted object, then ... maybe.

    See Ron's Sending mail from Excel with CDO page. Near the top is:

    3: You can send a range/sheet in the body of the mail (some mail programs can’t do this)

    Further down there's:

    Body examples: Module body1 = Selection/Range or whole worksheet


    So you could give that a look-see.

    HTH :)
     
  6. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    Right, I have solved a few issues, I have got the data to output horizontally using space( ) and I have inserted a comma to make pasting it back into excel easier (Text to columns).

    Now I want to be able to replicate that button down the spreadsheet, is there an easy way of doing it and automatically changing the cell references?

    So currently I have the button on Row 3 referencing row 3, if I wanted buttons from row 3 to row 100 and for the button on row 100 to reference row 100 how would I do that?
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Ron's method didn't work w/Lotus?

    1 button per 100 rows = no need.

    Keep the (single) button on row 1 and freeze row 1 so it's always visible. Then use:

    ThisRow = ActiveCell.Row
    Msg = ""
    Msg = Msg & Cells(ThisRow, 1) & "," & Cells(ThisRow, 2) & "," & Cells(ThisRow, 3) & "," & _
    Cells(ThisRow, 4) & "," & Cells(ThisRow, 5) & vbCrLf
     
  8. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    It does work with lotus, an absolute treat, I look like a proper excel nerd now!

    Will try that bit of code, and let you know.
     
  9. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    Worked a treat, as long as the cursor is on the row that im interested in the correct data is returned.

    Thank you again.
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    YW. Use Thread Tools at the top of the page to mark Solved if you wish.
     
  11. rafterman79

    rafterman79 Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    12
    Done,

    Thanks again.

    Sean.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752889

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice