Sending one sheet only

Dûrs

New Member
Joined
Jul 10, 2007
Messages
11
I'm new to VBA and pretty much making it up as I go along.

At the moment I have the following line of code:

ActiveWorkbook.SendMail Recipients:="godross22@hotmail.com", Subject:=Range("C4") & " TELEPHONE REFERRAL " & Range("C20")

It's working fine except it sends the entire document whereas the only sheet that needs to be sent is the current one (named "TELEPHONE REFFERAL FORM"). The additional sheets tabs are surplus and fairly bulky so they take up precious space in the recipient's inbox.

I've tried changing "ActiveWorkbook" to "ActiveSheet" and "ActiveWorksheet" but all I've got from that are errors, can anyone help?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mplees

Active Member
Joined
Feb 6, 2006
Messages
351
Hi,

I do something similar with a couple of my workbooks. Maybe the following code might help you out (I apologise in advance _ I'm no VBA expert, but the code seems to work well):

Code:
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

            Dim vSSfn As String, vSSfn1 As String
            vSSfn = "worksheet name.xls"
            ActiveSheet.Copy
            ActiveSheet.SaveAs Filename:=vSSfn
            With objMail
                .Attachments.Add (ActiveWorkbook.Path & "\" & vSSfn)
                '.Display or .Send

            End With

            vSSfn1 = (ActiveWorkbook.Path & "\" & vSSfn)
            Set objMail = Nothing
            Set objOL = Nothing
            ActiveWorkbook.Close
            Kill vSSfn1

...which basically takes the active worksheet, saves it, attaches to an email, & then deletes the file after attachment. Note that this only works with Outlook.

Hope this helps in some way.

Regards,

Mark
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top