excel macro mail send

[KisH \(Tihomir\)]

Hello,
I'm using this vb macro code for sending mail.
---------------------------------------------------------------
Sub mailto_Selection()
Dim Email As String, Subj As String, cell As Range
Dim response As Variant
Dim msg As String, url As String
Email = "" 'create list below
Subj = "Family Newsletter"
msg = "Here needs to be named range from excel(some text)"
'-- Create the URL

For Each cell In Selection
Email = Email & cell.Text & "; "
Next cell

url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
& Replace(msg, Chr(10), "/" & vbCrLf & "\")
url = Left(url, 2025) 'was successful with 2025 , not with 2045
'-- Execute the URL (start the email client)
ActiveWorkbook.FollowHyperlink (url)
Application.Wait (Now + TimeValue("0:00:10"))
Application.SendKeys "%s"

End Sub
-------------------------------------------------------------------------

Problem is that I don't know how to add named range(text) from excel to mail
body.
Ex.
In sheet1 I have text:
Bla bla bla
Bl bl bl
B b b
This text is named as named range "txt".
So, in this line of code " msg = "Here needs to be named range from
excel(some text)" ",
msg needs to have value of named range "txt".

How to do that?

Thank you!

 

[Per Jessen]

Hi

Substitute the "msg=..." line with the code below:

For Each cell in Range("txt")
Msg=Msg & cell.Value
Next

Regards,
Per

 

[KisH \(Tihomir\)]

Hi

Substitute the "msg=..." line with the code below:

For Each cell in Range("txt")
Msg=Msg & cell.Value
Next

Hello,
Thanks for your help but I got this error on your code:

Run-time error '1004':
Application-defined or object-defined error

I'm using Excel 2007

 

[Nayab]

Hello,
Thanks for your help but I got this error on your code:

Run-time error '1004':
Application-defined or object-defined error

I'm using Excel 2007

I checked it and the solution suggested is fine. However I get an
error 1004 when I try to access a named range which does not exist. So
check the name of the named range you are using.

 

[KisH \(Tihomir\)]

Hello,
Thanks for your help but I got this error on your code:

Run-time error '1004':
Application-defined or object-defined error

I'm using Excel 2007

I checked it and the solution suggested is fine. However I get an
error 1004 when I try to access a named range which does not exist. So
check the name of the named range you are using.

 

[Per Jessen]

Try this one

For Each cell In Range("txt").Cells
msg = msg & cell.Value
Next

Regards,
Per

 

[KisH \(Tihomir\)]

. So
check the name of the named range you are using.

Thanks i checked and now code works fine, but i have another problem.
Ex.
A B C
1 text1
2 text2
3 text3
4

Result in email body is text1text2text2.
What do I need to do so I can get in email body:
text1
text2
text3

Thanks.

 

[Per Jessen]

This should do it:

For Each cell In Range("txt").Cells
If msg <> "" Then
msg = msg & vbLf & cell.Value
Else
msg = cell.Value
End If
Next

Regards,
Per

 

[KisH \(Tihomir\)]

Try this one

For Each cell In Range("txt").Cells
msg = msg & cell.Value
Next

Huh same result, everything in one row in mail body.
Maybe problem is somwhere here:
url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
& Replace(msg, Chr(10), "/" & vbCrLf & "\")

 

[KisH \(Tihomir\)]

This should do it:

For Each cell In Range("txt").Cells
If msg <> "" Then
msg = msg & vbLf & cell.Value
Else
msg = cell.Value
End If
Next

Now result is text1/\text2/\text3

 

[Per Jessen]

It's working as desired here, but this should also do it:


For Each cell In Range("txt").Cells
If msg <> "" Then
msg = msg & Chr(13) & cell.Value
Else
msg = cell.Value
End If
Next

Regards,
Per

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/oebody.htm

If you use Outlook you have better options
See
http://www.rondebruin.nl/sendmail.htm