Email from excel with outlook..code win00 XL00

Dave,

If I understand your problem correctly I'd suggest that you compose the text of the body of your e-mail separately and then insert that in your e-mail text.

Use this code line to find the last cell used in column C:

LastRow = Cells(65536, 3).End(xlUp).Row

Then just loop through the cells in column C to create your list:

EvalListMsg = "If possible could the following items be supplied." & Chr(13) & Chr(13)

For i = 2 to LastRow

EvalListMsg = EvalListMsg & Cells(i,3).Value & Chr(13)

Next i

Then just insert the variable "EvalListMsg" into your e-mail text.

Some variant of this approach should work. You may have to play around with the Chr(13)s to get the formatting right but the basic idea should be what you need.

Hope this helps, good luck.

.To = Recipient .CC = "boss@boss.com" .Subject = "Items needed for valuation" .Body = Fname & "," & Chr(13) & Chr(13) & _ "If possible could the folling items be supplied." & Chr(13) & Chr(13) & _ Range("c2") & vbCrLf _ & Range("c3") & vbCrLf _ & Range("c4") & vbCrLf _ & Range("c5") & vbCrLf _ & Range("c6") & vbCrLf & Chr(13) & Chr(13) & _ "Thank You," & Chr(13) & Chr(13) & _ "David Duques" .Attachments.Add ("c:\My Documents\book3.xls") .Display

Thaks alot for the input, your code works perfectly and makes the insertion of the items in the body much easier and cleaner. I still run into the problem of blank(no value) cells apearing in the body. As an example if there are 3 items in row c with the formula
C1=IF(A5<>0,"","need address information")
C2=IF(A6<>0,"","need owners name")
C3=IF(A7<>0,"","need tax returns")

Let's say the first and last items are needed then column c will look like and so will email

need address information

need tax returns

BUT I want the body of the E-mail to look like

need address information
need tax returns

This is because there could be 20 to 30 items and it will look better.

TIA
DAVE Dave, If I understand your problem correctly I'd suggest that you compose the text of the body of your e-mail separately and then insert that in your e-mail text. Use this code line to find the last cell used in column C: LastRow = Cells(65536, 3).End(xlUp).Row Then just loop through the cells in column C to create your list: EvalListMsg = "If possible could the following items be supplied." & Chr(13) & Chr(13) For i = 2 to LastRow EvalListMsg = EvalListMsg & Cells(i,3).Value & Chr(13) Next i

.To = Recipient .CC = "boss@boss.com" .Subject = "Items needed for valuation" .Body = Fname & "," & Chr(13) & Chr(13) & _ "If possible could the folling items be supplied." & Chr(13) & Chr(13) & _ Range("c2") & vbCrLf _ & Range("c3") & vbCrLf _ & Range("c4") & vbCrLf _ & Range("c5") & vbCrLf _ & Range("c6") & vbCrLf & Chr(13) & Chr(13) & _ "Thank You," & Chr(13) & Chr(13) & _ "David Duques" .Attachments.Add ("c:\My Documents\book3.xls") .Display

Re: Email from exc.....Appened Code

Sub Send_Msg3()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Dim Fname As String

Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
Recipient = Range("a11") 'email recipient name in cell
Fname = Left(Worksheets("a").Range("A11").Value, InStr(1, Worksheets("a").Range("A11").Value, " ") - 1)

LastRow = Cells(65536, 3).End(xlUp).Row
EvalListMsg = "If possible could the following items be supplied." & Chr(13) & Chr(13)
For i = 2 To LastRow
EvalListMsg = EvalListMsg & Cells(i, 3).Value & Chr(13)
Next i

Application.StatusBar = "Compiling email.....hang on!"
With objMail

.To = Recipient
'.CC = "boss@boss.com"
.Subject = "Items needed for valuation"
.Body = Fname & "," & Chr(13) & Chr(13) & _
EvalListMsg & Chr(13) & Chr(13) & _
"Thank You," & Chr(13) & Chr(13) & _
"David Duques"
'.Attachments.Add ("c:\My Documents\book3.xls")
.Display
End With
Application.StatusBar = False

Set objMail = Nothing
Set objOL = Nothing
End Sub Thaks alot for the input, your code works perfectly and makes the insertion of the items in the body much easier and cleaner. I still run into the problem of blank(no value) cells apearing in the body. As an example if there are 3 items in row c with the formula .To = Recipient .CC = "boss@boss.com" .Subject = "Items needed for valuation" .Body = Fname & "," & Chr(13) & Chr(13) & _ "If possible could the folling items be supplied." & Chr(13) & Chr(13) & _ Range("c2") & vbCrLf _ & Range("c3") & vbCrLf _ & Range("c4") & vbCrLf _ & Range("c5") & vbCrLf _ & Range("c6") & vbCrLf & Chr(13) & Chr(13) & _ "Thank You," & Chr(13) & Chr(13) & _ "David Duques" .Attachments.Add ("c:\My Documents\book3.xls") .Display

BTW how to put range in body like A1:d10? TIA

Re: Email from exc.....Appened Code

Dave,

Just test for a zero-length string before using the contents of the cell. This would look like:

For i = 2 To LastRow

If Cells(i,3).Value <> "" Then
EvalListMsg = EvalListMsg & Cells(i, 3).Value & Chr(13)
End If
Next i

Also, you probably should dimension the new variables. I'd recommend:

Dim LastRow as Double
Dim i as Double
Dim EvalListMsg as String

Sorry, but I don't have any suggestions for copying the range data and pasting it in the e-mail. I don't know what the syntax is for calling something off the clip board. Perhaps someone else can enlighten us. :-)

take care

Dave,

You may also want to check out this article. I haven't read it yet, but it could have some useful info.

http://www.microsoft.com/exceldev/articles/bulkmail.htm

Thanks it's perfect...much appreciated!

Just test for a zero-length string before using the contents of the cell. This would look like: