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 = ""

'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

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
End With

'Delete the temporary file and restore screen updating

tWB.ChangeFileAccess Mode:=xlReadOnly
Kill tWB.FullName
tWB.Close SaveChanges:=False
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub

Download file with VBA code to send active sheet through outlook


  1. 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 .

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

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

  4. 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

    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?



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

  6. 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?

  7. 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.

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

  9. hi yougesh,

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

  10. 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

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

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

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

  14. This comment has been removed by the author.

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

    Thanks again

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

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

  18. 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!

  19. 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!

  20. This comment has been removed by the author.

  21. Nice post, thank you! There are lots of great tools but I like the most. It's a lightweight, friendly and free Chrome extension with good match rate.candidate sourcing tools

  22. Showbox is not an official app of Google so you have to download it from a trusted website or source. It is not easy to find and download the application as it is sideloaded from the platform. But we will tell you how you can download and install Latest Version of Showbox Apk on your device.

  23. Before the group email software "LISTSERV", emails were send manually, which is a very time consuming process. This group email software was freeware between `86 to `93, and nowadays it is a commercial product of "L-Soft", which is a company founded by Eric Thomas (the author of LISTSERV) in ` verifier

  24. Buat mereka yang ingin coba coba saja jadi diijinkan untuk lakukan taruhan dengan modal Rp 10rb saja
    bandar ceme terpercaya
    paito warna
    forum prediksi

  25. Shalom, Om swastiastu, Namo buddhaya, Salam kebajikan, pada artikel kali ini kami akan memberikan kepada kamu Panduan Cara Bet 2D Togel Klik4D Online yang tersedia di S128cash. Berhubung permainan togel adalah game legendaris di Indonesia, tahukah kamu kalau game togel sekarang bisa dimainkan secara online ? (Baca Selengkapnya Disini...)

  26. i never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful. visit this site

  27. thanks for posting this nice information.please keep posting.
    we are proving Digital marketing course in Bangalore with Live Projects. 100% placement, 20+ modules, 10+ certifications, Great discounts on course fees.


  29. Earn Rs.25000/- per month - Simple online Jobs - Are You Looking for Home-Based Online Jobs? - Are You a Student, Housewife, jobseeker ? - Are you ready to Work 1 to 2 Hours daily Online? - Do You need Guaranteed Payment Monthly? Then this is for You, - Clicking on their Advertisement E-mails. - Submitting their Data\'s online. - Reading their Advertisement Sms. - Filling Forms on their websites, etc,. FREE to Join >>
    9PJK1587500784 2020-04-23 01:58:08

  30. I truly like your style of blogging. I added it to my preferred's blog webpage list and will return soon…pmp certification in malaysia

  31. Great to become visiting your weblog once more, it has been a very long time for me. Pleasantly this article i've been sat tight for such a long time. I will require this post to add up to my task in the school, and it has identical subject along with your review. Much appreciated, great offer.

    data science course in noida

  32. Superb exertion to make this blog more awesome and appealing.

  33. enuinely refreshing scrutinizing your blog sections.Huge thankful for the significant data.
    hrdf claimable training

  34. I am sure that this is going to help a lot of individuals. Keep up the good work. It is highly convincing and I enjoyed going through the entire blog.
    data science training

  35. Super site! I am Loving it!! Will restore again, Im taking your food in like manner, Thanks.
    what is the difference between analysis and analytics

  36. Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
    Cyber Security Course in Bangalore

  37. Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
    Cyber Security Training in Bangalore

  38. Bolavita - Situs Permainan Online Aman Dan Terpercaya

    Tersedia Bola | Live Casino | Bola Tangkas | Sabung Ayam Online | Slot Games |Togel Online Aman Dan Terpercaya

    Melayani Deposit Via Pulsa Telkomsel | XL | Gopay | OVO | LinkAja | DANA Serta Semua Bank Indonesia

    Kontak Bolavita

  39. Nice post! you are giving right information to everyone one its good blog to all of us who are going to predict in future Otherwise any one who want to learn advance excel contact us on 9311002620 or visit

  40. Twitter Promotion is what I will get when promoting my blog but you can also buy instagram followers and more.

  41. Thanks for posting the best information and the blog is very informative.Data science course in Faridabad

  42. Very well written article. It was an awesome article to read. Complete rich content and fully informative. I totally Loved it. Email Finder