Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: E-Mail a Worksheet Selection. How?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I have a worksheet open called CopySheet
    I need to :-
    a) Copy a worksheet selection (Range B1 -E38) to the clipboard.
    c) Open Outlook
    d) Select recipient from the address book.
    e) Paste the clipboard contents into the text area.
    Is it possible to this with a macro?
    Thanks Ted

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hit Ted...
    I think you are using office 2000?
    Select your range and click on the email icon.
    I sent you a test email.
    Let me know if it works ok and I'll see if I can come up with the code to do it as well...
    Tom

    [ This Message was edited by: TsTom on 2002-04-21 05:22 ]

  3. #3
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    And in code:

    Add Microsoft Outlook Object Library by selecting VBA_Tools_References then use this code:

    'This sub set the range
    Sub CallSub()
    Dim newRange As Range
    Set newRange = Sheet1.Range("B4:D7")
    Call CreateNewEmail(newRange)
    End Sub

    'This sub creates a new email message and fill the subject and also body as your range data
    Sub CreateNewEmail(myRange As Range)
    Set myOLApp = New Outlook.Application
    Dim myOLItem As Outlook.MailItem
    Set myOLItem = myOLApp.CreateItem(olMailItem)
    With myOLItem
    .Subject = "Here is subject"
    For i = 1 To myRange.Rows.Count
    For j = 1 To myRange.Columns.Count
    tmptext = tmptext & " " & myRange.Cells(i, j).Value
    Next j
    tmptext = tmptext & vbCrLf
    Next i
    .Body = tmptext
    End With
    myOLItem.Display
    End Sub


    It is possible to play with this more and select To: etc in code.

    Regards

    Suat

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Smoz?
    Will that code retain the formatting?
    Tom

  5. #5
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    What kind of formatting you mean?
    Suat

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The formatting from the sheet as is...

  7. #7
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, just data with space delimiter for columns and new line for rows.
    Suat

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tom / Smozgur

    Still trying to get the code to work this end.
    Will let you know how it goes.
    Tom I haven,t got your test mail
    Thanks all!
    Ted

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Smozgur / Tom

    Your code works really well Smozgur and it's lightning fast! I need to retain the formatting though of the original sheet as it's not plain text I am sending but a quotation which has columns Qty and Description with total Equipment,VAT and Total + VAT calls at the bottom. If the formatting could be sorted then thats the answer to all my E-Mail problems. Maybe if it were sent as HTML or a picture it may solve the problem! Is that possible
    Regards Ted

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •