Copying Excel to Outlook, I lose the formating

[StevePS]

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

 

[Andrew Poulsom]

See:

http://216.92.17.166/board/viewtopic.php?topic=8251&forum=2

 

[StevePS]

The coding you directed me to works great to copy the spreadsheet to Outlook. this string also provided an automated way to reset the VB/Macro settings. I am unable to get this coding to work. I do not get any error messages, but the references are not reset as needed. Having this step automated would be helpfull.

Thanks again
StevePS

 

[Juan Pablo González]

On 2002-10-10 11:44, StevePS wrote:
...this string also provided an automated way to reset the VB/Macro settings. I am unable to get this coding to work...

I don't understand what you need to happen here...

 

[StevePS]

When using the coding supplied to copy Excel to Outlook, I must first go to the VB editor, click on tools, references, then select MS Scripting Runtime, MS Outlook 10.0 Object Library, and MS Outlook view control. the coding Ivan provides is to automate these steps, which would be of great help to me since I am distributing my spreadsheet for multple users. This is the coding that is not selecting these options.
Hope this helps.
StevePS

 

[invisigirl]

Wow, I thought it was just that Outlook sucked. I've had it change the formatting even copy/pasting from Word into Outlook...or even Outlook into Outlook!

Good luck - I hope something works for you!

 

[dk]

If you set the references and then distribute the workbook to other users they will still be referenced on the other users machine. The only problem you might face is if the other users have a different version of Outlook.

 

[StevePS]

Thanks for all your help. I will send the spreadsheet to a few coworkers and test the reference settings.
StevePS