Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi Ron,

i have amended the code as directed [please see below] and it still
only pasts the defined area and does not add the text as a string. :-(

Function TLMailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
"<br<br"

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = StrBody & RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Function


Ron de Bruin wrote:
I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Which Excel version do you use ?
Can you send me the workbook private.


--

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


"spences10" wrote in message ups.com...
Hi Ron,

i have amended the code as directed [please see below] and it still
only pasts the defined area and does not add the text as a string. :-(

Function TLMailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
"<br<br"

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = StrBody & RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Function


Ron de Bruin wrote:
I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message
oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message
ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Stupid from me

If Word is your mail editor in Outlook this is not working
Change it in Outlook: ToolsOptions..Mail format tab


--

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


"Ron de Bruin" wrote in message ...
Which Excel version do you use ?
Can you send me the workbook private.


--

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


"spences10" wrote in message ups.com...
Hi Ron,

i have amended the code as directed [please see below] and it still
only pasts the defined area and does not add the text as a string. :-(

Function TLMailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
"<br<br"

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = StrBody & RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Function


Ron de Bruin wrote:
I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message
oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message
ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

thats it Ron,

thank you very much
Ron de Bruin wrote:
Stupid from me

If Word is your mail editor in Outlook this is not working
Change it in Outlook: ToolsOptions..Mail format tab


--

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


"Ron de Bruin" wrote in message ...
Which Excel version do you use ?
Can you send me the workbook private.


--

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


"spences10" wrote in message ups.com...
Hi Ron,

i have amended the code as directed [please see below] and it still
only pasts the defined area and does not add the text as a string. :-(

Function TLMailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
"<br<br"

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = StrBody & RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Function


Ron de Bruin wrote:
I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message
oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message
ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

Hi Ron,

Last question [I think], now that I can send both the pasted area from
excel and some text. is there any way I can ensure that this happens
for other users, or will they all have to change their Outlook settings
as I have?


spences10 wrote:
thats it Ron,

thank you very much
Ron de Bruin wrote:
Stupid from me

If Word is your mail editor in Outlook this is not working
Change it in Outlook: ToolsOptions..Mail format tab


--

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


"Ron de Bruin" wrote in message ...
Which Excel version do you use ?
Can you send me the workbook private.


--

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


"spences10" wrote in message ups.com...
Hi Ron,

i have amended the code as directed [please see below] and it still
only pasts the defined area and does not add the text as a string. :-(

Function TLMailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
"<br<br"

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = StrBody & RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Function


Ron de Bruin wrote:
I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message
oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message
ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??

or will they all have to change their Outlook settings
Yes.

If you can change this setting (if possible with code, I never try) and users
like the Word interface to edit mail then I don't think they want you to change this setting.


--

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


"spences10" wrote in message ups.com...
Hi Ron,

Last question [I think], now that I can send both the pasted area from
excel and some text. is there any way I can ensure that this happens
for other users, or will they all have to change their Outlook settings
as I have?


spences10 wrote:
thats it Ron,

thank you very much
Ron de Bruin wrote:
Stupid from me

If Word is your mail editor in Outlook this is not working
Change it in Outlook: ToolsOptions..Mail format tab


--

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


"Ron de Bruin" wrote in message ...
Which Excel version do you use ?
Can you send me the workbook private.


--

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


"spences10" wrote in message
ups.com...
Hi Ron,

i have amended the code as directed [please see below] and it still
only pasts the defined area and does not add the text as a string. :-(

Function TLMailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
"<br<br"

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = StrBody & RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Function


Ron de Bruin wrote:
I have already tried this and no joy.

I not see that in your code
Note: I not use vbnewline and the HTMLBody line is different



--

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


"spences10" wrote in message
oups.com...
Hi Ron,

I have already tried this and no joy.

Confused = (

Ron de Bruin wrote:
Hi Scott

Use this line

StrBody = "Hi Colin, discretion request for your attention." & "<br<br"

And this

.HTMLBody = StrBody & RangetoHTML(rng)


--

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


"spences10" wrote in message
oups.com...
Hi Ron,

Thanks again for your help, I am struggling at the moment though.

Using the code supplied by yourself I have managed to paste from the
workbook into an outlook mail item, but adding a text string is proving
difficult as I can only have either the pasted item or the text string
but not both. = (

Sub MailDiscretionRequest()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set Rng = Nothing
'Set Rng = ActiveSheet.UsedRange
'You can also use a sheet name
Set Rng = Sheets("DiscretionRequest").Range("DiscReq")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

StrBody = "Hi Colin, discretion request for your attention." &
vbNewLine & vbNewLine

On Error Resume Next
With OutMail
.To = "Dolder, Colin : Business Banking Risk"
.CC = ""
.BCC = ""
.Subject = "DISCRETION REQUEST"
.HTMLBody = RangetoHTML(Rng)
'.Body = StrBody
'.Send
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

thanks,

Scott Spence
Ron de Bruin wrote:
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm

Must go now but get back to you this evening
Let me know if the second example is working for you

--

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


"spences10" wrote in message
ups.com...
Hi Ron,

This is fantastic!! thank you very much. There is one thing however, I
need to be able to add some text at the top of the e-mail. Oh and is it
possible to add an auto signature to the mail as well?


Ron de Bruin wrote:
Hi spences10

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm

--

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


"spences10" wrote in message
ups.com...
Hi,

I am trying to Copy the contents of an Excel worksheet into an Outlook
mail item, like a simple copy and paste.

Is there any code that can do this, I have examples that can populate
from the worksheet but I need it to be in a table format.

Can anyone help??