Create a mail using the mailto: function

[Dynelor]

Hi

I have a product list that also acts like a small database.
When a product is registered though a user form the users login name on the computer is added in one column in that row (L-column).

For that I use this code

Please Login or Register  to view this content.

Now I want to write code so that the user name cell is converted to a hyperlink so when someone click that link the mail client (outlook) shall open and put the cell value as mail recipie. For that I have this code that I found searching the forum, but the code needs to be rewritten some to mach my needs, see below.

Please Login or Register  to view this content.

Now to the problem.
I also want to add a subject to the mail. And that should be the text "You have recived this mail regarding: ", after that the cell value in column A, same row. How do I do that?

If possible I would also like to include some predefined text in the mail body that is the same every time, lika a string called StandardMailBody

Hope for help

[Leith Ross]

Hello Dynelor,

You only have name of the recipient for the MailTo protocol. You also need the email address for the recipient. Where will you obtain this information?

Example

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Dynelor]

Hello Dynelor,

You only have name of the recipient for the MailTo protocol. You also need the email address for the recipient. Where will you obtain this information?

Example

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi Leith Ross
The sheet will only be used internal in the company so the the login user name from the computer will do just fine. If my user name is like Carl001, then outlook will send to the right person. So that is working fine. It is the other issues I wrote about I need help with. How to include a subject and a standard body text, as described above.

[Dynelor]

I have been trying all evning to come up with a solution but have not found anything browsing the web.

When a new product is registered though the excel program, the name (computer login name) is put into a in a cell in the same row and that is the mail adress I want outlook to use as 'to:adress'. Since the list only will be used internal in the company, the computer login name will do just fine as outlook will send to the right person, that I have tested.

I first thought that using the mailto function would be most easy since I then could make a hyperlink at the same time one product was registered and the user name put into the cell. But after browsing the web I found this code, it uses the sendmail function and I think I could use that and that would make it very easy to put predefined text into the emails.

Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim MailAdress As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

MailAdress = ActiveCell.Value
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = MailAdress
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

But there is only one problem then. How do I solve the problem that when someone is clicking in that cell that has the user name in it, the sendmail macro should start. Is this possible, to make the name in the cell a hyperlink and when the hyperlink is clicked the SendMail macro is started?

Is there some way to solve this?
If possible the sendmail is better that mailto since I could put some more information in the mail.
I would be very happy for a solution

Regards
Dynelor