If you would like to send all worksheets in an Excel workbook as separate Outlook emails, you can use the method introduced in this article. It can guide you to realize it in bulk without troublesomely sending one by one.
In my previous post “2 Quick Methods to Send an Excel Worksheet as an Outlook Email”, you can learn two ways to send one Excel worksheet as email. If you wish to batch send all worksheets in one workbook as individual emails, using either of the two means mentioned in that post will be quite tedious and troublesome. Thus, here we’ll introduce another way, which can realize such “Batch Send” in a quick time.
Batch Send All Worksheets in One Excel Workbook as Separate Emails
- In the first place, you should open the source Excel workbook.
- Then, after entering the Excel window, you ought to tap on the “Alt + F11” key shortcuts.
- Next, another window called as “Microsoft Visual Basic for Applications” will display.
- In it, you have to open an unused module or insert a new module.
- Subsequently, you can copy the following VBA code into this module.
Sub SendEachWorksheet_inOutlookEmail() Dim objWorksheet As Excel.Worksheet Dim objRange As Excel.Range Dim objTempWorkbook As Excel.Workbook Dim objTempWorksheet As Excel.Worksheet Dim strTempFolder As String Dim strHTMLFile As String Dim objHTMLFile As Object Dim objFileSystem As Object Dim objTextStream As Object Dim objOutlookApp As Outlook.Application Dim objMail As Outlook.MailItem For Each objWorksheet In ActiveWorkbook.Sheets Set objRange = objWorksheet.UsedRange objRange.Copy Set objTempWorkbook = Excel.Application.Workbooks.Add(1) Set objTempWorksheet = objTempWorkbook.Sheets(1) With objTempWorksheet.Cells(1) .PasteSpecial xlPasteValues .PasteSpecial xlPasteColumnWidths .PasteSpecial xlPasteFormats End With strTempFolder = CStr(Environ("USERPROFILE")) & "\AppData\Local\Temp" strHTMLFile = strTempFolder & "\Temp" & Format(Now, "yyyymmddhhmmss") & ".htm" Set objHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address) objHTMLFile.Publish (True) Set objOutlookApp = CreateObject("Outlook.Application") Set objMail = objOutlookApp.CreateItem(olMailItem) Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objTextStream = objFileSystem.OpenTextFile(strHTMLFile) objMail.HTMLBody = objTextStream.ReadAll 'Change the email details as per your needs With objMail .To = "firstname.lastname@example.org" .Subject = objWorksheet.Name .Display End With objTextStream.Close objTempWorkbook.Close False Kill strHTMLFile Next End Sub
- After that, you could trigger this macro by pressing F5 key button or clicking the “Run” icon in the toolbar.
- At once, the emails, which are corresponding to all worksheets in this Excel workbook, will display in Outlook.
- Finally, you can re-check all the emails’ details and lastly click “Send” button to send the emails out.
Apply a Potent Recovery Tool if No Backups
More and more users have understood the importance of data backups. As long as you’ve backed up your PST file, PST recovery after damage will be quite simple. But if there is not an effective and up-to-date PST backup, you have no choice but to take aid of an external PST repair tool, like DataNumen Outlook Repair. It can repair compromised PST files like a breeze.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover sql and outlook repair software products. For more information visit www.datanumen.com