{"id":12154,"date":"2012-08-12T15:22:00","date_gmt":"2012-08-12T15:22:00","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=2293"},"modified":"2022-08-12T11:33:09","modified_gmt":"2022-08-12T11:33:09","slug":"send-more-than-one-sheet","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/","title":{"rendered":"How to Send more than one Sheet of the workbook in mail"},"content":{"rendered":"<p>In <a href=\"https:\/\/vmlogger.com\/excel\/2012\/08\/mail-one-sheet\/\" target=\"_blank\" rel=\"noopener\">previous article<\/a> of <a href=\"https:\/\/vmlogger.com\/excel\/excel-macro-tutorial\/\" target=\"_blank\" rel=\"noopener\">Send Email Tutorial using Excel Macro<\/a>, you learnt how to send One Sheet (Active Sheet) as attachment in the email.<br \/>\nIn this Article you are going to learn how to send more than one Sheet from a workbook as an attachment in Email. The below function sends two sheets <strong>Sheet2<\/strong> and <strong>Sheet3<\/strong> of the current workbook in a mail as attachment.<br \/>\n&nbsp;<br \/>\nThis subroutine again, uses the same logic as previous one.<br \/>\n<strong>1)<\/strong> Copy multiple Sheets in a new workbook<br \/>\n<strong>2)<\/strong> Save that Workbook at a temporary folder<br \/>\n<strong>3)<\/strong> Send Email by attaching that file<br \/>\n<strong>4)<\/strong> Close and Delete the temporary workbook.<\/p>\n<p><\/p>\n<pre name=\"code\" class= \"VB\">\nSub Email_Multiple_Sheets()\n\n    'Do not forget to change the email ID\n    'before running this code\n    \n    Dim OlApp As Object\n    Dim NewMail As Object\n    Dim TempFilePath As String\n    Dim FileExt As String\n    Dim TempFileName As String\n    Dim FileFullPath As String\n    Dim FileFormat As Variant\n    Dim Wb1 As Workbook\n    Dim Wb2 As Workbook\n\n    With Application\n        .ScreenUpdating = False\n        .EnableEvents = False\n    End With\n    Set Wb1 = ThisWorkbook\n    \n    'here multiple Sheets Sheet2 and Sheet3\n    'are copied to a new workbook and\n    'that will be sent as attachment\n    \n    Wb1.Sheets(Array(\"Sheet3\", \"Sheet2\")).Copy\n    \n    Set Wb2 = ActiveWorkbook\n    \n    'Below code will get the File Extension and\n    'the file format which we want to save the copy\n    'of the workbook with the active sheet.\n    \n    With Wb2\n        If Val(Application.Version) < 12 Then\n             FileExt = \".xls\": FileFormat = -4143\n        Else\n            Select Case Wb1.FileFormat\n            Case 51: FileExt = \".xlsx\": FileFormat = 51\n            Case 52:\n                If .HasVBProject Then\n                    FileExt = \".xlsm\": FileFormat = 52\n                Else\n                    FileExt = \".xlsx\": FileFormat = 51\n                End If\n            Case 56: FileExt = \".xls\": FileFormat = 56\n            Case Else: FileExt = \".xlsb\": FileFormat = 50\n            End Select\n        End If\n    End With\n\n    'Save your workbook in your temp folder of your system\n    'below code gets the full path of the temporary folder\n    'in your system\n    \n    TempFilePath = Environ$(\"temp\") &#038; \"\\\"\n\n    'Now append a date and time stamp\n    'in your new file\n    \n    TempFileName = Wb1.Name &#038; \"-\" &#038; Format(Now, \"dd-mmm-yy h-mm-ss\")\n\n    'Complete path of the file where it is saved\n    FileFullPath = TempFilePath &#038; TempFileName &#038; FileExt\n    \n    'Now save your currect workbook at the above path\n    Wb2.SaveAs FileFullPath, FileFormat:=FileFormat\n    \n    'Now open a new mail\n    \n    Set OlApp = CreateObject(\"Outlook.Application\")\n    Set NewMail = OlApp.CreateItem(0)\n    \n    On Error Resume Next\n    With NewMail\n        .To = \"info@learnexcelmacro.com\"\n        .CC = \"info@learnexcelmacro.com\"\n        .BCC = \"info@learnexcelmacro.com\"\n        .Subject = \"Type your Subject here\"\n        .Body = \"Type the Body of your mail\"\n        .Attachments.Add FileFullPath '--- full path of the temp file where it is saved\n        .display   'or use .Display to show you the email before sending it.\n    End With\n    On Error GoTo 0\n    \n    'Since mail has been sent with the attachment\n    'Now close and delete the temp file from the\n    'temp folder\n    Wb2.Close SaveChanges:=False\n    Kill FileFullPath\n    \n    'set nothing to the objects created\n    Set NewMail = Nothing\n    Set OlApp = Nothing\n    \n    'Now set the application properties back to true\n    With Application\n        .ScreenUpdating = True\n        .EnableEvents = True\n    End With\nEnd Sub\n<\/code><\/pre>\n<p><\/p>\n<span class=\"et_bloom_bottom_trigger\"><\/span>","protected":false},"excerpt":{"rendered":"<p>In previous article of Send Email Tutorial using Excel Macro, you learnt how to send One Sheet (Active Sheet) as attachment in the email. In this Article you are going to learn how to send more than one Sheet from a workbook as an attachment in Email. The below function sends two sheets Sheet2 and [&hellip;]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[5205,1676],"tags":[],"class_list":["post-12154","post","type-post","status-publish","format-standard","hentry","category-send-email","category-excel-tips"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v23.5 (Yoast SEO v23.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Send more than one Sheet of the workbook in mail - Let&#039;s excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Send more than one Sheet of the workbook in mail\" \/>\n<meta property=\"og:description\" content=\"In previous article of Send Email Tutorial using Excel Macro, you learnt how to send One Sheet (Active Sheet) as attachment in the email. In this Article you are going to learn how to send more than one Sheet from a workbook as an attachment in Email. The below function sends two sheets Sheet2 and [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\" \/>\n<meta property=\"og:site_name\" content=\"Let&#039;s excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-08-12T15:22:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-12T11:33:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"How to Send more than one Sheet of the workbook in mail\",\"datePublished\":\"2012-08-12T15:22:00+00:00\",\"dateModified\":\"2022-08-12T11:33:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\"},\"wordCount\":114,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Email\",\"Excel Tips\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\",\"name\":\"How to Send more than one Sheet of the workbook in mail - Let&#039;s excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2012-08-12T15:22:00+00:00\",\"dateModified\":\"2022-08-12T11:33:09+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Email\",\"item\":\"https:\/\/vmlogger.com\/excel\/send-email\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How to Send more than one Sheet of the workbook in mail\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let&#039;s excel in Excel\",\"description\":\"Let&#039;s share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore &amp; currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to Send more than one Sheet of the workbook in mail - Let&#039;s excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/","og_locale":"en_US","og_type":"article","og_title":"How to Send more than one Sheet of the workbook in mail","og_description":"In previous article of Send Email Tutorial using Excel Macro, you learnt how to send One Sheet (Active Sheet) as attachment in the email. In this Article you are going to learn how to send more than one Sheet from a workbook as an attachment in Email. The below function sends two sheets Sheet2 and [&hellip;]","og_url":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/","og_site_name":"Let&#039;s excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-08-12T15:22:00+00:00","article_modified_time":"2022-08-12T11:33:09+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"How to Send more than one Sheet of the workbook in mail","datePublished":"2012-08-12T15:22:00+00:00","dateModified":"2022-08-12T11:33:09+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/"},"wordCount":114,"commentCount":2,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Email","Excel Tips"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/","url":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/","name":"How to Send more than one Sheet of the workbook in mail - Let&#039;s excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2012-08-12T15:22:00+00:00","dateModified":"2022-08-12T11:33:09+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/08\/send-more-than-one-sheet\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Email","item":"https:\/\/vmlogger.com\/excel\/send-email\/"},{"@type":"ListItem","position":3,"name":"How to Send more than one Sheet of the workbook in mail"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let&#039;s excel in Excel","description":"Let&#039;s share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore &amp; currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12154"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12154"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12154\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}