Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1426
  • Last Modified:

copy and paste from excel to outlook

Hello,

im trying to copy some cells from the excel sheet and paste it the outllook

and i want to have it in a particular format, for eg my cells are having values

10  20  30
1    2    3

so i want to copy them as

10   1
20   2
30   3

in a tabular format, im not able to draw lines here, but hope its understandable

could anyone tell me how to achieve this

thanks

................

Private Sub CommandButton1_Click()
Dim myOutlook As Object
Dim myMailItem As Object
Dim str As String

    Set myOutlook = CreateObject("Outlook.Application")
    Set myMailItem = myOutlook.createitem(0)
   
    myMailItem.Recipients.Add "arunkumargc77@Yahoo.com"
   
    Range("F5:I5").Select
    Selection.Copy
   
    str = Range("F9").Select
       
    myMailItem.Subject = "test"
    myMailItem.body = str
    MsgBox str
   
    myMailItem.Send
   
    Set myOutlook = Nothing
   
End Sub



0
vihar123
Asked:
vihar123
  • 8
  • 7
  • 5
  • +2
2 Solutions
 
leonstrykerCommented:
Copy and Transpose in Excel first, then Copy to outlook:

Private Sub CommandButton1_Click()
Dim myOutlook As Object
Dim myMailItem As Object
Dim str As String
    Set myOutlook = CreateObject("Outlook.Application")
    Set myMailItem = myOutlook.createitem(0)
    myMailItem.Recipients.Add "arunkumargc77@Yahoo.com"
    Range("F5:I5").Copy
    ' Pick a range to paste somewhere
    Range("Q17").PasteSpecial Transpose:=True
    ' The entire range is already selected so copy it again:
    Selection.Copy
   
    str = Range("F9").Select
       
    myMailItem.Subject = "test"
    myMailItem.body = str
    MsgBox str
    myMailItem.Send
    Set myOutlook = Nothing
End Sub
0
 
Robberbaron (robr)Commented:
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
0
 
vihar123Author Commented:
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,

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vihar123Author Commented:
can somebody please answer

its urgent
0
 
leonstrykerCommented:
Take out the line

myMailItem.Send

This would prevent the email from being sent and you should be able to add other information.
0
 
Robberbaron (robr)Commented:
you may need myMailItem.save
0
 
Dave BrettVice President - Business EvaluationCommented:

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
0
 
vihar123Author Commented:
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

0
 
leonstrykerCommented:
>however its giving error at these

This code is designed to rotate you range (Transpose).  What is your error exactly and on which line?
0
 
Robberbaron (robr)Commented:
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.
0
 
vihar123Author Commented:
>>>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
0
 
vihar123Author Commented:
>>>>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
0
 
leonstrykerCommented:
>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
0
 
vihar123Author Commented:
i want to paste in outlook
0
 
leonstrykerCommented:
>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.
0
 
Robberbaron (robr)Commented:
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....
0
 
vihar123Author Commented:
>>>create the string transfer as HTML

how can i do this, shld i save it as html or there is some other way?
0
 
Robberbaron (robr)Commented:

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>"
0
 
vihar123Author Commented:
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
0
 
Robberbaron (robr)Commented:

then without more code to check the formating of the source cell and apply it to the HTML output, ie font color,back color, border, font name,font size......  probably not to hard but may not be perfectly what u expect

I think your only other option is to go leon's way....
copy range in Excel, paste to dummy location with transposition
select dummy zone & paste into outlook.   but i'm not sure how the paste to clipboard into outlook works.

  mailitem.paste     ?

I suspect that doing it manually uses OLE

I looked elsewhere & there are a few examples now that I know what u really want

http://www.officekb.com/Uwe/Forum.aspx/outlook-prog-vba/3118/Images-from-Word-to-MAPI-with-VB6  something similar but still onl pastes text , not the object u want which is RTF at least.

getting closer... http://p2p.wrox.com/archive/vba_excel/2003-02/18.asp
same problem as you... http://www.mrexcel.com/archive2/5200/5848.htm        no answer !

a solution ?? http://www.mail-archive.com/vba_excel@p2p.wrox.com/msg01915.html

and .... http://www.outlookcode.com/threads.aspx?forumid=2&messageid=1532  seems to solve the same problem u have !!!!

0
 
xandrexCommented:
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
0
 
Robberbaron (robr)Commented:
I think there is a solution here with multiple authors.
0
 
xandrexCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 7
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now