|  | excel macro mail send | | |
| | | KisH (Tihomir) |  |
|  Posted: Sun Aug 31, 2008 6:30 pm Post subject: excel macro mail send |  |
| |  | |
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 | |
| | Posted: Mon Sep 01, 2008 5:11 am Post subject: Re: excel macro mail send | |
| | | |
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...
| Quote: | 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!
|
|
| |
| | | KisH (Tihomir) | |
| | Posted: Mon Sep 01, 2008 7:38 am Post subject: Re: excel macro mail send | |
"Per Jessen" <per.jessen@mail.dk> wrote in message
news:eqEZoHADJHA.5196@TK2MSFTNGP04.phx.gbl...
| Quote: | 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 |
| |
| | | KisH (Tihomir) | |
| | Posted: Mon Sep 01, 2008 9:51 am Post subject: Re: excel macro mail send | |
"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:
| Quote: | "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. |
| |
| | | Per Jessen | |
| | Posted: Mon Sep 01, 2008 9:54 am Post subject: Re: excel macro mail send | |
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...
| Quote: |
"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
|
|
| |
| | | KisH (Tihomir) | |
| | Posted: Mon Sep 01, 2008 9:54 am Post subject: Re: excel macro mail send | |
"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:
| Quote: | . 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 | |
| | Posted: Mon Sep 01, 2008 9:59 am Post subject: Re: excel macro mail send | |
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...
| Quote: |
"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.
|
|
| |
| | | KisH (Tihomir) | |
| | Posted: Mon Sep 01, 2008 10:02 am Post subject: Re: excel macro mail send | |
"Per Jessen" <per.jessen@mail.dk> wrote in message
news:%23z1LvlCDJHA.3908@TK2MSFTNGP04.phx.gbl...
| Quote: | 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) | |
| | Posted: Mon Sep 01, 2008 10:07 am Post subject: Re: excel macro mail send | |
"Per Jessen" <per.jessen@mail.dk> wrote in message
news:utD$%23oCDJHA.4436@TK2MSFTNGP02.phx.gbl...
| Quote: | 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 | |
| | Posted: Mon Sep 01, 2008 10:38 am Post subject: Re: excel macro mail send | |
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
"KisH (Tihomir)" <kish89MAKNI@gmail.com> skrev i meddelelsen
news:g9glto$tqm$1@localhost.localdomain...
| Quote: |
"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
|
|
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
