I have a coded a Macro to copy a section of a spreadsheet to our office email system, Outlook. the macro works great to copy the data, but the formating is lost during the process. Any changes I can make to my Marco to save the format?
Sub CallSub()
'
' CallSub Macro
' Macro recorded 10/9/2002 by S5S
'
Dim newRange As Range
Set newRange = Sheet1.Range("A46:I88")
Call CreateNewEmail(newRange)
End Sub
Sub CreateNewEmail(myRange As Range)
'
' Macro2 Macro
' Macro recorded 10/9/2002 by S5S
'
' This sub creates a new email message and fill the subject and also body as your range
Set myOLApp = New Outlook.Application
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailtem)
With myOLItem
.Subject = "Conservation"
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
tmptext = tmptext & myRange.Cells(i, j).Value
Next j
tmptext = tmptext & vbCrLf
Next i
.Body = tmptext
End With
myOLItem.Display
End Sub
Thanks for your help.
Steve PS
Sub CallSub()
'
' CallSub Macro
' Macro recorded 10/9/2002 by S5S
'
Dim newRange As Range
Set newRange = Sheet1.Range("A46:I88")
Call CreateNewEmail(newRange)
End Sub
Sub CreateNewEmail(myRange As Range)
'
' Macro2 Macro
' Macro recorded 10/9/2002 by S5S
'
' This sub creates a new email message and fill the subject and also body as your range
Set myOLApp = New Outlook.Application
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailtem)
With myOLItem
.Subject = "Conservation"
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
tmptext = tmptext & myRange.Cells(i, j).Value
Next j
tmptext = tmptext & vbCrLf
Next i
.Body = tmptext
End With
myOLItem.Display
End Sub
Thanks for your help.
Steve PS