Excel VBA Introduction Part 29.1 - Creating Outlook Emails

Подписаться 208 тыс.

Просмотров 283 тыс.

50% 1

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
By Andrew Gould
www.wiseowl.co.uk - Writing VBA code in Excel doesn't only allow you to control Excel, you also have access to all of the other Office applications. This video looks at how to create emails in Microsoft Outlook by writing code in Excel. You'll learn how to reference the Outlook Object Library, several methods for creating a new instance of the Outlook application and how to create new emails. You'll see how to set basic properties of an email, such as the address and subject and also how to add attachments and make sure that the email includes a signature. The video spends a lot of time talking about the different email formats and how to set the body text of the email according to which format you've chosen; this includes how to write plain text emails, and how to construct basic HTML emails including how to format them. Towards the end the video also shows how to write emails using the Word Editor, allowing you to copy and paste information directly from Excel.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!

Опубликовано:

25 мар 2014

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 305

@patttkaaa92 2 года назад

We should appreciate not only the videos' worth but also your charismatic and affecting tone of voice. Many thanks for the videos :)

@WiseOwlTutorials 2 года назад

Ahh thank you Patrycja!

@yp4577 Год назад

Thought you'd like to know that even now, after all these years your videos are still tremendously helpful for me. I can't count the times I had to look up something Excel VBA related and eventually ended up with WiseOwl.

@AntoniWroblewski78 10 месяцев назад

Andy, I got so many gold stars at work today after using this video to band-aid an IT issue. Thank you.

@WiseOwlTutorials 10 месяцев назад

That's great to hear, happy that it helped!

@oleksijm 4 года назад

I cannot express how grateful I am for this tutorial. Thank you so much Andy.

@totalzack5000 7 лет назад

You have a very good way of explaining things and I love your videos. THanks for doing a great job !

@chuchaqui1000 8 лет назад

YOU ARE AMAZING ANDREW!! i have watched most of your videos and you have a great skill for teaching. Thank you for sharing your knowledge.

@tylerziomkowski54 8 лет назад

I cannot even tell you how much your videos have influenced my project management. Thank you so much Andrew!!!!

@yasinwarsame3282 7 лет назад

I am so grateful for your videos! I have recently been introduced to the VBA and coding world and I am hooked. Thank you soo much!

@colorfulhandmadedreams2207 5 лет назад

Detailed explanation on each concept with great examples! Thanks for making and sharing them :) very helpful.

@letslearnexcel 4 года назад

Really amazing and very professional. The way you are explaining it is really simple to understand and not complecting like other videos.

@davidcarpenter4881 5 лет назад

Quite possibly the best VBA videos I have watched. I subscribed to the channel quickly after watching. Thank you for providing such detailed explanations.

@abhishekpandey190 8 лет назад

Sir, your VBA series videos are very good. My vba skills and knowledge grown exponentially after watching your videos. Keep doing such a great work.

@David-iq1kd 4 года назад

Seriously amazing tutorial, thank you so much. For those needing a trick for quickly finding file paths to your attachment is to hold the shift button while right clicking on your file, then choosing "copy as path." Sometimes navigation to the desktop through the windows explorer to get the path can be hard on networked work computers and this simplifies it.

@WiseOwlTutorials 4 года назад

That's a great tip, thanks David!

@maurocastagnera8949 7 лет назад

J'm sorry don't understand all you say in your videos, because J don't speak English very well, but J really think your tutorials are the best people can found on internet about VBA. Thank you, thank you, thank you!

@andrewnolan180 3 года назад

Incredible lesson. I would have preferred to have it broken into 3 or 4 separate lessons. It took me several hours to get through it but I now understand how to generate emails from Excel with VBA better than I would have with any other teacher.

@WiseOwlTutorials 3 года назад

Thanks Andrew! Agreed about the length: it turned out to be a lot longer than originally planned. The other videos in the playlist on using Outlook with Excel VBA are a little more manageable I think!

@enrikekasijas705 3 года назад

