VBA code to send selected range in body of mail

Good day,

please help me with arranging VBA code to send range of cells in body of mail message.

I have bellow code, it is working but dont know how to display predefined range (A10:D50) in body of message. Also want to do that it will display only full cells.

Sub SendMAIL()


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

'Use hour to create a text
Select Case Hour(Time)
Case Is <= 12
TxtHello = "Good Morning," & vbNewLine
Case Is >= 12
TxtHello = "Good Afternoom," & vbNewLine
Case Is >= 18
TxtHello = "Good Evening," & vbNewLine
End Select



'Create message
On Error Resume Next
With OutMail
.to = "aaaa" 'Insert required address here ########
.CC = "aaaa"
.BCC = ""
.Subject = "subject"
.Body = TxtHello & "my message"

.Attachments.Add strPath & strFName
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With

'Delete any temp files created
Kill strPath & strFName
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing


End Sub

Dear,

sorry but bellow code is not working for me.

So i need a code that in body of message fill display content from range A10:D50.

It is possible to add it in my code?

find attached sample workbook...it is the same I will use in my real workbook.

When clicking on button nothing happens.

test.xlsm

Hi..

Richard provided some nice code for you.. taking 3 seconds to step through it would have shown you that the error you made was not adding the correct Sheet name..

Change this..

Please Login or Register  to view this content.

to this..

Please Login or Register  to view this content.

To step thought your code.. put a break point at the start.. and then use F8 to step thru... hover te cursor over variables to see there values as you go.. ( use Watch window also.. but maybe that come slater as you learn more)..

Dears,

MANY THANKS!...what a stupid error...

Next question..is possible to display messsage prior to sending?

Change :

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

already changet it..without any result

test it: test.xlsm

Yeap.. I got nothing.. seems Mail Envelope can NOT display before sends for the reasons Richard outlines..

Richard.. is the method you describe above kinda like a shorter version of the RangetoHtml function (RonDeBruin)?

ok noted above, i will use your method Thanks!

you are Excel Masters!

my last question: hod to add text after these range? (so introduction is already in code..I want also add "conclusion"). it possible to define font size and style for whole message? so that it will the same..

Ders,

please hel me with bellow code:

Sub TEST()
' Create PDF of active sheet and send as attachment.

Sheets("TEST").Select
Dim strPath As String, strFName As String
Dim OutApp As Object, OutMail As Object

'Create PDF of active sheet only
strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"

strFName = ActiveWorkbook.Name
strFName = Worksheets("test").Range("A1").Value & " " & ActiveSheet.Name & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

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

'Create message
On Error Resume Next
With OutMail
.To = "" .CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With

'Delete any temp files created
Kill strPath & strFName
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing




End Sub

It is possible to ammend these code that will generate PDF only from range and not entire sheet.

Have solved it.

hello,

Maybe anyone knows how to made that bellow code will sedn mail to mail adresses noted in cell N2

Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()



Sheets("test").Select




'Working in Excel 2002-2013
Dim Sendrng As Range

On Error GoTo StopMacro

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

'Note: if the selection is one cell it will send the whole worksheet
Sheets("test").Range("A1:E207 ").Select ' where MySheet is the VBA code name of the sheet containing the range in question."
Set Sendrng = Selection

'Create the mail and send it
With Sendrng

ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope

' Set the optional introduction field thats adds
' some header text to the email body.


With .Item
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "test"
.send
End With

End With
End With

StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False




End Sub

Good day,

is possible to ammend bellow code that will send mail to adresses noted in cell N2

Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()



Sheets("test").Select




'Working in Excel 2002-2013
Dim Sendrng As Range

On Error GoTo StopMacro

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

'Note: if the selection is one cell it will send the whole worksheet
Sheets("test").Range("A1:E207 ").Select ' where MySheet is the VBA code name of the sheet containing the range in question."
Set Sendrng = Selection

'Create the mail and send it
With Sendrng

ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope

' Set the optional introduction field thats adds
' some header text to the email body.
'.Introduction = TxtHello


With .Item
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "test"
.send
End With

End With
End With

StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False




End Sub