Saturday, July 24, 2010

Excel Macros : Send Email with VBA

The Code I am sharing with you today is capable of sending active sheet of your workbook through outlook mail to email ID specified by you. You will not get any warning messages and the email will be sent using outlook profile available on your machine.

This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.

You will need to copy this code to regular VBA module of your workbook

Sub EmailActiveSheetWithOutlook()
    
    Dim oApp, oMail As Object, _
    tWB, cWB As Workbook, _
    FileName, FilePath As String

    Application.ScreenUpdating = False

'Set email id here, it may be a range in case you have email id on your worksheet
    
    Mailid = "yogesh@yogeshguptaonline.com"
    
'Write your email message body here , add more lines using & vbLf _ at the end of each line
    
    Body = "Please find enclosed " & vbLf _
    & vbLf _
    & "Thanks & Regards"
    
   
'Copy Active Sheet and save it to a temporary file

    Set cWB = ActiveWorkbook
    ActiveSheet.Copy
    
    Set tWB = ActiveWorkbook
    FileName = "Temp.xls" 'You can define the name
    FilePath = Environ("TEMP")
    
    On Error Resume Next
    Kill FilePath & "\" & FileName
    On Error GoTo 0
    Application.DisplayAlerts = False
    tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
    Application.DisplayAlerts = True
     
'Sending email through outlook

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        .To = Mailid
        .Subject = "Update Message Subject here"
        .Body = Body
        .Attachments.Add tWB.FullName
        .send
    End With
     
'Delete the temporary file and restore screen updating
    
    tWB.ChangeFileAccess Mode:=xlReadOnly
    Kill tWB.FullName
    tWB.Close SaveChanges:=False
    cWB.Activate
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
End Sub



Download file with VBA code to send active sheet through outlook

Posted by Yogesh Gupta at 11:24 PM
Labels: , ,

19 comments:

  1. UnknownDecember 14, 2010 at 11:58 AM

    APTIMASTERS is basically an online aptitude site which provides the help for the students to make their aptitude skills more enhanced so that they can overcome the entrance examinations for CAT,MAT,UPSC ,Placement Exams etc .

    ReplyDelete
  2. suparthaSeptember 2, 2011 at 6:13 PM

    Please help me in counting the total no of common rows in excel vba

    ReplyDelete
  3. UnknownOctober 12, 2011 at 3:29 PM

    Very good Yogesh Sir.. Good Blog.. We also develop advance automation programs in excel at infocorpsolutions.net

    ReplyDelete
  4. NitinNovember 11, 2011 at 2:45 PM

    Hi Yogesh,

    I'm using a macro for sending an email with a workbook in my directory and the file name in an excel sheet. The extract of the code is:

    .To = EmailAddr
    .Cc = CRecipient
    .Subject = Subj
    .Body = Msg
    attach_file = "C:\directory" & cell.Offset(0, 10).Value & ".xlsx"
    .Attachments.Add attach_file
    .Display
    .Send

    The "cell.Offset(0, 10).Value" contains the file name in the excel sheet and I've to paste the file name in the sheet for attaching the file in the email. Is there a way I can make the macro read the file name by picking just a part of the file name and location of the file attach the file to the email?

    Regards,

    Nitin

    ReplyDelete
  5. UnknownMay 15, 2012 at 6:20 PM

    Thanks alot!!!
    It really works. Keep on going!

    ReplyDelete
  6. JGMay 24, 2012 at 9:36 PM

    This result of this post has given me much priase at work! thank you!
    What would the code be to copy all sheets into a new Workbook and email?

    ReplyDelete
  7. AnonymousAugust 22, 2012 at 2:08 AM

    Can you please tell me how to include multiple email ids for this macro. I want to send my active work sheet to multiple receipients.
    Thanks
    SKS

    ReplyDelete
  8. JacksonSeptember 20, 2012 at 5:09 PM

    which part of the code can I delete to use it only to send attachments?

    ReplyDelete
  9. UnknownOctober 20, 2012 at 8:44 PM

    hi yougesh,

    your scripts are very very useful. it saves lots of time. hats off to you.

    ReplyDelete
  10. Jesper Klug AndersenOctober 22, 2012 at 5:51 PM

    Hi Yougesh,

    This works like a charm on most PC's, but I have one user that will get an Outlook notification whether to allow or deny sending this email. Do you have any idea on how to get this suppressed or a workaround not to see this on some PC's (at the moment only one).

    Thanks in advance,
    Jesper Andersen

    ReplyDelete
  11. Deepak Kiran GJanuary 14, 2013 at 8:21 PM

    I want to know, how to send mail from Zimbra account. Please let me know.

    ReplyDelete
  12. UnknownJanuary 31, 2013 at 5:56 AM

    How would I simply add more than one recipient's email address to send to?

    ReplyDelete
  13. UnknownJanuary 31, 2013 at 5:56 AM

    How would I simply add more than one recipient's email address to send to?

    ReplyDelete
  14. UnknownFebruary 14, 2013 at 4:23 PM

    This comment has been removed by the author.

    ReplyDelete
  15. PECFebruary 21, 2013 at 10:55 PM

    This is great and efficient. Thank you! Is there a way to modify so that it prompts for the email address?

    Thanks again

    ReplyDelete
  16. Sathish KumarApril 18, 2013 at 11:39 AM

    Hello Yogesh, You are Awesome !! I'm a Fan of yours !!

    ReplyDelete
  17. UnknownAugust 13, 2013 at 9:55 PM

    What changes are required in this code to send to an email listed in Cell A1 in the excel sheet?

    ReplyDelete
  18. UnknownOctober 3, 2014 at 10:56 PM

    Is there a way to trigger an auto email to go out once a certain amount hits a spreadsheet? For example, I'm tracking several items on inventory. Once they hit a specific number, the word "order" automatically populates. I'd like an email to automatically go to the person who would then place the order. Is that possible? Thank you!

    ReplyDelete
  19. UnknownOctober 3, 2014 at 10:56 PM

    Is there a way to trigger an auto email to go out once a certain amount hits a spreadsheet? For example, I'm tracking several items on inventory. Once they hit a specific number, the word "order" automatically populates. I'd like an email to automatically go to the person who would then place the order. Is that possible? Thank you!

    ReplyDelete

Subscribe to: Post Comments (Atom)