I have a range of cells on a spreadsheet that I want to copy to Outlook as
the body of the message. The range is 9 rows by 9 columns. Three rows have
data on them, and each row of data has 2 or 3 sections (2 or 3 columns) of
data. These sections are formatted differently - different fonts, different
colors, date format, number format, etc. Some sections have borders.

If I highlight the range in the spreadsheet, select copy, go to Outlook, and
paste, I get an exact replica of what is on the spreadsheet. I want to make
this happen using VBA. The code below works partially - it gives me all of
the data in the range. The dates show correctly, as do the numbers and
amounts. What it apparently is doing is copying all the text (which, of
course, is what .GetText would appear to do). The spacing between rows of
data is correct. But it is all in one font and one color, there are no
borders, and the spacing between columns of data is not exactly right.

How can I get all the formatting (areas with borders, color, font
parameters, etc.) to move to Outlook - in other words, how can I get VBA to
give me an exact replica like Copy and Paste does?


Public olToName As String
Public olSubject As String
Public olBody As String
Public olRange As Range
Public RangeData As DataObject
Public olAttach1 As String

Sub SendReceipt()

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim SigString As String
Dim Signature As String

Set olApp = New Outlook.Application
Set RangeData = New DataObject

olToName = Range("K5").Value
olSubject = "Dues Receipt"
Range("A1:I9").Copy
RangeData.GetFromClipboard
olBody = RangeData.GetText
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = olToName
.Subject = olSubject
.Body = olBody
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

End Sub

--
Bill @ UAMS

Copy the range to a new worksheet and then use the technique shown in the
sample at http://www.outlookcode.com/codedetail.aspx?id=1333. The same
MailEnvelope object also works for a Worksheet.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"BillCPA" <Bill @ UAMS> wrote in message
news:3573F798-40FB-4C26-BF7F-(E-Mail Removed)...
>I have a range of cells on a spreadsheet that I want to copy to Outlook as
> the body of the message. The range is 9 rows by 9 columns. Three rows
> have
> data on them, and each row of data has 2 or 3 sections (2 or 3 columns) of
> data. These sections are formatted differently - different fonts,
> different
> colors, date format, number format, etc. Some sections have borders.
>
> If I highlight the range in the spreadsheet, select copy, go to Outlook,
> and
> paste, I get an exact replica of what is on the spreadsheet. I want to
> make
> this happen using VBA. The code below works partially - it gives me all
> of
> the data in the range. The dates show correctly, as do the numbers and
> amounts. What it apparently is doing is copying all the text (which, of
> course, is what .GetText would appear to do). The spacing between rows of
> data is correct. But it is all in one font and one color, there are no
> borders, and the spacing between columns of data is not exactly right.
>
> How can I get all the formatting (areas with borders, color, font
> parameters, etc.) to move to Outlook - in other words, how can I get VBA
> to
> give me an exact replica like Copy and Paste does?
>
>
> Public olToName As String
> Public olSubject As String
> Public olBody As String
> Public olRange As Range
> Public RangeData As DataObject
> Public olAttach1 As String
>
> Sub SendReceipt()
>
> Dim olApp As Outlook.Application
> Dim olMail As MailItem
> Dim SigString As String
> Dim Signature As String
>
> Set olApp = New Outlook.Application
> Set RangeData = New DataObject
>
> olToName = Range("K5").Value
> olSubject = "Dues Receipt"
> Range("A1:I9").Copy
> RangeData.GetFromClipboard
> olBody = RangeData.GetText
> Set olMail = olApp.CreateItem(olMailItem)
> With olMail
> .To = olToName
> .Subject = olSubject
> .Body = olBody
> .Send
> End With
>
> Set olMail = Nothing
> Set olApp = Nothing
>
> End Sub
>
> --
> Bill @ UAMS