I can't believe I am 7 years behind. This would really simplify my life back in the days. Need to roll up my sleeves finally. I watched almost all of your VBA tutorials last year and created some epic macros but I already forgot all tricks I used there. On the one hand when you guide us through the video it is so cool , logic but then when I face the issue myself I keep returning to each video of yours xDDDD to figure out the solution. I need second life to Reach those SQl videos from last weeks. I caught a glimpse of them and they opened my eyes and inspired and show possibilies but it takes a lot of effort to master those VBA skills. 7 years ago you were making such transcending videos. I just can't believe that. Best channel on RU-vid.

@WiseOwlTutorials 3 года назад

I know the feeling Enrike! It's easy to forget if you're not using a skill regularly and there never seems to be enough time to practise everything! But then, if we already knew everything, what would we do then?!

@enrikekasijas705 3 года назад

@@WiseOwlTutorials I guess we would do WiseOwlTutorials then;D really you are doing God's work. It's just overwhelmingly useful knowledge there.;)

@WiseOwlTutorials 3 года назад

@@enrikekasijas705 😀 thank you so much!

@bhar2855 Год назад

Thank you very much for this. I had coding like this I obtained from someone else over a year ago but it wasn't attaching signatures to any of my files. I had to have additional coding that would do that saving my signature as an image and then attaching that to an email only for the files where only I was the one emailing. It didn't help the shared ones that others would also email. I've only recently started learning code so I don't yet have the understanding that I want, but with this, I was able to tweak my existing code and now EVERY file I use it for is attaching signatures no matter who is sending the email. Thank you again!

@WiseOwlTutorials Год назад

Happy to hear that the video helped! Thanks for watching and taking the time to write a nice comment!

@AGASGASDGEWQ2646 8 лет назад

Andrew, I really know my stuff because of this series! I ran into a DLL error and knew approximately where to go. Thank you for the early vs late binding info.

@Shaan0112 9 лет назад

this video is really awesome.... I get all my answer in this video. presentation is too good. looking fwd for more videos.... thanks for superb work. Grt work done

@rahulapte2433 7 лет назад

You are awesome, great programming and teaching skills... Thanks a ton!!

@info2satish 7 лет назад

really brilliantly explained all the aspects of writting code to access outlook...great!!

@cooeee1234 7 лет назад

Thanks so much! your vba tutorials are BEYOND AWSOME!!!

@cmrobinsDJ 7 лет назад

This tutorial helped me SO very much. Thank you!

@gunjanautomation 4 года назад

I am watching all the videos and i can say its really ultimate also the way you are teaching anyone can understand and learn easily .....hats off to you....... :)

@kamalkumara5338 7 лет назад

Thank you so much for your reply. I got the point, also this tutorials are very good and we can learn everything. Thank you so much again

@techsalesforce 3 года назад

Thank you so much for this video! Helped me out so much!

@WiseOwlTutorials 3 года назад

Happy to hear it Cassio, thanks for watching!

@annamuszynska6106 5 лет назад

What a great video! You helped a lot :) Thank you so much!

@Raidon88 8 лет назад

Thank you very much for this tutorial. It's extremely informative and a useful tool. Keep up the good work!

@Abhishekyadav-mo6py 4 года назад

Wickus Koster how do I copy single cell value from excel sheet and paste it to outlook I mean how do I manipulate your getmovie function to get single cell value from excel and paste it to outlook using hrmlbody

@kumar-manoj 3 года назад

Very well demonstrated, most needed for me. Thank you....

@WiseOwlTutorials 3 года назад

Happy that you found it useful Manoj!

@TravisDrLee 2 года назад

Your videos are the best and very well explained. I'm learning so much about all the different libraries and enums and more. Thank you very much for this video.

@WiseOwlTutorials 2 года назад

Thanks Larry! Pleased to hear that you're enjoying the videos! Thanks for taking the time to leave a comment and for watching!

@NaimaEchalhi 3 года назад

As always, fun & learn ! Thank you, Andrew !

@WiseOwlTutorials 3 года назад

You're very welcome Naima, glad you enjoyed it!

@FanMaoyi 2 года назад

