excel macro mail send

  • Thread starter Thread starter KisH \(Tihomir\)
  • Start date Start date
K

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!
 
Hi

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

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

Regards,
Per
 
Per Jessen said:
Hi

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

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


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
 
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
Click to expand...

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.
 
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
Click to expand...

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
 
. So
check the name of the named range you are using.
Click to expand...

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
 
Per Jessen said:
Try this one

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

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 said:
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
Click to expand...
:(
Now result is text1/\text2/\text3
 
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
 
Back
Top