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!

Hi

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

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

Regards,
Per

"KisH (Tihomir)" <kish89MAKNI@gmail.com> skrev i meddelelsen
news:g9ev1d$leh$1@localhost.localdomain...
> 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" <per.jessen@mail.dk> wrote in message
news:eqEZoHADJHA.5196@TK2MSFTNGP04.phx.gbl...
> 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

On Sep 1, 2:38*pm, "KisH \(Tihomir\)" <kish89MA...@gmail.com> wrote:
> "Per Jessen" <per.jes...@mail.dk> wrote in message
>
> news:eqEZoHADJHA.5196@TK2MSFTNGP04.phx.gbl...
>
> > 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

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.

"Nayab" <nayabaz@gmail.com> wrote in message
news:9446ccea-d26c-4389-98e5-c0247e05c479@k37g2000hsf.googlegroups.com...
On Sep 1, 2:38 pm, "KisH \(Tihomir\)" <kish89MA...@gmail.com> wrote:
> "Per Jessen" <per.jes...@mail.dk> wrote in message
>
> news:eqEZoHADJHA.5196@TK2MSFTNGP04.phx.gbl...
>
> > 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

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.

Try this one

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

Regards,
Per

"KisH (Tihomir)" <kish89MAKNI@gmail.com> skrev i meddelelsen
news:g9gd6f$sb8$1@localhost.localdomain...
>
> "Per Jessen" <per.jessen@mail.dk> wrote in message
> news:eqEZoHADJHA.5196@TK2MSFTNGP04.phx.gbl...
>> 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" <nayabaz@gmail.com> wrote in message
news:9446ccea-d26c-4389-98e5-c0247e05c479@k37g2000hsf.googlegroups.com...
On Sep 1, 2:38 pm, "KisH \(Tihomir\)" <kish89MA...@gmail.com> wrote:
>. 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.

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)" <kish89MAKNI@gmail.com> skrev i meddelelsen
news:g9gl5e$tlb$1@localhost.localdomain...
>
> "Nayab" <nayabaz@gmail.com> wrote in message
> news:9446ccea-d26c-4389-98e5-c0247e05c479@k37g2000hsf.googlegroups.com...
> On Sep 1, 2:38 pm, "KisH \(Tihomir\)" <kish89MA...@gmail.com> wrote:
>>. 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" <per.jessen@mail.dk> wrote in message
news:%23z1LvlCDJHA.3908@TK2MSFTNGP04.phx.gbl...
> 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 & "\")

"Per Jessen" <per.jessen@mail.dk> wrote in message
news:utD$%23oCDJHA.4436@TK2MSFTNGP02.phx.gbl...
> 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