Send Range of Cells in Email from Excel 1

[AtlasAF]

I am trying to program a button in excel that will select a specific range of cells from an active spreadsheet and paste those cells into a new email. Anyone out there know how to do this?

AtlasAF
USAF

 

[SkipVought]

Hi,

Add a new workbook.

Copy the range to the new workbook

Send the new workbook.

Skip,

_[red][/red]

 

[AtlasAF]

I need the selected range to be in the body of the email, not under an attachment.

AtlasAF
USAF

 

[AnotherHiggins]

What email program are you using?

Do you want the data to appear as a picture? If not, I don't see how it will come through with any formatting.

(For the record, if you do want it as a pic, I'm *still* not sure how to do it, but I can at least see that being possible.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AtlasAF]

I'm using Office 2003. The easiest way to answer is I need the email to look like you selected the range of cells and "pasted" the cells in the email. I have the following code built thus far, the problem I'm running into is getting the cells to paste into the message.

Private Sub CommandButton1_Click()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Dim varBody As String

Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
varBody = Worksheets("DCC").Range("A1:G25").Activate


    With objmail
        .To = "Jane.Doe@email.com"
        .Subject = "Next Of Kin Information"
        .Body = varBody & vbCrLf & "If you have any please email john.doe@email.com" & vbCrLf
        .NoAging = True
        .Display
    End With
    Set objmail = Nothing
    Set objol = Nothing

End Sub

Previously I used the .copy instead of .activate and tried varBody.paste in the .body but I just got errors.

AtlasAF
USAF

 

[WinblowsME]

http://word.mvps.org/FAQS/MacrosVBA/ManipulateClipboard.htm

Private Sub CommandButton1_Click()
   Dim objol As New Outlook.Application
   Dim objmail As MailItem
   Dim varBody As String
   [COLOR=red]Dim objdata As DataObject[/color]

   Set objol = New Outlook.Application
   Set objmail = objol.CreateItem(olMailItem)
   [COLOR=red]Set objdata = New DataObject[/color]

   Worksheets("DCC").Range("A1:G25").[COLOR=red]Copy[/color]

   [COLOR=red]objdata.GetFromClipboard[/color]
   [COLOR=red]varBody = objdata.GetText[/color]

   With objmail
      .To = "Jane.Doe@email.com"
      .subject = "Next Of Kin Information"
      .body = varBody & vbCrLf & "If you have any please email john.doe@email.com" & vbCrLf
      .NoAging = True
      .Display
   End With
    
   Set objmail = Nothing
   Set objol = Nothing
End Sub

 

[WinblowsME]

I have a very bad habit of not releasing the objects...

Set objdata = Nothing

 

[AtlasAF]

Perfect the information is now in the email. I do have one more question, do you know if it is possible to maintain the formatting?

AtlasAF
USAF

 

[WinblowsME]

This is the only way I can think of to keep the format, but this includes the entire sheet as the email body. SendKeys is sloppy, so be careful. Also, this method will bypass the Outlook Security prompt.

Sub Send_Email()
   ActiveWorkbook.EnvelopeVisible = True
   ActiveWorkbook.ActiveSheet.Range("A1").Select
   
   ' Tweak SendKeys accordingly
   ' The below considers the TO Field to be three SHIFT+TABS up
   
   SendKeys "+{TAB}"             ' SHIFT + TAB
   SendKeys "{HOME}+{END}{DEL}"  ' Deletes textbox contents
   SendKeys "SUBJECT"            ' Subject Field?
   
   SendKeys "+{TAB}"             ' SHIFT + TAB
   SendKeys "{HOME}+{END}{DEL}"  ' Deletes textbox contents
   SendKeys ""                   ' CC Field?
   
   SendKeys "+{TAB}"             ' SHIFT + TAB
   SendKeys "{HOME}+{END}{DEL}"  ' Deletes textbox contents
   SendKeys "[URL unfurl="true"]www.com"[/URL]            ' TO Field?
   
   'SendKeys "%s"                ' ALT + S - Sends Email
End Sub

 

[WinblowsME]

   'SendKeys "%s"[COLOR=red], 1[/color]

 

[AtlasAF]

In that case I'll stick with the previous code. Thanks again for your help. Stars to you.

AtlasAF
USAF