Copying Excel to Outlook, I lose the formating

StevePS

New Member
Joined
Aug 19, 2002
Messages
7
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

StevePS

New Member
Joined
Aug 19, 2002
Messages
7
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

New Member
Joined
Aug 19, 2002
Messages
7

ADVERTISEMENT

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

Board Regular
Joined
Mar 18, 2002
Messages
130
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

New Member
Joined
Aug 19, 2002
Messages
7
Thanks for all your help. I will send the spreadsheet to a few coworkers and test the reference settings.
StevePS
 

Forum statistics

Threads
1,148,170
Messages
5,745,169
Members
423,930
Latest member
Simple77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top