Copy Range of Cells from Excel

  • Thread starter BillCPA
  • Start date Views 1,348
Status
Not open for further replies.
B

BillCPA

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
 
Status
Not open for further replies.
Top