Solved: Sending an email from excel which quotes cell values

[rafterman79]

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

 

[bomb #21]

The script in the attached works with various mail clients.

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

 

[rafterman79]

Dude, thats amazing.

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

 

[rafterman79]

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?

 

[bomb #21]

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



"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]

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?

 

[bomb #21]

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]

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]

Worked a treat, as long as the cursor is on the row that im interested in the correct data is returned.

Thank you again.

 

[bomb #21]

YW. Use Thread Tools at the top of the page to mark Solved if you wish.

 

[rafterman79]

Done,

Thanks again.

Sean.