My VBA code emailing the whole workbook instead of one sheet!!

[Samo]

Hi guy,

My VBA code below is emailing the whole workbook instead of one worksheet "DATA", despite the fact i followed the rules, please i need help? i just want to email DataSheet, which is named "DATA" ?? SEE the code below.

15650

Regards,

Samo

[Samo]

Just in case the code photo wasn't clear, i split it into two parts:
1565115652

[gmayor]

Your code sends the fukll workbook because that is what you have instructed it to do.

.Attachments.Add ReportBook.FullName
You had defined ReportBook as the ActiveWorkbook at the start of your code.

You should copy the active sheet to a new workbook and attach that workbook.

This was covered at http://www.vbaexpress.com/kb/getarticle.php?kb_id=326

When posting code please paste the code using the code tags and not a screen shot which is hard to read and impossible to test.

[Samo]

Hi gmayor,

I followed the same instructions mate, they defined WB as ActiveWorkbook as well, so where is the problem? check the link you sent me, it is the same as i did !!

[gmayor]

The active workbook defined as WB is the COPY in the example. In yours ReportBook is the ORIGINAL. You need to read the linked code again as it is not like yours.

Perhaps it will make more sense if you name the two workbooks separately e.g.

Option Explicit

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
OriginalWB As Workbook, _
CopyWB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
Set OriginalWB = ActiveWorkbook
ActiveSheet.Copy

Set CopyWB = ActiveWorkbook
FileName = "Temp.xlsx"
On Error Resume Next
Kill "C:\Path\" & FileName
On Error GoTo 0
CopyWB.SaveAs FileName:="C:\Path\" & FileName

On Error Resume Next
'Get Outlook if it's running
Set oApp = GetObject(, "Outlook.Application")
'Outlook wasn't running, start it from code
If Err <> 0 Then
Set oApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0

'Create and show the outlook mail item
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
'.Subject = "Look at my workbook!"
.Attachments.Add CopyWB.FullName
.Display
End With

CopyWB.Close SaveChanges:=False
'Delete the temporary file
Kill "C:\Path\" & FileName


'Restore screen updating and release Outlook
lbl_Exit:
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
Set CopyWB = Nothing
Set OriginalWB = Nothing
Exit Sub
End Sub

[Samo]

Thanks a lot for your message, so basically what you are saying is that after the step of copying the activeworksheet we open new workbook as activeWorkbook in which we save the copied worksheet right? and this new workbook is the one that will be saved and emailed later on right?


Thanks.

Samo

[gmayor]

ActiveSheet.Copy creates a new workbook which becomes the active workbook. It is that workbook that you need to associate with the named variable, so you do that AFTER the copy process. Your original code set variable to the activeworkbook BEFORE the copy process. It then remains associated with the original workbook which is why that was added to the message. My example above provides two named variables one for each workbook so you can better see the process.

[Samo]

Great stuff, thanks a lot mate, problem solved :)