Use VBA to email active worksheet without using an attachment

[BetterBeef]

I have looked around the web as well as this forum and I cannot find an answer to my question, which is why I am posting it here.

I would like to put some VBA code together that allows me to embed the active sheet in my Excel workbook into an email. Most of what I have seen on the web is how to attach an active workbook into an email.

Essentially I want the Macro to do the following task that I would do manually.

File, Send To, Mail Recipient not as attachment.

Any idea of how to do this?

 

[VoG]

Take a look here http://www.rondebruin.nl/sendmail.htm

 

[BetterBeef]

Which one of these do I use? I had seen this link from someone else's previous post that you had answered. I do not want to create a new workbook. I should be able to send the email without making an attachment.

Which procedure from the website should I use because it appears that they all copy the worksheet into a new workbook and send it as an attachment?

 

[VoG]

Choose one from the list under Outlook object model (body).

 

[BetterBeef]

Thank you. I guess I did not scroll down far enough.

Thanks again!

 

[BetterBeef]

I guess I do have a question. How do I modify the code so that the user can enter the correct email addresses him or herself? The way the code is written, the email addresses and subject lines are embedded in the code.

I have listed the code that should probably be modified below for your convenience.

Sub EmailWorksheet()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 
    Set rng = Nothing
    'Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    Set rng = Sheets("COPY TO EMAIL").UsedRange
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = "[EMAIL="ron@debruin.nl"]ron@debruin.nl[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .HTMLBody = RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[VoG]

Try

Sub EmailWorksheet()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 
    Set rng = Nothing
    'Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    Set rng = Sheets("COPY TO EMAIL").UsedRange
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = InputBox("Enter e-mail address")
        .CC = ""
        .BCC = ""
        .Subject = InputBox("Enter subject line")
        .HTMLBody = RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[BetterBeef]

I guess the issue with that method is that I need to put a distribution list into the "To:" field. Is this possible with this method?

 

[VoG]

This worked for me

.To = InputBox("Enter e-mail addresses separated by ;")

 

[BetterBeef]

The distribution list I am referring to is defined in Outlook. What I would like is for the user to type in an Outlook distribution list name and have it go out to those people. Do you know if this will work using an input box like this?