VBA & Macro Problems

How to Pass Excel Worksheet Values to Outlook Email with VBA

Chanuka Geekiyanage Sep 27, 2026 869 views

Question details

The user needs to pass worksheet values captured during a Worksheet_Change event to an Outlook email macro and correctly attach a file linked via a Google Drive hyperlink.

How to Pass Excel Worksheet Values to Outlook Email with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating Outlook email generation triggered by Excel worksheet changes and attaching cloud-hosted files.
Observed behavior: Values are not passing correctly to the email procedure, and Google Drive hyperlinks fail to attach as local files in Outlook.

Before you start

Ensure you have the Microsoft Outlook Object Library enabled in your VBA Editor (Tools > References) and that your Google Drive desktop client is actively syncing files to your local drive.

Solution 1Recommended

Pass Variables Correctly and Use Local File Paths for Attachments

Resolve the issue by passing variables as arguments or module-level declarations, and converting cloud web links to local synchronized paths.

When passing values from a Worksheet_Change event to another procedure, you must pass them as arguments to maintain their scope. Additionally, Outlook cannot attach a file directly from a web URL (like Google Drive). The file must be synced to a local directory first before it can be added using the Attachments.Add method.

Pass values as arguments

Declare your variables at the module level or pass them directly as arguments in your Worksheet_Change event (e.g., Call CreateCourseCertificates(Target.Value)).

Extract the hyperlink address

Retrieve the web link address from the specific cell containing the Google Drive link using the Hyperlink.Address property.

Convert to a local path

Ensure the Google Drive folder is synced locally on your computer. Construct the local file path string by replacing the web URL with your local Google Drive directory path.

Locate the target file

Locate the specific file in the folder (e.g., starting with 'SCAN') by utilizing the Dir function in VBA with wildcards.

Attach to Outlook

Use the Attachments.Add method in your Outlook MailItem object, providing the verified local file path string instead of the web link.

Pass Variables Correctly and Use Local File Paths for Attachments

Local Sync Required: The Google Drive application must be installed and running on your computer to ensure the linked cloud files are available locally for the VBA script to access.

Run Macros Efficiently

How to Use VBA in WPS Spreadsheets to Automate Tasks

WPS Spreadsheets offers comprehensive VBA support, allowing you to run your existing Excel macros, including those that interact with Outlook, without modifying your code. Here is how to manage macros seamlessly in WPS Office.

1. Open your macro workbook: Launch WPS Spreadsheets and open your existing macro-enabled workbook (.xlsm).
2. Access the Developer tab: Navigate to the 'Developer' tab on the top ribbon to access the macro management tools.
3. Open the VBA Editor: Click 'Visual Basic' or press ALT + F11 to open the WPS VBA Editor.
4. Run your Outlook automation: Paste or edit your Worksheet_Change and Outlook automation code into the respective modules, save the document, and trigger the macro.

Seamless execution of standard VBA code and automation scripts.Perfect compatibility with Microsoft Excel macro-enabled workbooks (.xlsm).Lightweight application that runs macros faster and consumes fewer system resources.Free built-in tools for advanced data analysis and visualization.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my Google Drive hyperlink work as an Outlook attachment?

Outlook requires a local file path to attach documents. A web-based URL from Google Drive cannot be directly read by the Attachments.Add method. You must sync the file to your hard drive and use the local path (e.g., C:\Users\...).

How do I pass values from Worksheet_Change to a standard module?

You can pass them as arguments by calling the target macro with parameters (e.g., Call MyMacro(Target.Value)), or by declaring a Public variable at the top of your standard module to temporarily store the value.

How do I find a file starting with a specific word like 'SCAN' in VBA?

You can use the built-in Dir function with wildcards. For example, FileName = Dir(FolderPath & "SCAN*.*") will search the directory and return the name of the first file beginning with 'SCAN'.

Does WPS Office support VBA for automating Outlook emails?

Yes, WPS Spreadsheets supports VBA functionality. You can use standard COM automation methods like CreateObject("Outlook.Application") within the WPS Macro editor to generate and send emails via Outlook, just as you would in Excel.

Excel & Data Problems