What a great tutorial video! Organized and clear!

@WiseOwlTutorials 2 года назад

Thanks for watching!

@OutForABlast 9 лет назад

Super informative. Excellent video thank you!

@yashchawla8360 2 года назад

Wow, I always used dim as new in my all applications. I learned something new and I will always use set a new after that. You are really amazing!!!

@WiseOwlTutorials 2 года назад

Thanks!

@mjjmagicc 5 лет назад

Thank you, you made it very easy to understand and helpful

@user-xp9kn6uk5k 3 года назад

Amazing video! thank you so much! You explain complicated things in such an easy way, great job! Oh and that amazing British pronunciation!!!!

@WiseOwlTutorials 3 года назад

You're very welcome! Happy to hear that you enjoyed the video (and the accent!), thanks for watching and taking the time to leave a comment!

@lpohlmeyer 10 лет назад

Great tutorial! I knew absolutely nothing about VBA prior to watching this. You're very thorough. I'd like to also know how to attach the current Excel sheet to the email. I want to attach this macro to a button defined in the sheet. Or to create a PDF of the sheet and attach it to the email. Both of which I believe are macros in Excel that are shown on the File tab. I'm using 2010.

@UmamaheshwarAskula 10 лет назад

Hi Andrew, Your tutorial is awesome!. I have learned a lot & implemented in my daily work. It works fantastically. Thank you once again. Looking more classes on VBA.

@Abhishekyadav-mo6py 4 года назад

Umamaheshwar Askula how do I copy single cell value from excel sheet and paste it to outlook I mean how do I manipulate your getmovie function to get single cell value from excel and paste it to outlook using hrmlbody

@divyal5852 8 лет назад

How Did I miss this one?! Amazing info..thank you Andrew! :)

@Kickassng 8 лет назад

Actually, Never mind. I watched one of your other videos and figured it out. YOU ARE A GENIUS!!! Thanks for making me look good. ;)

@Kickassng 8 лет назад

Very. Love your videos

@InteligenciadeNegocios 4 года назад

excellent tutorial. 2020 and the code is still working fine . Thank you

@leeblack2103 7 лет назад

Kudos WiseOwl......your video was very helpful! Cheers bro

@guillaumepaquet1551 Год назад

Awesome explanations! THANK you!

@WiseOwlTutorials Год назад

Thank you for watching!

@wadduz 5 лет назад

Great Tutorial! exactly what I needed!

@WiseOwlTutorials 5 лет назад

Happy to help, Artur! Thanks for watching!

@marcinkrawczyk4437 8 лет назад

Goooood job Andrew ! Thank you

@jalalkasmani2575 9 лет назад

Very informative. Thanks!

@AmjadMuhammad13 6 лет назад

absolutely brilliant, I couldn't thank you enough!

@prasanthk.p6751 6 лет назад

Wise Owl you are the best

@josemariaabecassis5324 6 лет назад

Very useful, thank you!

@AC-cv5zz 3 года назад

I'm pretty sure that I "liked" every video in this VBA - Outlook sub-series as my job deals a lot with Outlook. Based on what you've taught here (and the typical hopping around on the net), I've created some nice email sorters (one using a rather lengthy keyword list on an excel sheet) as well as some email counters that report various data to my colleagues. My only wish is that you would add a few more videos to this Outlook playlist as it seems like there is so much more that can be done in Outlooks using VBA than most people realize. Another series that I'd be interested in is VBA for SAP GUI automation. Though some SAP VBA automation videos already exist, your teaching style, flow, and knowledge of VBA would help tons of people in finance careers that use SAP accounting software. Anyways... thanks again for your videos and the work that you put into them.

@WiseOwlTutorials 3 года назад

Hi AC, sounds like you've made some cool solutions, great stuff! I think at this point Outlook deserves its own playlist and tutorial series rather than squishing more into the Excel tutorial but I'm not sure when that's likely to happen. If you have some specific ideas for topics please do feel free to share them here. On the subject of SAP, sadly we don't use it here at Wise Owl so it's pretty unlikely we'll create a tutorial for this product I'm afraid. Thanks for the suggestions though, and for taking the time to leave a comment, it's much appreciated!

