The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hi Brice

Strange, but you can change
Environ$("temp")

To another folder

TempFile = "C:/TestFolder" & .......................

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

See
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

And read the info in the tips page (link on top of the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hi Ron,

Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with
email body data and keeps workbook open. Macro also creates an email without
email body details. Please advise

Thanks

"Ron de Bruin" wrote:

Hi Brice

Strange, but you can change
Environ$("temp")

To another folder

TempFile = "C:/TestFolder" & .......................

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

See
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

And read the info in the tips page (link on top of the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hi Ron, in regards to copy/past unformatted values, I went through the
weblinks you provided and am still very confused. Is it possible you can
still help me with both requests by providing full code?

From, The Novice

"Brice" wrote:

Hi Ron,

Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with
email body data and keeps workbook open. Macro also creates an email without
email body details. Please advise

Thanks

"Ron de Bruin" wrote:

Hi Brice

Strange, but you can change
Environ$("temp")

To another folder

TempFile = "C:/TestFolder" & .......................

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

See
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

And read the info in the tips page (link on top of the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hello, can somebody help me with exact code? I don't know how to fix these
two issues. Thanks so much, Brice

"Brice" wrote:

Hi Ron, in regards to copy/past unformatted values, I went through the
weblinks you provided and am still very confused. Is it possible you can
still help me with both requests by providing full code?

From, The Novice

"Brice" wrote:

Hi Ron,

Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with
email body data and keeps workbook open. Macro also creates an email without
email body details. Please advise

Thanks

"Ron de Bruin" wrote:

Hi Brice

Strange, but you can change
Environ$("temp")

To another folder

TempFile = "C:/TestFolder" & .......................

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

See
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

And read the info in the tips page (link on top of the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hi Brice

Try to change the security first on the machine to test if it
is working then



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
Hello, can somebody help me with exact code? I don't know how to fix these
two issues. Thanks so much, Brice

"Brice" wrote:

Hi Ron, in regards to copy/past unformatted values, I went through the
weblinks you provided and am still very confused. Is it possible you can
still help me with both requests by providing full code?

From, The Novice

"Brice" wrote:

Hi Ron,

Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with
email body data and keeps workbook open. Macro also creates an email without
email body details. Please advise

Thanks

"Ron de Bruin" wrote:

Hi Brice

Strange, but you can change
Environ$("temp")

To another folder

TempFile = "C:/TestFolder" & .......................

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

See
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

And read the info in the tips page (link on top of the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hi Ron,

Other users have tested on their computers with low-level security settings
and still not working. Can you help?

Thanks!

"Ron de Bruin" wrote:

Hi Brice

Try to change the security first on the machine to test if it
is working then



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
Hello, can somebody help me with exact code? I don't know how to fix these
two issues. Thanks so much, Brice

"Brice" wrote:

Hi Ron, in regards to copy/past unformatted values, I went through the
weblinks you provided and am still very confused. Is it possible you can
still help me with both requests by providing full code?

From, The Novice

"Brice" wrote:

Hi Ron,

Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with
email body data and keeps workbook open. Macro also creates an email without
email body details. Please advise

Thanks

"Ron de Bruin" wrote:

Hi Brice

Strange, but you can change
Environ$("temp")

To another folder

TempFile = "C:/TestFolder" & .......................

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

See
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

And read the info in the tips page (link on top of the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brice" wrote in message ...
The below code (which creates email from a range/selection in excel)
currently works on my computer but I would like my colleagues to use it too.
When they try on their computers they receive an script error message. The
debugger identifies "Environ$" as problem in VBA ascript. How do I fix this?

Also, I would like to copy & paste unformatted values into the email. Is
this possible? Please provide code if possible.

I would really appreciate your help! Thanks, Brice

Code:
---------------------------------------------
Function RangetoHTML(rng As Range)

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"



'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

-----------------------------------------------------------
Sub Mail_Selection_Range_Outlook_Body_Recall()

' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'You can also use a range if you want

Set rng =
Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = Sheets("Recall").Range("O9").Value

.CC = "

.BCC = ""

.Subject = "Wire Receipt"

.HTMLBody = RangetoHTML(rng)

.Display 'or use .Send as replacement to auto-send email without review

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub