Excel VBA Introduction Part 29 – Creating Outlook Emails

Excel VBA Introduction Part 29 – Creating Outlook Emails

By Andrew Gould

http://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!
Video Rating: 4 / 5


13 Comments on Excel VBA Introduction Part 29 – Creating Outlook Emails

  1. David White // February 20, 2015 at 5:24 am //

    HI Andrew, thanks for the tutorial, I have found it extremely useful and
    your way of presenting is informative and easy to follow and learn from. I
    have a question regarding inserting hyperlinks into rich text (word
    editor). Do you know how to do this?

    In HTML email it’s extremely easy, but I can’t seem to find out how to add
    a URL bookmark into the message body of a rich text email.

    I.e. Click HERE to go to link
    (where HERE would be a hyperlink off to a URL, but still say the word
    Hope that makes sense. Thanks again!

  2. jathin dev // February 20, 2015 at 5:59 am //

    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?

  3. Lisa Pohlmeyer // February 20, 2015 at 6:15 am //

    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.

  4. Mathew Varghese // February 20, 2015 at 6:52 am //

    Dear Andrew, How can I send an outlook email automatically at a predefined
    date and time?

  5. Kirill Posazhennikov // February 20, 2015 at 7:23 am //

    Hi, Andrew. Your videos are awesome! Very helpful!
    It will be great, if you will make subtitles for all your videos. I’m live
    in Russia, but unfortunately my knowledge’s in English is not good. Can you
    do subtitles for Russian language?

  6. Tamás Jankovics // February 20, 2015 at 7:54 am //

    Hi Andrew,

    How can i make it version independent?
    Dim olApp As Outlook.Application
    Dim olEmail As Outlook.MailItem

    Set olApp = New Outlook.Application
    Set olEmail = Outlook.CreateItem(olMailItem)

    i got stuck wth the second and the 4th line…

    Thank you

  7. Need For Excel // February 20, 2015 at 8:12 am //


    Firstly, your Videos are amazing and have helped me automate my work

    Secondly, I am trying a variation of this code, but I am getting 2 types of
    errors –

    1) Run-time error ‘462’: The remote server machine does not exist or is
    2) Runtime Error 2147023170 (800706be): Automation Error The Remote
    Procedure Call Failed.

    Can you guide me regarding the same?

  8. Hi Andrew, this won’t work if I have Thunderbird?

  9. Jason Hild // February 20, 2015 at 9:44 am //

    Amazing Tutorial! Thank you so much! First time messing with VBA in
    Excel… The possibilities are endless! I’m working on a file very similar
    to this one; Excel sheet with some activeX radial options and a submit
    button. It emails out a formatted HTML email and works great until I try
    to create a table that has blank spaces in it. the For loops and .end
    functions are not picking up everything. I’ve looks around all of today
    for a solution and even watched a few more of your tutorials, and I can’t
    seem to figure it out. The Columns are not Dynamic, they have 4 Cells to
    put data in. The rows are Dynamic however in terms of how many entries are
    put in. Thanks a Million!

  10. Umamaheshwar Askula // February 20, 2015 at 10:10 am //

    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.

  11. WiseOwlTutorials // February 20, 2015 at 11:03 am //
  12. Donn Pienaar // February 20, 2015 at 11:29 am //

    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!

  13. When you add the reference to Outlook, do subsequent users of the workbook
    have to add it as well?

    Or, in other words, do references that are set in the VBA editor follow the
    user profile or do they attach themselves to the workbook?

Comments are closed.