copy and paste from excel to outlook

this should do what u want...
 Note the change of variable name to sz as str is a reserved word

   Range("F5:I5").Select

    i = Selection.Columns.Count
    j = Selection.Rows.Count
   
    sz = ""
    For r = 1 To i
        For q = 1 To j
            If q > 1 Then sz = sz & vbTab                 'create space between cells
            sz = sz & Selection.Cells(q, r)
        Next q
        sz = sz & vbCrLf    'end new cell line
    Next r
       
    myMailItem.Subject = "test"
    myMailItem.body = sz
    MsgBox sz
   
    myMailItem.Send

thanks for response
but i dont want to send it directly, a new mail should be created with this data and then ill send this mail after writing some things in it.

hope its understandable,

can somebody please answer

its urgent

Take out the line

myMailItem.Send

This would prevent the email from being sent and you should be able to add other information.

you may need myMailItem.save

no points please, Leon has answered this one.

use
myMailItem.Display
rather than
myMailItem.Send

to show the message rather than send it

Cheers

Dave

i think my question is not clear, ill try to explain clearly what im trying to do

im having an excel sheet with some values, so i want to copy a range say range
range("B3:G5").Select

and these valiues are something like this (two cells one with description and other with valiues)

s            r
a           a  
l            t
e           i
s           o
200       400

so like these there are some values and all this i want to copy as range and make them as normal

that is

sales  200
ratio  400

and copy this and paste it in the outlook in a new mail and then i want to add some things and press send

---------------------

the above code is not working , i dont understand what does this thing do, however its giving error at these lines and also its opening appointment planner and not a mail

 Range("Q17").PasteSpecial Transpose:=True
    ' The entire range is already selected so copy it again:
    Selection.Copy
   
    str = Range("F9").Select
-------------------------------------------

so ii hope its little bit understandable now, so if anone has ideas how to go about it, please contribute

thanks

>however its giving error at these

This code is designed to rotate you range (Transpose).  What is your error exactly and on which line?

My piece of code to create a text string with the correct format certainly work though it ends up as pure text not a excel object.
modified to join all but last row in selection together

   i = Selection.Columns.Count
    j = Selection.Rows.Count
   
    sz = ""
    For r = 1 To i
        For q = 1 To j
            If q = j-1 Then sz = sz & vbTab                 'create space between cells
            sz = sz & Selection.Cells(q, r)
        Next q
        sz = sz & vbCrLf    'end new cell line
    Next r

   mailitem.body = sz

My preference is for just text in a message anyway.

>>>This code is designed to rotate you range (Transpose).  What is your error exactly and on which line?

runtime error 1004

application or objectdefition error

>>>>i = Selection.Columns.Count
    j = Selection.Rows.Count
   
    sz = ""
    For r = 1 To i
        For q = 1 To j
            If q = j-1 Then sz = sz & vbTab                 'create space between cells
            sz = sz & Selection.Cells(q, r)
        Next q
        sz = sz & vbCrLf    'end new cell line
    Next r

   mailitem.body = sz


this code is working quite well but how can bring in colours and make it table format

>runtime error 1004

Thisa means that you did not fully define the location of your range.
 
ActiveSheet.Range("Q17").PasteSpecial Transpose:=True

or you may want to use a temporary location on another sheet:

ActiveWorkbook.Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

i want to paste in outlook

>i want to paste in outlook

Yes, but first you must transpose the data into the correct format (as per you requirements), which is why there is an intermediate step of copying in Excel.

only other way would be to create the string transfer as HTML , with color & table codes.  eg

i = Selection.Columns.Count
    j = Selection.Rows.Count
   
    sz = "<table>" & vbcrlf
    For r = 1 To i
         sz = sz & "<tr>" & vbcrlf
        For q = 1 To j
           
            sz = sz & "<td>" & Selection.Cells(q, r) & "</td>"
        Next q
        sz = sz & "</tr>" & vbCrLf    'end new cell line
    Next r
   sz = sz & "</table>" & vbcrlf

   mailitem.body = sz


color formats up to you....

>>>create the string transfer as HTML

how can i do this, shld i save it as html or there is some other way?

use myMailItem.HTMLBody  instead (or as well as) .Body
from MSDN ..... The HTMLBody property contains the HTML representation of the message.

eg....
myMailItem.HTMLBody = "<body>Dear " & strName & "<br>Thank you for registering with Us. <br>Your Username is :" & strUser & " <br> Your password is:" & strPwd & "<br>Have a nice day!</body>"

when im using this code its giving me this format


Leistungsart LA Konstruktion Mechanik Entwicklung Prozesse Konstruktion Hardware Messtechnik Applikation Messtechnik Software Entwicklung Elektronik Vision Applikation Software Applikation Ausprobe Funktion / Software Montage Ausprobe Mechanik Installation Elektrik
Abteilung EDM EPT EES14 EES2 EES2 EES2 EES22 EES3 EES3 MOE2 MOE2 MOE25
             
where as i want to copy as object just as we copy and paste (with same colour etc)




Range("AA1:AC13").Select
sz = Selection.Copy

i = Selection.Columns.Count
    j = Selection.Rows.Count

    sz = "<table>" & vbCrLf
    For r = 1 To i
         sz = sz & "<tr>" & vbCrLf
        For q = 1 To j

            sz = sz & "<td>" & Selection.Cells(q, r) & "</td>"
        Next q
        sz = sz & "</tr>" & vbCrLf    'end new cell line
    Next r
   sz = sz & "</table>" & vbCrLf




Application.CutCopyMode = True


    myMailItem.Subject = "Projekt datein"
    myMailItem.HTMLbody = sz
    myMailItem.display
    Set myOutlook = Nothing

link given by robberbaron (www.mail-archive.com) works fine but needs tweaking regarding default mail format. Warning though : depends on Outlook's preferences regarding the New Mail form (displaying Bcc: field for instance)
Here's an updated & shorter version (works fine on Outlook XP) :

Public Sub pasteInfoOutlook()
 
    Range("H1:J9").Copy

    Dim MyOutlookAppl As Outlook.Application
    Dim MyMailItem As Outlook.MailItem
 
    Set MyOutlookAppl = CreateObject("Outlook.Application")
    Set MyMailItem = MyOutlookAppl.CreateItem(olMailItem)
 
    With MyMailItem
        .BodyFormat = olFormatRichText ' switch to RTF format
        .Display
    End With
    ' move focus to mail body
    SendKeys "{TAB}"
    SendKeys "{TAB}"
    SendKeys "{TAB}"
    SendKeys "{TAB}"
    SendKeys "{HOME}"
    ' write introduction text
    SendKeys "mmfmmf !"
    SendKeys "{ENTER}"
    SendKeys "{ENTER}"
    SendKeys "{ENTER}"
    ' paste excel selection
    SendKeys "^v", True
 
End Sub

I think there is a solution here with multiple authors.

GPrentice00 & robberbaron : sorry if I've mixed things. I read the answers, tried them and posted a compilation of what works for me, I don't deserve any points.