email using outlook(I havent used VBA for a while..help :))

[0 Agios]

Sub Mail_Selection_Range_Outlook_Body()
' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = Selection.SpecialCells(xlCellTypeVisible)
' You can also use a range with the following statement.
' Set rng = Sheets("YourSheet").Range("D4:D12").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")
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)
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
.Send
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub



this where I get stuck. '.HTMLBody = RangetoHTML(rng)
do I have to create a range ? and is the name of the range RangetoHTML ? using names correct?

 

[Ravinder Kumar]

Hi Agios,

Coding is working fine what do you want.


Ravinder

 

[0 Agios]

Hi Agios,

Coding is working fine what do you want.


Ravinder

The coding stops here when I try to run this

this where I get stuck. '.HTMLBody = RangetoHTML(rng)
do I have to create a range ? and is the name of the range RangetoHTML ? using names correct?

 

[0 Agios]

The coding stops here when I try to run this

this where I get stuck. '.HTMLBody = RangetoHTML(rng)
do I have to create a range ? and is the name of the range RangetoHTML ? using names correct?

bump

 

[BrianExcel]

Yes you have to set the range. Right now the first line of code you have (after the dim statements) sets the range to nothing (Set rng = nothing).

You have to set the range by doing the following.

Set rng = INSERT WHAT YOU WANT TO SET IT AS HERE.

What are you trying to set as the range? in other words, what do you want the range to contain? What sheet and/or what cells?

 

[BrianExcel]

You will also want to change .Send to .Display so you can see first that your code is actually working.

 

[0 Agios]

How about printing, not the page but the email with all the emails to: BCC etc

 

[BrianExcel]

Not sure what you mean.

This code should open a new email message within Outlook and you can print the message from there.

 

[0 Agios]

Not sure what you mean.

This code should open a new email message within Outlook and you can print the message from there.

I agree, but I want it to printautomatically as I send the eamail with the .send command, I tried printing automation and it works but prints my excel page not the emailed page with the TO, CC, AND BCC. Thanks for your help Brian.