Share via

Mail merge in an Excel add-in

Browse code
A workbook with mail merge add-in open.

This sample sends emails from inside Excel using the Microsoft Graph JavaScript SDK. You'll learn how to:

  • Verify and validate data, such as email addresses.
  • Send email with Microsoft Graph.
  • Sign-in to Microsoft Graph to get proper permissions.

How to run this sample

Prerequisites

Run the add-in from the Office Add-ins Development Kit

  1. Create a new project with the sample code.

    Open the Office Add-ins Development Kit extension and view samples in the Sample gallery. Select the Create button in the top-right corner of the sample page. The new project will open in a second Visual Studio Code window. Close the original VSC window.

  2. Open the Office Add-ins Development Kit.

    Select the The Office Add-ins Development Kit icon in the activity bar of Visual Studio Code. icon in the Activity Bar to open the extension.

  3. Preview Your Office Add-in (F5).

    Select Preview Your Office Add-in(F5) to launch the add-in and debug the code. In the drop down menu, select the option Desktop (Edge Chromium).

    The 'Preview your Office Add-in' option in the Office Add-ins Development Kit's task pane.

    The extension checks that the prerequisites are met before debugging starts. The terminal will alert you to any issues with your environment. After this process, the Excel desktop application launches and opens a new workbook with the sample add-in sideloaded. The add-in automatically opens as well.

    If this is the first time that you have sideloaded an Office Add-in on your computer (or the first time in over a month), you may be prompted to delete an old certificate and/or to install a new one. Agree to both prompts. The first run requires installing dependency of this project, which might take 2~3 minutes or longer. During this time, there might be a dialog pop up at the lower right of the VSC screen. You should not interact with this dialog before the Office application launched.

  4. Stop Previewing Your Office Add-in.

    Once you are finished testing and debugging the add-in, select the The Office Add-ins Development Kit icon in the activity bar of VSCode icon and then select Stop Previewing Your Office Add-in. This closes the web server and removes the add-in from the registry and cache.

Use the sample add-in

An Excel desktop application will be auto-launched and the Mail Merge add-in will be auto-run on the right task pane area. The sideload steps has been integrated into the process, eliminating the need for manual intervention.

Please follow the steps below:

  1. If you have an application ID already, please ensure:

    In Microsoft Entra admin center under Identity > Applications > App registrations:

    • Navigate to Redirect URI, set the first drop-down to Single-page application (SPA) and its value to https://localhost:3000/consent.html.

    Otherwise, if you haven't registered a web application with the Azure Active Directory admin center, please follow the steps below:

    • Log into Microsoft Entra admin center using a personal or business Microsoft account.
    • In the navigation, select Identity > Applications > App registrations.
    • Choose New registration. On the App registrations page, configure the values as follows:
      • Set Name to Office Add-in Graph Tutorial.
      • Set Supported account types to Accounts in any organizational directory and personal Microsoft accounts.
      • Under Redirect URI, set the first drop-down to Single-page application (SPA) and set the value to https://localhost:3000/consent.html.
    • Click Register and copy the value of the Application (client) ID.

  2. In Visual Studio Code: edit the taskpane.js file and replace YOUR_APP_ID_HERE with the Application Id you got from the App Registration Portal.

  3. Create sample data, including valid email address (required) and other information.

  4. Verify template and data. the To Line must contain the column name of the email address.

  5. Send email, which will pop up a dialog to get the consent of Microsoft Graph. After sign-in, the email will be sent.

Explore sample files

These are the important files in the sample project.

| .eslintrc.json
| .gitignore
| .vscode/
|   | extensions.json
|   | launch.json               Launch and debug configurations
|   | settings.json             
|   | tasks.json                
| assets/                       Static assets, such as images
| babel.config.json
| manifest.xml                  Manifest file
| package.json                  
| README.md                     
| RUN_WITH_EXTENSION.md         
| src/                          Add-in source code
|   | taskpane/
|   |   | consent.html          Consent HTML
|   |   | consent.js            Consent JavaScript
|   |   | taskpane.css          Task pane style
|   |   | taskpane.html         Task pane entry HTML
|   |   | taskpane.js           Add API calls and logic here
| webpack.config.js             Webpack config

Feature details

./src/taskpane contains all the main page rendering, mail merge code logic, and Graph consent process.

  1. The taskpane.html file is the main page of this project. In our sample project, we use several text-area boxes and buttons to interact with the backend for data and commands.
  2. The taskpane.js file contains the main code logic of this add-in:

  • The createSampleData() function uses the Excel JavaScript API to interact with the workbook. It inserts a sample table named "InvoiceTable" and fills it with the necessary data (email addresses) and other information.

  • Your add-in can get authorization to Microsoft Graph data by obtaining an access token from the Microsoft identity platform. Use either the Authorization Code flow or the Implicit flow just as you would in other web applications, but with one exception: The Microsoft identity platform doesn't allow its sign-in page to open in an iframe. When an Office Add-in is running in Office on the web, the task pane is an iframe. This means you'll need to open the sign-in page in a dialog box using the Office dialog API. This affects how you use authentication and authorization helper libraries. For more information, see Authentication with the Office dialog API.

  • In our project, we use the DialogAPIAuthProvider class to open the sign-in page and get consent for Graph, which contains two functions: getAccessToken() and login().

    • getAccessToken() checks whether the token already exists.
    • login() constructs a URL for the Graph login dialog and uses the Office JavaScript API to display this dialog. It sets up event handlers for dialog events. If the dialog sends a message with a status of 'success', it stores the received access token and resolves the Promise with it.

  • The sendEmail() function replaces the column names in the to/subject/content text areas with the corresponding values in the table and sends emails row by row.

    The code for sending an email via Microsoft Graph is as follows:

    const sendMail = 
{
    message: {
        subject: finalSubject,
        body: {
            contentType: 'Text',
            content: finalContent
        },
        toRecipients: [{
            emailAddress: {
                address: addressValue[i][0]
            }
        }]
    }
};

await graphClient.api('me/SendMail')
    .post(sendMail);

Reference

Additional resources

Tips and Tricks

Troubleshooting

If you have problems running the sample, take the following steps.

  • Close any open instances of Excel.
  • Close the previous web server started for the sample with the Stop Previewing Your Office Add-in Office Add-ins Development Kit extension option.
  • Try to run the sample again.

If you still have problems, see troubleshoot development errors or create a GitHub issue and we'll help you.

For information on running the sample on Excel on the web, see Sideload Office Add-ins to Office on the web.

For information on debugging on older versions of Office, see Debug add-ins using developer tools in Microsoft Edge Legacy.

Make code changes

Once you understand the sample, make it your own! All the information about Office Add-ins is found in our official documentation. You can also explore more samples in the Office Add-ins Development Kit. Select View Samples to see more samples of real-world scenarios.

If you edit the manifest as part of your changes, use the Validate Manifest File option in the Office Add-ins Development Kit. This shows you any errors in the manifest syntax.

Engage with the team

Did you experience any problems with the sample? Create an issue and we'll help you out.

Want to learn more about new features and best practices for the Office platform? Join the Microsoft Office Add-ins community call.

Copyright (c) 2024 Microsoft Corporation. All rights reserved. This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.