How to Paste a Selected Excel Range into an Outlook Email with VBA
Question details
The user needs to dynamically copy a selected range of cells in Excel and embed them into the body of a new Outlook email using VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the process of emailing specific spreadsheet data via Outlook without relying on fixed worksheet names or hardcoded cell ranges.
- Observed behavior
- The user requires a flexible macro script that captures the currently highlighted selection, properly formats it into HTML, and seamlessly inserts it into the email body.
Ensure that Microsoft Outlook is installed and configured with an active email account on your computer. Additionally, make sure you have enabled the Developer tab in Excel to access the VBA editor.
Use VBA and the RangetoHTML Function
Implement a VBA macro that dynamically captures your active selection, converts it to an HTML format using a custom function, and embeds it into a new Outlook message.
This method utilizes a combination of the Selection object and a custom RangetoHTML function. Instead of hardcoding a specific sheet name (like 'Sheet1'), it processes whatever cells you have currently highlighted, ensuring complete flexibility across your workbook.
Open your Excel workbook. Press the Alt + F11 keys on your keyboard simultaneously to launch the Microsoft Visual Basic for Applications (VBA) editor.
In the top menu bar of the VBA editor, click 'Insert' and select 'Module' from the dropdown menu. This will open a new, blank code window for your script.
Copy and paste the following script into the module window. It contains both the execution macro and the necessary HTML conversion function: Sub Mail_Selection_Range_Outlook_Body() Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing Set rng = Selection.SpecialCells(xlCellTypeVisible) If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With With TempWB.PublishObjects.Add(SourceType:=xlSourceRange, Filename:=TempFile, Sheet:=TempWB.Sheets(1).Name, Source:=TempWB.Sheets(1).UsedRange.Address, HtmlType:=xlHtmlStatic) .Publish True End With Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=") TempWB.Close SaveChanges:=False Kill TempFile End Function
Close the VBA editor to return to your Excel sheet. Use your mouse to highlight the specific range of cells you wish to email. Press Alt + F8, select 'Mail_Selection_Range_Outlook_Body' from the list of macros, and click 'Run'. An Outlook email draft will appear with your formatted selection embedded.
Process Spreadsheets Seamlessly with WPS Office
While specific COM object integrations like Outlook automation heavily rely on the Microsoft ecosystem, WPS Office provides an incredibly lightweight, free, and efficient alternative for your daily spreadsheet and data processing needs.
- 1. Download the Installer: Navigate to the official WPS Office website and click the free download button to get the installer package.
- 2. Install the Software: Run the downloaded executable file and follow the simple on-screen instructions to install the suite on your computer.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing Excel workbooks to continue editing without formatting issues.
Frequently Asked Questions
Why do I get a prompt saying 'The selection is not a range or the sheet is protected'?
This message is triggered by the script if you run the macro without highlighting any specific cells first, or if your active worksheet is password protected. You must select your target range and ensure the sheet is unprotected so VBA can copy the visible cells.
Will this VBA macro work properly on any worksheet in my file?
Yes. By utilizing the 'Selection' object rather than rigid references (like Range("A1:C10")), the macro automatically targets the highlighted cells on whatever worksheet is currently open and active on your screen.
Can I automatically assign a recipient email address in the VBA code?
Absolutely. In the provided VBA script, locate the line that says '.To = ""'. You can type your desired recipient's email address inside the quotation marks (for example, '.To = "contact@example.com"'), and it will auto-fill the 'To' field when the draft opens.
