How to Send Bulk Emails with Unique Attachments from Excel (VBA Script)

Imagine you're an HR coordinator, a training manager, or a sales operations professional. You have a list of 50, 100, or even 500 people in an Excel sheet, and each person needs a unique document – perhaps a course completion certificate, a personalized offer letter, or a sales invoice. The thought of manually attaching each file to individual emails can be daunting. This guide will show you how to send bulk emails with unique attachments from Excel using a simple VBA script, saving you hours of tedious work.

The Manual Nightmare of Sending Personalized Attachments

Sending personalized documents one by one is a time-consuming and error-prone process. As a tech startup, for instance, if you need to share course completion certificates with numerous candidates, manually sending each one is inefficient. "I would not sit and send first certificate to first person, second certificate to second person," says one expert, highlighting the common frustration. This manual approach not only eats into valuable time but also increases the risk of sending the wrong attachment to the wrong person, leading to embarrassing mistakes and rework. Automating this process means you can focus on more strategic tasks rather than repetitive administrative work.

The 3-Step Setup for Automated Bulk Emailing

Automating your email distribution with unique attachments from Excel is simpler than you might think. This process involves just three key steps:

1. Prepare Your Data in Excel: Structure your spreadsheet with all the necessary information for each email and attachment.
2. Generate the VBA Macro: Use an AI tool like ChatGPT to create the custom VBA script tailored to your needs.
3. Run the Script: Execute the VBA code in Excel to send your personalized bulk emails via Outlook.

This method allows you to automate sending certificates from Excel, offer letters, or any other personalized PDF from an Excel list with ease.

Step 1: Structure Your Data in Excel (The Right Way)

The foundation of successful email automation lies in well-organized Excel data. Your sheet needs specific columns to feed the VBA script accurately. Here’s how to structure your data:

• Column A: File Name (e.g., Certificate_RohanSharma.pdf, OfferLetter_PriyaSingh.docx) - This is the exact name of the unique file you want to attach for that recipient.
• Column B: Email ID (e.g., rohan.sharma@example.com) - The recipient's email address.
• Column C: Recipient Name (e.g., Rohan Sharma) - The name of the person, used for personalizing the email body.
• Column D: Subject (e.g., Your Course Completion Certificate from Juno School) - The subject line for the email.
• Column E: File Path (e.g., C:\MyDocuments\Certificates\ or D:\HR_Documents\OfferLetters\) - This is the folder path where all your unique attachment files are stored. It's crucial that this path is correct and accessible.

An expert explains, "the file name column says which files you want to attach, the email ID to whom you want to send." Ensure your file paths are consistent and accurate. For example, if your certificates are in a folder named "Certificates" on your D: drive, the path would be D:\Certificates\. The script will then combine this path with the file name from Column A to locate each unique attachment.

Step 2: Generate the VBA Macro with ChatGPT

You don't need to be a coding expert to create the necessary VBA script. AI tools like ChatGPT can generate the code for you. Here's the exact prompt you can use:

I need a VBA script for Excel that automates sending personalized emails with unique attachments via Outlook. My Excel sheet will have columns for 'File Name' (e.g., Certificate_JohnDoe.pdf), 'Email ID' (recipient's email), 'Recipient Name', 'Subject', and 'File Path' (the folder where the attachments are located). The script should loop through each row, create an Outlook email, attach the specified file, personalize the subject and body, and address it to the correct recipient. It should also check if the attachment file exists before trying to attach it.

This prompt will provide you with a robust script. If you want to dive deeper into using AI for Excel automation, Juno School's Master Excel with ChatGPT course offers practical, hands-on training. For more general guidance on generating code with AI, you can also refer to our article on Generate VBA Code for Excel with ChatGPT.

Step 3: The Complete VBA Script (Copy & Paste)

Once you have your Excel data structured, it's time to implement the VBA script. Follow these steps:

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, right-click on your workbook name in the Project Explorer (usually on the left pane).
4. Select Insert > Module.
5. Copy and paste the following code into the newly opened module window:

Sub SendBulkEmailsWithUniqueAttachments()

    ' Declare variables
    Dim OutApp As Object
    Dim OutMail As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim emailID As String
    Dim recipientName As String
    Dim subjectLine As String
    Dim attachmentPath As String
    Dim fileName As String

    ' Set reference to the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Assuming your data is on Sheet1 - adjust if your sheet has a different name

    ' Find the last row with data in column A (or any relevant column)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Check if Outlook is running, if not, create a new instance
    On Error Resume Next
    Set OutApp = GetObject("Outlook.Application")
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0

    ' Loop through each row in the Excel sheet starting from the second row (header is in row 1)
    For i = 2 To lastRow
        ' Get data from Excel columns
        ' Column A: File Name (e.g., "Certificate_JohnDoe.pdf")
        ' Column B: Email ID (e.g., "john.doe@example.com")
        ' Column C: Recipient Name (e.g., "John Doe")
        ' Column D: Subject (e.g., "Your Course Completion Certificate")
        ' Column E: File Path (e.g., "C:\Certificates\") - this should be the folder path where attachments are stored

        fileName = ws.Cells(i, "A").Value
        emailID = ws.Cells(i, "B").Value
        recipientName = ws.Cells(i, "C").Value
        subjectLine = ws.Cells(i, "D").Value
        attachmentPath = ws.Cells(i, "E").Value & fileName ' Combine folder path and file name

        ' Create a new Outlook mail item
        Set OutMail = OutApp.CreateItem(0) ' 0 = olMailItem

        With OutMail
            .To = emailID
            .Subject = subjectLine
            .Body = "Dear " & recipientName & "," & vbCrLf & vbCrLf & _
                    "Please find your personalized document attached." & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & "Juno School Team" ' Customize your email body here

            ' Add the unique attachment
            If Dir(attachmentPath) <> "" Then ' Check if the file exists
                .Attachments.Add attachmentPath
            Else
                MsgBox "Error: Attachment file not found for " & recipientName & vbCrLf & _
                       "Expected path: " & attachmentPath, vbCritical
                ' Optional: You might want to log this error or skip sending the email for this recipient
                ' GoTo NextIteration ' If you want to skip and continue with the next email
            End If

            .Display ' Use .Display to review each email before sending
            '.Send ' Use .Send to send automatically without review (uncomment this line and comment .Display for automation)
        End With

        ' Clear the mail item object for the next iteration
        Set OutMail = Nothing

'NextIteration: ' Label for skipping if attachment not found
    Next i

    ' Clean up
    Set OutApp = Nothing
    MsgBox "Bulk email process completed. Please review displayed emails and send them.", vbInformation

End Sub

After pasting the code, you can run it directly from the VBA editor by placing your cursor anywhere within the Sub SendBulkEmailsWithUniqueAttachments() and pressing F5, or by going to Run > Run Sub/UserForm. The script is designed to excel vba send email with attachment outlook, displaying each email for your review before sending. To send automatically, uncomment .Send and comment out .Display.

Troubleshooting: Common Errors and How to Fix Them

One of the most frequent issues encountered when running this script is the "Cannot find this file" error. As an expert noted, "an error occurred, cannot find this file. Verify the file path and name are correct." This typically means:

• Incorrect File Path: Double-check Column E in your Excel sheet. Is the folder path exactly where the files are located? Even a small typo or missing backslash can cause an error.
• Incorrect File Name: Ensure the file name in Column A matches the actual file name, including the extension (e.g., .pdf, .docx). Case sensitivity might also be a factor depending on your operating system.
• File Not Present: Verify that the specific file mentioned in the error message actually exists in the specified folder.
• Permissions Issues: Less common, but ensure Excel has permission to access the folder where your attachments are stored.

Always verify your paths and file names meticulously to avoid this error and ensure smooth automation. If you are handling sensitive documents like offer letters, ensure your data is secure. Learn more about how to use AI on confidential Excel data without risking leaks.

Beyond Certificates: Other Uses for This Automation

The ability to send bulk emails with unique attachments from Excel extends far beyond just course certificates. This powerful automation can be applied to various scenarios, making it invaluable for different departments:

• Sales Operations: Send personalized sales invoices, proposals, or product brochures to clients.
• HR Departments: Distribute offer letters, appointment letters, payslips, or annual appraisal documents to employees.
• Event Management: Send personalized event tickets, registration confirmations with unique QR codes, or welcome kits to attendees.
• Education/Training: Beyond certificates, distribute personalized progress reports, study materials, or assignment feedback.
• Customer Service: Send out personalized reports, account statements, or responses to specific queries with relevant attachments.

This macro-based solution allows you to mail merge with different attachments excel, transforming repetitive tasks into efficient, automated workflows across your organization.