Help Debugging Code : Placing a range in the body of an email.

[JamesGilchrist]

Hi All,

Topic - Help Debugging Code : Placing a range in the body of an email.

Am not programmer, I am cobbling together this and dont have the expertise to debug it! Can some one PLEASE help?

Sub CreateEmail()

'Clears a range that may cause a bug
Range("A501").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-500

'selects the range that I need placed in the body of an email WORKS GREAT
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B4:R" & LastRow).Select

'NOT MY CODE...this opens an email but the range selected in
'the previous step does not get placed into the body of the email.

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 = Selection.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")
    OutApp.Session.Logon
    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

 

[AlphaFrog]

Included with your original code above in the same VBA module, do you also have the code for the Function RangetoHTML ? You need it if you don't.

What line does it error on?

Also, don't forget to change the email "To:" address in the code...
.To = "ron@debruin.nl"

 

[JamesGilchrist]

Hi AlphaFrog,

I have put the RangetoHTML in....This code does not bugg out a highlight an error. It just doesn't deliver the desired result.

It opens up an email, but does not place the selection in the body.

Any help is appreciated.

 

[AlphaFrog]

As a test to see if the range is converted to HTML, add this MsgBox line to your code. The HTML code for the range should then be displayed in a message box.

    Set rng = Nothing
    On Error Resume Next
    Set rng = Selection.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
    [COLOR="Red"]MsgBox RangetoHTML(rng)[/COLOR]