@Smartieboks 10 лет назад

Hi Andrew, your tutorials are fantastic! I've watched almost everything on SQL and VBA. Just 2 months ago my SQL knowledge was absolute zero and my VBA was shaky at best. Would love to see one on how to run SQL query from VBA. As a newbie I find the ADODB object difficult to understand. Thanks and keep up the good work!

@dsdlonyer 5 лет назад

Very informative, and help me to create a nice little excel procedure for sending some pre-defined emails.

@WiseOwlTutorials 5 лет назад

Happy to hear that you found it useful! Thanks for watching and for commenting!

@dsdlonyer 5 лет назад

@@WiseOwlTutorialsan I ask a question though, I have Microsoft Office Professional Plus 2016 at home and work. It works fine at home, but at work, I get an error of Run-time error ‘287’ Application-defined or object-defined error. It doesn't like the .HTMLBody at the end of .HTMLBody = Email & GetDataHTML & Emailpt2 & .HTMLBody. Only difference between home and work is work we have exchange server. Is this something you are aware of? If I remove the end .HTMLBody, it reads in the data but doesn't add my signature, if I comment out the line, it creates the emails with signature, but obviously no data is read in from excel.

@ahmedali2512 7 лет назад

NIce Video. Thank You!

@vinhthai93 8 лет назад

This is great. Thank you.

@vinhthai93 8 лет назад

+WiseOwlTutorials More like thanks for sharing your wealth of knowledge. The training within your tutorials has helped me not only with my development but also with my everyday work. Mixing and stringing multi VBA coding into something I wouldn't have thought about doing on the past.

@fengfuxu4822 2 года назад

great video. thanks

@WiseOwlTutorials 2 года назад

You're welcome, thanks for watching!

@accountsoffice2943 Год назад

Thank you very much. very helpful

@WiseOwlTutorials Год назад

You're very welcome, thanks for watching!

@excelbear6860 5 лет назад

this is absolutely brilliant. i've been searching for excel vba to outlook resources but can only find plain codes but no books or courses. wish there are more to come! what would you recommend if the email body has many components such as different font type, size, bullet points, etc? Is there an easy way to get the html code and place in the vba code?

@yutn512 2 года назад

Thank you so very much!

@WiseOwlTutorials 2 года назад

You're very welcome, thank you for watching!

@joskabouw 9 лет назад

THX very much !!

@dereklowry3111 8 лет назад

excellent video. Thank you

@dereklowry3111 8 лет назад

quick question... I followed your video and successfully set up a report email from excel... works like a dream... I've been searching for how to assign a category to these reports. so I can search or filter by these in the future

@dereklowry3111 8 лет назад

thanks

@cxvoiqw9917 9 лет назад

Hi, These videos are amazing! I just download every VBA tutorials. I was hoping you have something about JAVA by the time I finish all of this. I would download everything if have plans of doing it.

@ryanjefferis7598 6 лет назад

This was a great tutorial and I learned a lot but I was wondering is there a way to add multiple attachments based on a list in your spreadsheet? The file path is stored in column R7 and can be 20 rows (R7:R27). Any help would be greatly appreciated!!

@alisonwaltrick2002 7 лет назад

Hi !! That's an amazing tutorial. What if I want to attach a picture into email body ? How could I do it ? Also what if I want to set up the picture with hiperlink ? Thank you so much.

@TravelTrainee 4 года назад

Hi Andrew, How can i format the HTML table in the codes that you have shown. To be precise I want the header (top row) to be bold with light blue fill and along with that I also want the table to have a outline border. Thank you in advance.

@tuyoexcelypowerbi 7 лет назад

Really good! same as all videos that you have created. Can we also bring images included in the different signatures? What about if we do not want to show body´s e-mails avoiding the .display in case that we want to send 50 or 100? Can we create or bring signatures with images? Many thanks

@franciscolara9462 3 года назад

