Macro for Email - Sending to email addresses in cell

[Brice]

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(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 = "(e-mail address removed)"
.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

 

[Ron de Bruin]

Hi Brice

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

 

[Simon Lloyd]

Replac
Code
-------------------

On Error Resume Nex
With OutMai
.To = "(e-mail address removed)
.CC = "
.BCC = "
.Subject = "Wire Intx
.HTMLBody = RangetoHTML(rng
.Display 'or use .Send as replacement to auto-send email withou
revie
End Wit

-------------------
wit
Code
-------------------

On Error Resume Nex
With OutMai
.To = "(e-mail address removed)
.CC = Sheets("Intx").Range("O3").Valu
.BCC = "
.Subject = "Wire Intx
.HTMLBody = RangetoHTML(rng
.Display 'or use .Send as replacement to auto-send email withou
revie
End Wit

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

Hello

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

Please kindly advise. Macro details below

Code
-------------------
' Don't forget to copy the function RangetoHTML in the module
Dim rng As Rang
Dim OutApp As Objec
Dim OutMail As Objec

Set rng = Nothin
On Error Resume Nex
'You can also use a range if you wan
Set rng = Sheets("Paymen
Intx").Range("$N$9:$O$18").SpecialCells(xlCellTypeVisible
On Error GoTo

If rng Is Nothing The
MsgBox "The selection is not a range or the sheet is protected" &
vbNewLine & "please correct and try again.", vbOKOnl
Exit Su
End I

With Applicatio
.EnableEvents = Fals
.ScreenUpdating = Fals
End Wit

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

On Error Resume Nex
With OutMai
.To = "(e-mail address removed)
.CC = "
.BCC = "
.Subject = "Wire Intx
.HTMLBody = RangetoHTML(rng
.Display 'or use .Send as replacement to auto-send email withou
revie
End Wit
On Error GoTo

With Applicatio
.EnableEvents = Tru
.ScreenUpdating = Tru
End Wit

Set OutMail = Nothin
Set OutApp = Nothin
End Su
-------------------

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com