If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Search this Thread | Display Modes |
#1
|
|||
|
|||
Copy Range of Cells from Excel
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 |
Ads |
#2
|
|||
|
|||
Copy Range of Cells from Excel
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 ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to import Excel range of dates into Outlook Calender | Philip | Outlook and VBA | 1 | September 12th 08 01:51 PM |
Copy a range from Excel to Oulook MailItem's body as Bitmap | WhytheQ | Outlook and VBA | 8 | November 2nd 07 12:33 PM |
Copy contents of Excel range name to Email Subject line | [email protected] | Outlook and VBA | 3 | June 21st 07 06:14 AM |
linking cells in excel to outlook calender | Harvey in Kabul | Outlook - Calandaring | 3 | May 30th 07 04:47 PM |
Paste cells from Excel into message body | goshute | Outlook and VBA | 1 | March 29th 06 06:02 AM |