• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA email sheet help needed!

MikeTE

New Member
hi guys!

I'm not good with VBA so I need help amending this code.
I was trying to create macro to send form to specific email. this code works but I'm struggling to find a way to send only one sheet instead of full workbook. I tried replacing thisWorkbook with ThisWorkbook.worksheets("Form") but that didn't work.
can anybody please help me?

>>> start to use ' code - tags ', which have noticed for You many times <<<
Code:
Sub send_mail()
Dim outlookOBJ As Object
Dim mItem As Object
Set outlookOBJ = CreateObject("Outlook.application")
Set mItem = outlookOBJ.createItem(olMailItem) With mItem
.to = "miketheemperor@gmail.com"
.Subject = "test" .body = "test"
.attachments.Add ThisWorkbook.path & "\" & ThisWorkbook.Name
.display
End With
End Sub
 
Last edited by a moderator:
This macro saves the ACTIVE SHEET as an .xlsx file and attaches it to the email :

Code:
Option Explicit

Sub Email_Worksheet_As_Workbook()
    ActiveSheet.Copy
    With ActiveWorkbook
        '.Windows(1).Visible = False
        Application.DisplayAlerts = False
        .SaveAs Environ("TMP") & "\tmp.xlsx", FileFormat:=xlWorkbookDefault, ConflictResolution:=xlLocalSessionChanges
        Application.DisplayAlerts = True
        .Close (True)
    End With
    
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = "me@yahoo.com"
        .Subject = "Worksheet: " & ActiveSheet.Name
        .Body = ""
        .Attachments.Add Environ("TMP") & "\tmp.xlsx"
        .Display
        '.send
    End With
End Sub
 

Attachments

  • WORKS Attached Sheet 1 as Email.xlsm
    18.9 KB · Views: 12
thanks a lot that works magic! genius! i'm still failing to understand why some codes may be adjusted and some can't
 
"i'm still failing to understand why some codes may be adjusted and some can't "

If you mean why your original code was not utilize en masse, I didn't have the time to review the posted code and adjust. The priority here was to provide you an answer that worked in short order.
 
Back
Top