{"id":49256,"date":"2023-02-25T19:59:59","date_gmt":"2023-02-25T18:59:59","guid":{"rendered":"https:\/\/ekiwi-blog.de\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/"},"modified":"2023-02-25T20:08:40","modified_gmt":"2023-02-25T19:08:40","slug":"vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/","title":{"rendered":"VBA macro: Automatically add a file attachment to an e-mail message"},"content":{"rendered":"<p>You can speed up the process of adding an attachment to an e-mail in Microsoft Outlook by using a suitable macro. Often you click on &#8220;<em>Insert<\/em>&#8221; via the ribbon menu. \u279c &#8220;<em>Append file<\/em>&#8221; to the respective file. Or you can first find the file in Windows Explorer and then drag it into the new mail using <em>Drag&amp;Drop<\/em>. This is already relatively practical, but can be improved with a <abbr title=\"Visual Basic for Application\">VBA<\/abbr> macro, especially if you send the same file over and over again or the file attachment is always under a certain path and corresponds to a concrete nomenclature. In an earlier article it was already shown how to automatically <a href=\"https:\/\/ekiwi-blog.de\/en\/51740\/vba-save-e-mail-attachment\/\" title=\"VBA Save e-mail attachment\">save email attachments with VBA<\/a>, now the reverse way, i.e. attaching files to a mail using VBA, will be shown here.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of content<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ekiwi-blog.de\/en\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/#VBA_code\" >VBA code<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ekiwi-blog.de\/en\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/#Explanation_of_the_code\" >Explanation of the code<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ekiwi-blog.de\/en\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/#OlAttachmentType\" >OlAttachmentType<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"VBA_code\"><\/span>VBA code<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>With the following VBA code we first create a new Outlook email and then we automatically insert an attachment into this email.<\/p>\n<pre><code><span style=\"color: #0000ff;\">Sub<\/span> NewMailWithAttachment()\r\n    <span style=\"color: #0000ff;\">Dim<\/span> appOutlook <span style=\"color: #0000ff;\">As<\/span> Outlook.Application\r\n    <span style=\"color: #0000ff;\">Dim<\/span> myEmail <span style=\"color: #0000ff;\">As<\/span> Outlook.MailItem\r\n\r\n    <span style=\"color: #0000ff;\">Set<\/span> appOutlook = <span style=\"color: #0000ff;\">New<\/span> Outlook.Application\r\n    <span style=\"color: #0000ff;\">Set<\/span> myEmail = appOutlook.CreateItem(olMailItem)\r\n    <span style=\"color: #0000ff;\">Set<\/span> myAttachments = myEmail.Attachments\r\n    myAttachments.Add \"c:\\Users\\danie\\Desktop\\Invoice-2023-01-1938.pdf\", _\r\n    olByValue, 1, \"YourInvoice\"\r\n    myEmail.Display\r\n<span style=\"color: #0000ff;\">End Sub\r\n<\/span><\/code><\/pre>\n<p>Running this code\/macro will open and display a newly created email with the appropriate file attachment, as shown in the image below.<\/p>\n<figure id=\"attachment_49251\" aria-describedby=\"caption-attachment-49251\" style=\"width: 700px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-49251 size-full\" title=\"Attach file with VBA\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2023\/02\/outlook-vba-attach-file-to-email-macro.jpg\" alt=\"Screenshot of VBA code with new email with file attachment inserted\" width=\"700\" height=\"406\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2023\/02\/outlook-vba-attach-file-to-email-macro.jpg 700w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2023\/02\/outlook-vba-attach-file-to-email-macro-300x174.jpg 300w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption id=\"caption-attachment-49251\" class=\"wp-caption-text\">VBA code creates email with attachment<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Explanation_of_the_code\"><\/span>Explanation of the code<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The expression <mark>Set appOutlook = New Outlook.Application<\/mark> simply creates an object from the Outlook application for further use. This is then taken to <a href=\"https:\/\/ekiwi-blog.de\/en\/62792\/vba-outlook-create-new-e-mail\/\">create the new email<\/a> <mark>CreateItem(olMailItem)<\/mark> with. The new email object <mark>myEmail<\/mark>, has a property\/object that manages the attachments. We assign this to our <mark>myAttachments<\/mark> object.<\/p>\n<p>The actual interesting method in this case is <mark>myAttachments.Add<\/mark>. This can be passed four parameters in the following order:<\/p>\n<ol>\n<li style=\"list-style-type: none;\">\n<ol>\n<li><u>Source<\/u> (<em>mandatory<\/em>): this is the full file path to the file to be attached to the mail<\/li>\n<li><u>Type<\/u> (<em>optional<\/em>): defines the type of file attachment. This parameter corresponds to the <strong>OlAttachmentType<\/strong> enumeration of which there are four (see below)<\/li>\n<li><u>Position<\/u> (<em>optional<\/em>): here you can define where the attachment is inserted in the e-mail. This is a numerical value. If you enter the value &#8220;1&#8221;, the attachment is positioned at the beginning of the e-mail. With any other number, the attachment is positioned at the point corresponding to the number of characters in the e-mail text. If the number is greater than the number of characters of the e-mail text, the attachment is inserted at the end.<\/li>\n<li><u>DisplayName<\/u> (<em>optional<\/em>): Here you can give the file attachment a different name. This is the file name as it then appears in the e-mail. However, this parameter does not work in all constellations. For it to work, the type <strong>olByValue<\/strong> must be used and the e-mail must be formatted in rich text format. For <a href=\"https:\/\/ekiwi-blog.de\/en\/27127\/outlook-send-e-mail-as-plain-text\/\">plain text<\/a> or HTML emails, the file name would be used as specified in Source.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<h3><span class=\"ez-toc-section\" id=\"OlAttachmentType\"><\/span>OlAttachmentType<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>As mentioned earlier, of these there are four types with the following meaning:<\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><strong>olByReference:<\/strong> You should no longer use this type. It is no longer supported by current Outlook versions<\/li>\n<li><strong>olByValue: <\/strong>With this type, the attachment is inserted into the e-mail as a copy of the original file. This should be the most suitable type for most use cases. If the original file is renamed, edited or deleted, this will not affect the inserted file<\/li>\n<li><strong>olEmbeddeditem:<\/strong> This type is used when inserting attachments in Outlook message format (*.msg files)<\/li>\n<li><strong>olOLE<\/strong>: This type concerns special objects for which Microsoft has created the OLE (<em>Object Linking and Embedding<\/em>). This allows objects\/applications to be cross-linked or embedded in other Microsoft applications. The best example of this is when you insert a Visio drawing into Word, for example. Then this is inserted in such a way that one can edit the Visio drawing within Word.<\/li>\n<\/ul>\n<\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"<p>You can speed up the process of adding an attachment to an e-mail in Microsoft Outlook by using a suitable<\/p>\n","protected":false},"author":2,"featured_media":6532,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[2714,2713,2252,1707,1703,1704,2168,1569,1700,1705,2715,1706,1558],"class_list":["post-49256","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-automate","tag-automatic","tag-code-en","tag-macro","tag-microsoft-en","tag-microsoft-office-en","tag-office-en","tag-outlook-en","tag-programming","tag-vba-en","tag-vba-code-en","tag-visual-basic-for-application-en","tag-windows-en"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/49256","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=49256"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/49256\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/6532"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=49256"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=49256"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=49256"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}