Hello,

I have reviewed Ron de Bruin's website
(http://www.rondebruin.nl/sendmail.htm) and used his code to create a macro
to send email from Excel. I would like to add/edit code so I can send email
To: email addresses located in cell O3 of worksheet name "Intx"

Please kindly advise. Macro details below.


Sub Mail_Selection_Range_Outlook_Body()
' 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("Payment
Intx").Range("$N$9:$O$18").SpecialCells(xlCellType Visible)
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 = "
.CC = ""
.BCC = ""
.Subject = "Wire Intx"
.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

See the tips page
http://www.rondebruin.nl/mail/tips2.htm

--

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


"Brice" wrote in message ...
Hello,

I have reviewed Ron de Bruin's website
(http://www.rondebruin.nl/sendmail.htm) and used his code to create a macro
to send email from Excel. I would like to add/edit code so I can send email
To: email addresses located in cell O3 of worksheet name "Intx"

Please kindly advise. Macro details below.


Sub Mail_Selection_Range_Outlook_Body()
' 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("Payment
Intx").Range("$N$9:$O$18").SpecialCells(xlCellType Visible)
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 = "
.CC = ""
.BCC = ""
.Subject = "Wire Intx"
.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

Replace
Code:
--------------------

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Wire Intx"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send as replacement to auto-send email without
review
End With

--------------------
with
Code:
--------------------

On Error Resume Next
With OutMail
.To = "
.CC = Sheets("Intx").Range("O3").Value
.BCC = ""
.Subject = "Wire Intx"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send as replacement to auto-send email without
review
End With

--------------------
Brice;198314 Wrote:
Hello,

I have reviewed Ron de Bruin's website
('Example Code for sending mail from Excel'
(http://www.rondebruin.nl/sendmail.htm)) and used his code to create a
macro
to send email from Excel. I would like to add/edit code so I can send
email
To: email addresses located in cell O3 of worksheet name "Intx"

Please kindly advise. Macro details below.



Code:
--------------------
Sub Mail_Selection_Range_Outlook_Body()
' 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("Payment
Intx").Range("$N$9:$O$18").SpecialCells(xlCellType Visible)
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 = "
.CC = ""
.BCC = ""
.Subject = "Wire Intx"
.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

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54603