Hello every one, I am getting error 4608 "value out of range" when trying to paste an excel table into the email using the Word editor (1:16:50) The thing is it works for me but it does not work for a colleague, both have the same office version and we both have the Word library checked on references. Any idea??

@TheBanjaara 5 лет назад

Hi Andy, I found your videos quite helpful. Thank you for all the efforts you put in to help people by sharing your knowledge. Could you please help me on how to send email from a particular email account, if I have multiple accounts mapped in Outlook? Thanks in advance! God bless! ❤️😊

@erinbaxman1723 8 лет назад

hi, after creating a table in the body of the e-mail and everything is said and done there is a drastic lag and "thinking time" for the computer (37 seconds to be exact) is there anything that can be done to speed up this process. I have created this worksheet and copied it 31 times (each day of the month) and after doing that it started to lag? any information would be appreciated... thank you

@ericdunham8835 8 лет назад

Fantastic video! Tons of helpful info. Having a challenge with formatting the emails generated. Everything looks fine in an Outlook client but a gmail client doesn’t recognize table formatting. Your Outlook Sub’s utilize .Display to append the default signature. When I replace .Display with .Send I lose my default signature as expected but I also lose some formatting of a table that resides in my .HTMLBody. I am declaring .BodyFormat = 2. Example: .HTMLBody = “” & myStr & myStr2 & MyTbl & .HTMLBody & “”. The only HTML tags I have in my strings are ’s. Any quick thoughts on what tags might be missing to keep the table formatting? The table is being generated using Excels publish as a web page wizard.

@priscillaschmidt4849 7 лет назад

Thank you for sharing your knowledge with us, I have learned so much by watching your videos. Question, if email is displayed but I decide not to sent it but close it, is there a condition I can use to let the user know with an msgbox that email was not sent but closed or cancel? Thank you.

@paulvarley6872 9 лет назад

Hi Andrew, this is a great and informative video and just about covers all I need. The one bit I need more help on is more table formatting. I have been brushing up on HTML code but its where to insert it correctly in the VBA code that has me stumped.

@dawidmisiak850 7 лет назад

Hi Andrew, thank you for this tutorial. It really helped me to create half of what I need. I have though a question. I created a macro that firstly creates a file based on data put in the User Form and then send it via Outlook emails. I wonder if this is possible to add two buttons in the Email HTML text and assign different macros to those Buttons? Do you have a solution about that? Be very grateful for any tips how to create this.

@shaikayesha3156 7 лет назад

sir! thank yu so much.

@devangjoshi6290 6 лет назад

I am receiving an error which says "The requested operation requires elevation" when I am trying to create an outlook object either by early binding or late binding method with below statements "Set olApp = New Outlook.Application" OR "Set olApp = CreateObject("Outlook.Application")" It only works when I open this Excel file with "Run as Admin" mode and run my macro. Can you help me to resolve this issue? I do not want to run my Excel with "Run as Admin..." mode to execute my code. I am using MS-Office professional 2013.

@tannertucker22 5 лет назад

This is by far the most complete. Thank you. Do you know how to hard code the sent from email account from an account other than the default email account? Thank you.

@rameshsatkurilinksolutions130 6 лет назад

Am very happy sir am improved my skill

@johnwatkins39 7 лет назад

with this vba code can you set up to pull data from an excel worksheet for the "to:", "subject:", "from:", etc.

@bondniko 3 года назад

Hello Andrew, what if I have one account and two mail addresses in it. The second one is a shared account. I am able to send emails from that one programmatically, but the sent items appear in my primary Sent folder. Could you elaborate on that, too?

@martymcfly00333 7 лет назад

Is it necessary when reverting to the Late Binding to remove the OL reference? If not I can leave the reference alone to make it easier to code in future emails.

@mehbubalam5438 7 лет назад

Hi, Thank you for the amazing videos. Could you please help on how to use"olmobilesms" object in excel vba while connecting to outlook?

@luluprieto2317 4 года назад

Hello, Thank you for all your explanations. I really appreciate your step by step. I'm pulling some information from excel and sending it by email. However, I want to remove my signature. How can I remove the signature from the email without affecting the information I pasted from excel?

