How to Batch Send All Worksheets in One Excel Workbook as Separate Outlook Emails

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

1. In the first place, you should open the source Excel workbook.
2. Then, after entering the Excel window, you ought to tap on the “Alt + F11” key shortcuts.
3. Next, another window called as “Microsoft Visual Basic for Applications” will display.
4. In it, you have to open an unused module or insert a new module.
5. 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 = "test@datanumen.com"
             .Subject = objWorksheet.Name
             .Display
        End With
 
        objTextStream.Close
        objTempWorkbook.Close False
        Kill strHTMLFile
    Next
End Sub

6. After that, you could trigger this macro by pressing F5 key button or clicking the “Run” icon in the toolbar.
7. At once, the emails, which are corresponding to all worksheets in this Excel workbook, will display in Outlook.
8. 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.

Author Introduction:

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