Sendind Email through Excel Macro

[PATTANAM]

Hi Team,

Have written code for active sheet sending mail. My requirement is I need to input Sub, TO & CC values through Cell ranges (Row and Colums).
I would request please help for the requirement.

Option Explicit
Sub Mail_Range_Outlook_Bodypp()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object


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

Set rng = Nothing
On Error Resume Next
Set rng = Sheets("MailRangeSelection").Range("B:L").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

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

On Error Resume Next
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Michael M]

Have a look at Post # 4 here
http://www.mrexcel.com/forum/showthread.php?640837-Email-attachment-code-with-email
Like this

.To =Range("A1").value
.CC = Range("B1").value
.BCC = Range("C1").value
.Subject = Range("D1").value

 

[PATTANAM]

Super

THank you Michael M!

It is working fine!

 

[Michael M]

Glad it worked...thanks for the feedback...