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

74 comments:

  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 .

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

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

    ReplyDelete
  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
    .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. Thanks alot!!!
    It really works. Keep on going!

    ReplyDelete
  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?

    ReplyDelete
  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.
    Thanks
    SKS

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

    ReplyDelete
  9. hi yougesh,

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

    ReplyDelete
  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

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

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

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

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. 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. Hello Yogesh, You are Awesome !! I'm a Fan of yours !!

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

    ReplyDelete
  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!

    ReplyDelete
  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!

    ReplyDelete
  20. This comment has been removed by the author.

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

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

    ReplyDelete
  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 `94.email verifier

    ReplyDelete
  24. Buat mereka yang ingin coba coba saja jadi diijinkan untuk lakukan taruhan dengan modal Rp 10rb saja
    asikqq
    dewaqq
    sumoqq
    interqq
    pionpoker
    bandar ceme terpercaya
    hobiqq
    paito warna
    forum prediksi

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

    ReplyDelete
  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

    ReplyDelete
  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.
    https://onlineidealab.com/digital-marketing-courses-in-bangalore/


    ReplyDelete
  28. http://www.yogeshguptaonline.com/2012/02/excel-addins-spell-currency-excel-addin.html?showComment=1587588227343#c1226145676207704524

    ReplyDelete
  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 >> http://dailyonlinejobs.com
    9PJK1587500784 2020-04-23 01:58:08

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  32. Superb exertion to make this blog more awesome and appealing.
    https://360digitmg.com/course/data-analytics-using-python-r

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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 https://linktr.ee/bolavita

    ReplyDelete
  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 https://www.htsindia.com/Courses/Business-Analytics/adv-excel-training-course

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

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

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

    ReplyDelete
  43. Thank you for sharing this valuable information about Advance Excel I really appreciate your hard work you put into your blog and detailed information you provide. Further More Information About Advance Excel Training Institute in Delhi So Contact Here-+91-9311002620 Or Visit Website- https://htsindia.com/Courses/business-analytics/adv-excel-training-course


    ReplyDelete
  44. Thanks for sharing this informative post. It's really kind of content that I'm looking for a very helpful post by the way. If anyone looking for best Sas training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

    ReplyDelete
  45. Thanks for sharing this post vert impressive aprt form that if anyone look for Python training institute in Delhi Contact Here-+91-9311002620 Or Visit Website- https://www.htsindia.com/Courses/python/python-training-institute-in-delhi

    ReplyDelete
  46. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science in bangalore

    ReplyDelete
  47. Wow, happy to see this awesome post. I hope this think help any newbie for their awesome work and by the way thanks for share this awesomeness, i thought this was a pretty interesting read when it comes to this topic. Thank you..
    Artificial Intelligence Course

    ReplyDelete
  48. I need to thank you for this very good read and i have bookmarked to check out new things from your post. Thank you very much for sharing such a useful article and will definitely saved and revisit your site.
    Data Science Course

    ReplyDelete
  49. Thank you for sharing this type of content in your post. Your content is really good, by the way. If anyone looking forbest Sas training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

    ReplyDelete
  50. Thank you for sharing this type of content in your post. Your content is really good, by the way. If anyone looking forbest Sas training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

    ReplyDelete
  51. Your site is truly cool and this is an extraordinary moving article and If it's not too much trouble share more like that. Thank You..
    Digital Marketing Course in Hyderabad

    ReplyDelete
  52. Thank a lot. You have done excellent job. I enjoyed your blog . Nice efforts
    Data Science Certification in Hyderabad

    ReplyDelete
  53. your blog everyday and try to learn something from your blog. Thank you and I'm waiting for your new post.
    best data science institute in hyderabad

    ReplyDelete
  54. Thanks for sharing this amazing post this is the content i really looking for, it's very helpful i hope you will continue your blogging anyway if anyone looking for Sas training institute in delhi contact us +91-9311002620 visit-https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

    ReplyDelete
  55. Thanks for sharing this informative post. It's really kind of content that I'm looking for a very helpful post by the way. If anyone looking for best Sas training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

    ReplyDelete
  56. Thanks for sharing this informative post. It's really kind of content that I'm looking for a very helpful post by the way. If anyone looking for best Sas training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

    ReplyDelete
  57. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    best data science institute in hyderabad

    ReplyDelete
  58. A big thanks for sharing this post by the way if anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in pune, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-pune.html

    ReplyDelete
  59. A big thanks for sharing this post by the way if anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in pune, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-pune.html

    ReplyDelete
  60. Anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in pune, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-pune.html

    ReplyDelete
  61. Anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in pune, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-pune.html

    ReplyDelete
  62. Excellent Blog! I would like to thank you for the efforts you have made in writing this post. Gained lots of knowledge.
    Data Analytics Course

    ReplyDelete
  63. What an incredible message this is. Truly one of the best posts I have ever seen in my life. Wow, keep it up.
    AI Courses in Bangalore

    ReplyDelete
  64. Awesome article. I enjoyed reading your articles. this can be really a good scan for me. wanting forward to reading new articles. maintain the nice work!
    Data Science Courses in Bangalore

    ReplyDelete
  65. I am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
    Business Analytics Course in Bangalore

    ReplyDelete
  66. Thank you for sharing this great post its very helpful but if anyone looking for Experience Certificate Providers in Noida, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or Visit Website-Website-https://experiencecertificates.com/experience-certificate-provider-in-Noida.html

    ReplyDelete
  67. First You got a great blog .I will be interested in more similar topics. i see you got really very useful topics, i will be always checking your blog thanks.
    best data science institute in hyderabad

    ReplyDelete
  68. Thanks for posting the best information and the blog is very important.data science institutes in hyderabad

    ReplyDelete
  69. Always so interesting to visit your site.What a great info, thank you for sharing. this will help me so much in my learning
    data scientist course

    ReplyDelete