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
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