VBA challenged Needs ALOT of help please?

[latarshac]

Hello all, I am very VBA challenged and using it to create a simple process of emailing a Named Range in the Body of an Outlook email. I'm using Ron de Bruin's but cannot find the command that will use the Named Range versus the Selected Range.

I hope that makes sense, I don't know the terminology.

here is the code that I "think" I should use, however I'm not certain where I would assign the Named Range in order for the Selection to only chose my named range before it sends the body text into Outlook.

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
'Working in Excel 2002-2013
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range

On Error GoTo StopMacro

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

'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = Worksheets("Sheet1").Range("A1:B15")

'Remember the activesheet
Set AWorksheet = ActiveSheet

With Sendrng

' Select the worksheet with the range you want to send
.Parent.Select

'Remember the ActiveCell on that worksheet
Set rng = ActiveCell

'Select the range you want to mail
.Select

' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope

' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = "This is test mail 2."

With .Item
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "My subject"
.Send
End With

End With

'select the original ActiveCell
rng.Select
End With

'Activate the sheet that was active before you run the macro
AWorksheet.Select

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

End Sub

 

[iliace]

Please use [ CODE ] tags in the future to make it easier for others to read your code.

There's a lot of "selecting" going on in this macro, but I don't see where it's using the Selected Range for anything. My best guess is you want to modify this line:

Set Sendrng = Worksheets("Sheet1").Range("A1:B15")

to be something more like

Set Sendrng = Worksheets("Sheet1").Range("MyNamedRange")

where the MyNamedRange is the Named Range you want to use.

Hope that's helpful, post back with any additional information.