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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top