Get the Workbook + Office Scripts Code
Grab the ready-to-use Excel file from the video. Sample reports to test with and both script versions (range-based and table-based) included.
What You’ll Build
By the end of this tutorial, you’ll have a setup where:
- An index sheet lists each manager’s name, email address, and the sheet(s) they should receive
- One click converts the right sheets to PDF and emails them to the right person
- Each manager only sees their own data
- It works the same way in Excel desktop and Excel Online
What Are Office Scripts?
Office Scripts is Microsoft’s modern replacement for VBA in Excel. Think of it as VBA’s younger sibling. It uses TypeScript instead of Visual Basic, and the big advantage is that it works in Excel Online, not just on desktop.
New to Office Scripts? Start with Office Scripts in Excel: Automate Tasks Without VBA for a beginner walkthrough of recording your first script. If you’re deciding between VBA and Office Scripts for your project, the VBA vs Office Scripts comparison covers the differences.
To access it, go to the Automate tab in Excel, click New Script, then Create in Code Editor.
You’ll see a script editor panel open on the right side of your screen.
The Basic Office Scripts PDF Email Script
Microsoft provides a starter script in the official Office Scripts sample on Microsoft Learn that converts an Excel file to PDF and sends it by email. Here’s how the core logic works:
OfficeScript.convertToPdf()converts the workbook to a PDF object in memory- You define a file name for the PDF
OfficeScript.sendMail()sends it to a recipient with a subject line and message
After pasting the base code into the editor, you customize the recipient email, subject line, and message body. Then hit Run.
That’s it. A few lines of code, and the PDF lands in the recipient’s inbox.
Why convertToPdf() Sends the Whole Workbook
The basic script converts the entire workbook to a PDF. Every sheet. Including the index sheet and any internal sheets you don’t want people to see.
Unfortunately, the convertToPdf() method doesn’t currently accept arguments to specify which sheets to include. It takes the whole workbook.
How to Email Only Specific Sheets as PDF
Here’s the trick: hidden sheets are excluded from the PDF conversion.
So the workflow becomes:
- Hide every sheet the recipient should NOT see
- Convert to PDF
- Unhide everything again
If you do this manually for one person, it works. But obviously you don’t want to sit there hiding and unhiding sheets for every manager.
How to Loop Through an Index Table to Send Different Sheets to Different People
This is where it gets practical. Create an index sheet with a table that contains three columns:
- Name
- Sheets
Name the table something like SendList.
The script will loop through each row, read the manager’s name and email, figure out which sheets to show, hide everything else, convert to PDF, send the email, and then unhide all sheets again.
If a manager is responsible for multiple countries (like Prakash with Canada and Germany), separate the sheet names with a comma.
The script splits them and includes both in a single PDF.
How Does the Full Script Work?
Here’s a plain-language breakdown of what the complete script does:
- Reads the index table to get each manager’s name, email, and assigned sheets
- Makes the target sheets visible first (to avoid errors if they were already hidden)
- Hides every other sheet so only the target sheets remain visible
- Converts to PDF using
convertToPdf() - Sends the email with the PDF attached and a personalized message
- Unhides all sheets so the workbook returns to its original state
- Repeats for the next row in the table
The script also includes error handling. If someone types a sheet name that doesn’t exist, that row gets skipped instead of crashing the whole script. Same if the email field is blank.
Range Version vs. Table Version: Which Should You Use?
There are two versions of the script:
Range version: References a fixed cell range (e.g., B5:D6) for the manager data. Simpler, but you need to update the range manually if you add more rows.
Table version: References a named Excel table (like SendList). More flexible because the table expands automatically when you add rows. This is the recommended version.
If you use the table version, just make sure the table name in your script matches the actual table name in Excel. You can check this under Table Design on the ribbon.
How to Add a One-Click Button to Run the Script
You don’t have to run the script from the code editor every time. You can attach it to a button right on your worksheet.
In the Office Scripts code editor, click the three-dot menu (More options) and select Add in workbook. This places a button on your sheet that anyone can click to trigger the script.
This works on desktop and in Excel Online.
Testing the Workflow End-to-End
Once everything is set up, click the button. You’ll see activity in the background as the script runs. Within seconds, each manager receives their personalized email with only their country data attached as a PDF.
Here’s what the recipient sees:
- A personalized greeting (e.g., “Hi Tom, please find attached your report.”)
- A PDF attachment containing only the sheets assigned to them
- No access to other managers’ data
Office Scripts PDF Email: Key Things to Know
Office Scripts require Microsoft 365. You need a business or enterprise license. The feature isn’t available on personal/home plans.
It works in both desktop and online Excel. This is the main advantage over VBA, which only works on desktop.
The convertToPdf() method converts visible sheets only. That’s why the hide/unhide trick works.
You don’t need Power Automate. The sendMail() method is built into Office Scripts. No extra tools or connectors needed.
Give your script a clear name. Don’t leave it as “Script 11.” Rename it to something descriptive like “Convert to PDF and Email.” It will appear in your Office Scripts gallery under the Automate tab.
Office Scripts vs Power Automate vs VBA: Which Should You Use?
All three can convert Excel sheets to PDF and email them. The right choice depends on where the workbook lives and what’s installed.
Use Office Scripts when: the workbook is in OneDrive or SharePoint, users are on Microsoft 365 business or enterprise, and you want a one-click button that works on desktop AND the web. This is the simplest setup.
Use Power Automate when: you need the script to run on a schedule, trigger from an email, or pass data to other apps like Teams or SharePoint lists. Power Automate calls the Office Script, you don’t replace it. See the full walkthrough in How to Use Office Scripts with Power Automate.
Use VBA when: the workbook lives on a local drive, you’re on Excel desktop only, and you need fine control over Outlook (custom signatures, embedded images, draft mode). VBA does not work in Excel for the web.
Frequently Asked Questions
Do Office Scripts work in Excel desktop or only Excel Online?
Both. Office Scripts run in Excel for Windows, Mac, and the web. The convertToPdf() and sendMail() methods work across all three platforms. This is the main advantage over VBA, which only runs on Excel desktop.
What license do I need to use Office Scripts?
Any Microsoft 365 business or enterprise plan (Business Basic, Business Standard, Business Premium, E1, E3, E5, or F3). Office Scripts is also in preview for personal and family Microsoft 365 subscriptions through the Microsoft 365 Insider program. The workbook must be saved to OneDrive or SharePoint.
Can I send specific sheets as PDF instead of the whole workbook?
Yes, but indirectly. The convertToPdf() method converts every visible sheet in the workbook. To send only specific sheets, hide the sheets you want to exclude before calling the method, then unhide them afterward. The script in this tutorial automates that hide and unhide cycle for each recipient.
Do I need Power Automate to send emails from Office Scripts?
No. OfficeScript.sendMail() is built directly into Office Scripts since the January 2026 update. It sends an email from the running user’s Outlook account with no connectors or external services required. Use Power Automate only if you need to trigger the script on a schedule or from another app.
Why doesn’t the Automate tab appear in my Excel?
Three common causes. First, you may not have a supported Microsoft 365 license (check the previous answer). Second, your admin may have disabled Office Scripts in the Microsoft 365 admin center. Third, third-party cookies must be enabled in your browser for Excel on the web to show the Automate tab.
Can Office Scripts send an email with the PDF as a link instead of an attachment?
Not directly with sendMail(). To send a link, save the PDF to OneDrive or SharePoint first using a Power Automate flow, then include the share link in the email body. This is useful for large PDFs that exceed the 25 MB attachment limit.
Download the Workbook
Index table, both script versions (range + table), and the one-click button ready to go.
Get the workbook + codeTake It Further
Want more no-code automation recipes? The Automate Excel: Power Query & Power Automate Recipes course includes hands-on lessons on Office Scripts, scheduled PDF exports, and conditional email automations that you can apply this week.
Featured Course
Automate Excel: Power Query & Power Automate Recipes
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
