Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

Hi Ron,

In the body of the e-mail if possible
--
John


"Ron de Bruin" wrote:

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

Try this tester John

Copy the macro and function in a standard module
This example will send the selection in the body of the mail

Change the sheet and range here where the mail addresses are

Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A5:A105").Cells.SpecialCells(xlCellTypeCon stants)

Tip: use Display to test the code in this line
.Send 'or use .Display




Sub Mail_Selection_Range_Outlook_Body()
' 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 cell As Range
Dim strto As String
Dim AddressRange As Range

On Error Resume Next
Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)

For Each cell In AddressRange
If cell.Value Like "?*@?*.?*" And Application.CountIf( _
AddressRange.Parent.Range(AddressRange.Cells(1).Ad dress _
& ":" & cell.Address), cell.Value) = 1 Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use the range if you want without selecting
'Set rng = Sheets("Sheet1").Range("C4:J105").SpecialCells(xlC ellTypeVisible)
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 = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
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


--

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


"John" wrote in message ...
Hi Ron,

In the body of the e-mail if possible
--
John


"Ron de Bruin" wrote:

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

HI Ron

Thanks for your help - it all works when I put the e-mail addresses iin
cells A5:A105 but if these are filled from a lookup table it does not work.
I have checked they are hyperlinks and even added "mailto:" and they still do
not work

Any suggestions
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Ron de Bruin" wrote:

Try this tester John

Copy the macro and function in a standard module
This example will send the selection in the body of the mail

Change the sheet and range here where the mail addresses are

Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A5:A105").Cells.SpecialCells(xlCellTypeCon stants)

Tip: use Display to test the code in this line
.Send 'or use .Display




Sub Mail_Selection_Range_Outlook_Body()
' 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 cell As Range
Dim strto As String
Dim AddressRange As Range

On Error Resume Next
Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)

For Each cell In AddressRange
If cell.Value Like "?*@?*.?*" And Application.CountIf( _
AddressRange.Parent.Range(AddressRange.Cells(1).Ad dress _
& ":" & cell.Address), cell.Value) = 1 Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use the range if you want without selecting
'Set rng = Sheets("Sheet1").Range("C4:J105").SpecialCells(xlC ellTypeVisible)
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 = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
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


--

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


"John" wrote in message ...
Hi Ron,

In the body of the e-mail if possible
--
John


"Ron de Bruin" wrote:

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

Hi John

See the help for speciallcells

You must change xlCellTypeConstants to xlCellTypeFormulas

--

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


"John" wrote in message ...
HI Ron

Thanks for your help - it all works when I put the e-mail addresses iin
cells A5:A105 but if these are filled from a lookup table it does not work.
I have checked they are hyperlinks and even added "mailto:" and they still do
not work

Any suggestions
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Ron de Bruin" wrote:

Try this tester John

Copy the macro and function in a standard module
This example will send the selection in the body of the mail

Change the sheet and range here where the mail addresses are

Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A5:A105").Cells.SpecialCells(xlCellTypeCon stants)

Tip: use Display to test the code in this line
.Send 'or use .Display




Sub Mail_Selection_Range_Outlook_Body()
' 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 cell As Range
Dim strto As String
Dim AddressRange As Range

On Error Resume Next
Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)

For Each cell In AddressRange
If cell.Value Like "?*@?*.?*" And Application.CountIf( _
AddressRange.Parent.Range(AddressRange.Cells(1).Ad dress _
& ":" & cell.Address), cell.Value) = 1 Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use the range if you want without selecting
'Set rng = Sheets("Sheet1").Range("C4:J105").SpecialCells(xlC ellTypeVisible)
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 = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
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


--

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


"John" wrote in message ...
Hi Ron,

In the body of the e-mail if possible
--
John


"Ron de Bruin" wrote:

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

Thanks for your help - all working correctly now
--
John


"Ron de Bruin" wrote:

Hi John

See the help for speciallcells

You must change xlCellTypeConstants to xlCellTypeFormulas

--

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


"John" wrote in message ...
HI Ron

Thanks for your help - it all works when I put the e-mail addresses iin
cells A5:A105 but if these are filled from a lookup table it does not work.
I have checked they are hyperlinks and even added "mailto:" and they still do
not work

Any suggestions
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Ron de Bruin" wrote:

Try this tester John

Copy the macro and function in a standard module
This example will send the selection in the body of the mail

Change the sheet and range here where the mail addresses are

Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A5:A105").Cells.SpecialCells(xlCellTypeCon stants)

Tip: use Display to test the code in this line
.Send 'or use .Display




Sub Mail_Selection_Range_Outlook_Body()
' 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 cell As Range
Dim strto As String
Dim AddressRange As Range

On Error Resume Next
Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)

For Each cell In AddressRange
If cell.Value Like "?*@?*.?*" And Application.CountIf( _
AddressRange.Parent.Range(AddressRange.Cells(1).Ad dress _
& ":" & cell.Address), cell.Value) = 1 Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use the range if you want without selecting
'Set rng = Sheets("Sheet1").Range("C4:J105").SpecialCells(xlC ellTypeVisible)
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 = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
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


--

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


"John" wrote in message ...
Hi Ron,

In the body of the e-mail if possible
--
John


"Ron de Bruin" wrote:

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John

You are welcome

Have a nice day

--

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


"John" wrote in message ...
Thanks for your help - all working correctly now
--
John


"Ron de Bruin" wrote:

Hi John

See the help for speciallcells

You must change xlCellTypeConstants to xlCellTypeFormulas

--

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


"John" wrote in message ...
HI Ron

Thanks for your help - it all works when I put the e-mail addresses iin
cells A5:A105 but if these are filled from a lookup table it does not work.
I have checked they are hyperlinks and even added "mailto:" and they still do
not work

Any suggestions
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Ron de Bruin" wrote:

Try this tester John

Copy the macro and function in a standard module
This example will send the selection in the body of the mail

Change the sheet and range here where the mail addresses are

Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A5:A105").Cells.SpecialCells(xlCellTypeCon stants)

Tip: use Display to test the code in this line
.Send 'or use .Display




Sub Mail_Selection_Range_Outlook_Body()
' 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 cell As Range
Dim strto As String
Dim AddressRange As Range

On Error Resume Next
Set AddressRange = ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)

For Each cell In AddressRange
If cell.Value Like "?*@?*.?*" And Application.CountIf( _
AddressRange.Parent.Range(AddressRange.Cells(1).Ad dress _
& ":" & cell.Address), cell.Value) = 1 Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use the range if you want without selecting
'Set rng = Sheets("Sheet1").Range("C4:J105").SpecialCells(xlC ellTypeVisible)
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 = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
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


--

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


"John" wrote in message ...
Hi Ron,

In the body of the e-mail if possible
--
John


"Ron de Bruin" wrote:

Hi John

Body or attachment

--

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


"John" wrote in message ...
Hi

In cells A5:A105 I have e-mail addresses that are populated from a look up
table. I need to send the section C4:J105 to all the e-mail addresses but
the addresses are often duplicated in the range A5:A105 and I only want the
e-mail address to show once. Also some of the cells have more than one e-mail
address in it.

I also have a seperate list that needs to be CC in the same e-mail.

Thanks in advance
--
John