Email Selection From Excel, No Selection Copied

[billydavey]

Hi Guys,

Hoping you can help as I'm really struggling with this.

I'm trying to email a range from excel into the body of an email, all using Access VBA.

I've used Ron De Bruin's excellent VBA code to do this, however, I keep getting the error that 'The selection is not a range or the sheet is protected".

The sheet is not protected and stepping through the code I can see that the range is being selected (highlighted) so I'm stumped as to why I'm receiving this error.

If I comment out the error capture code, the procedure continues to run through but nothing is pasted into the mail body in Outlook.

Below is the code I'm using:

Sub Mail_Selection_Range_Outlook_Body()
'Working in Excel 2000-2013
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
'Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a fixed range if you want
Set rng = excel.Sheets("Sheet1").Range("A1:F35").Select.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 excel.Application
.EnableEvents = False
.ScreenUpdating = False
End With

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

On Error Resume Next
With OutMail
.To = "me@me.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[WasWodge]

Try taking the select out of the middle of the Set rng line i.e.

Please Login or Register  to view this content.

and make sue that you are not in R1C1 reference style i.e. you have column letters and not numbers showing.

and please use code tags!!!!!!!

[billydavey]

Thanks for the response and apologies for not putting code tags in.

I've tried replacing .select with the above but the same issue occurs.

I can see that the range is being 'selected' but for some reason it appears that it isn't recognising that there are entries in the cells, hence I'm getting the error capture or if removing that just nothing pasted into the Email?

[WasWodge]

The code below works fine for me (as long as the sheet is in A1 reference style) and obviously using Outlook

by the way

I can see that the range is being 'selected'

how can you see it being selected if you actually removed the select as I posted????

Please Login or Register  to view this content.

[billydavey]

Thanks for the reply.

When I say that I can see the range is being selected, I mean that with screenupdating turned off you can see the range of cells become highlighted with the select statement.

I can get the code to work in Excel, just not when referencing Excel from Access.

[WasWodge]

I haven't got access in front of me until I knock off and so I won't be able to look at it until then.
Hopefully someone might jump in before then.

[billydavey]

Thanks for looking.