Solved: Sending an email from excel which quotes cell values

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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.
 

Attachments

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?
 

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?
 
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 :)
 

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?
 
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
 

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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top