@syedmuhammadwasihasan1335 6 лет назад

Is this possible to send specific email for specific subject? For example if some one sent me mail and have subject "SHANGHAILCL" then "SHANGHAILCL TEMPLATE" automatically sent to the person in reply. If yes can you please help me how to do it?

@somanathalegaon9574 9 лет назад

Hi Andrew, Thank you for the wonderful tutorial, Its very helpful. I wanted to know do you have any tutorial on filters and selecting the data and copy to another sheet. Any guidance will be appreciated..!! :)

@Dr-B172 7 лет назад

Hi, I want the table to have borders in each cell. How can I do? (in html) thanks!

@samriddhtuteja7371 7 лет назад

Great Tutorial !!! Is it possible to have predefined body messages , and we get to choose which body need to be used in emails before sending each of them ? ?

@thres34 6 лет назад

Hi, Is there a codeto personalise the attachment? I really would like to hear from new in the near future. big thank you in advance.

@anshumandixit7336 6 лет назад

All works fine but not very sure why my signature does not appear when it display new email. Any suggestion?

@shantanu654654645 6 лет назад

I have created the excel sheet to send outlook meeting invites and getting Complie Error in Hidden Module: Sheet1. This excel sheet on sharepoint for mutiple users to access. Some of the users are using office 2010 and some are using office 2013. For office 2010, user is getting above error but office 2013, users are able to sent outlook meeting invite through this code. After my finding i got to know there is references issue for Microsoft Outlook 14.0 object library and Microsoft Outlook 15.0 object library. Whenever office 2010 user opens the excel sheet the references Microsoft Outlook 15.0 object library reflect as Missing and it gives the above error. Can you please provide any workaround on this. Need a code which can add the Microsoft Outlook 15.0 or 14.0 object library references as per the versions through vba code or any code with which automatically selects the required outlook references according the office versions (Office 2010 or 2013). Awaiting for your response. Thanks in Advance !!

@zyvsoo311 5 лет назад

Thanks for this video. I want to know how to send the Excel data with use range of any person's name or code or drop down via VBA paste in outlook mail body with the common mail draft.. If possible please help me friend.

@darrylmoore3772 6 лет назад

Hello, this was a great tutorial. do you have a tutorial that would show how to add items from a listbox to the .body of the email? I can't seem to figure out a way to do this.

@mohammedalghamdi2459 4 года назад

thank you for your prof explanation , i want to replay to an email with body. how it can be done ?

@fadge316 8 лет назад

This is really helpful, especially for a beginner! I do have one question though, say I wanted to send different emails to different email addresses based on certain criteria being met...how is that done? So almost like the subroutine will check a table row by row, if a row contains say "send reminder" then it will use the email address listed in that row using data in a cell as the body, then continue the subroutine until all entries in the table have been completed. Thanks!

@ethansubbu1768 4 года назад

Hi Is there any options to call vba code for one outlook application to another out look application

@jathinsarang 9 лет назад

Hi Andrew, I want to know how to get senders name, subject and date sent of all mails in inbox or any folder to an excel sheet. Can you help me with that?

@DuApril 10 лет назад

Hi Andrew, this won't work if I have Thunderbird?

@thewezzley 5 лет назад

Anyone know why you need .Display in order to send a selected range? The code in which he cut and paste a range into an email--if you run that with .Display commented out, the body will be empty.

@sriramiyer3558 6 лет назад

Good video. is it possible to add a picture to the outlook email body through a VBA macro?

@nestle1988 8 лет назад

HI Andrew, how do we email a specific sheet in a workbook?

@jathinsarang 9 лет назад

Hi Andrew, This video was awesome! I want to know how to change the font size and font style if i am using Rich text format with word editor. Can you help me out in this?

@NidishVashistha 8 лет назад

Dear Mr. Gould The video is fantastic. I learned a lot. Thanks for your efforts. Can you please answer my question, If i have to modify my macro to send Particular Rows from Excel to Outlook E-mail, then In what case, What should I do. I'm stuck. Pls help.