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
|
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 .
ReplyDeleteGreat Article blockchain projects for students
DeleteIEEE Projects for Engineering Students
JavaScript Training in Chennai
Networking Projects
JavaScript Training in Chennai
Please help me in counting the total no of common rows in excel vba
ReplyDeleteVery good Yogesh Sir.. Good Blog.. We also develop advance automation programs in excel at infocorpsolutions.net
ReplyDeleteHi Yogesh,
ReplyDeleteI'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
Thanks alot!!!
ReplyDeleteIt really works. Keep on going!
This result of this post has given me much priase at work! thank you!
ReplyDeleteWhat would the code be to copy all sheets into a new Workbook and email?
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.
ReplyDeleteThanks
SKS
which part of the code can I delete to use it only to send attachments?
ReplyDeletehi yougesh,
ReplyDeleteyour scripts are very very useful. it saves lots of time. hats off to you.
Hi Yougesh,
ReplyDeleteThis 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
I want to know, how to send mail from Zimbra account. Please let me know.
ReplyDeleteHow would I simply add more than one recipient's email address to send to?
ReplyDeleteHow would I simply add more than one recipient's email address to send to?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis is great and efficient. Thank you! Is there a way to modify so that it prompts for the email address?
ReplyDeleteThanks again
Hello Yogesh, You are Awesome !! I'm a Fan of yours !!
ReplyDeleteWhat changes are required in this code to send to an email listed in Cell A1 in the excel sheet?
ReplyDeleteIs 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!
ReplyDeleteIs 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!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteNice 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
ReplyDeleteShowbox 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.
ReplyDeleteBefore 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
ReplyDeleteBuat mereka yang ingin coba coba saja jadi diijinkan untuk lakukan taruhan dengan modal Rp 10rb saja
ReplyDeleteasikqq
dewaqq
sumoqq
interqq
pionpoker
bandar ceme terpercaya
hobiqq
paito warna
forum prediksi
paito warna china
ReplyDeletedata sydney update
datahk
syair sydney
syairsgp
datasgp
paito warna terlengkap
http://warungsgp.com/
live hk 6d
live sydney
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...)
ReplyDeletei never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful. visit this site
ReplyDeletethanks for posting this nice information.please keep posting.
ReplyDeletewe 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/
http://www.yogeshguptaonline.com/2012/02/excel-addins-spell-currency-excel-addin.html?showComment=1587588227343#c1226145676207704524
ReplyDeleteEarn 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
ReplyDelete9PJK1587500784 2020-04-23 01:58:08
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
ReplyDeleteGreat 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.
ReplyDeletedata science course in noida
Superb exertion to make this blog more awesome and appealing.
ReplyDeletehttps://360digitmg.com/course/data-analytics-using-python-r
enuinely refreshing scrutinizing your blog sections.Huge thankful for the significant data.
ReplyDeletehrdf claimable training
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.
ReplyDeletedata science training
Super site! I am Loving it!! Will restore again, Im taking your food in like manner, Thanks.
ReplyDeletewhat is the difference between analysis and analytics
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.
ReplyDeleteCyber Security Course in Bangalore
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!
ReplyDeleteCyber Security Training in Bangalore
Bolavita - Situs Permainan Online Aman Dan Terpercaya
ReplyDeleteTersedia 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
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