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

Imagine you're an HR professional preparing to send out dozens of personalized offer letters, each with a unique candidate-specific document. Or perhaps you're an event manager needing to distribute hundreds of participation certificates, each one distinct. Manually attaching individual files to separate emails is a tedious, error-prone task that consumes valuable time. Standard mail merge features in email clients often fall short when you need to send not just personalized text, but also *unique* attachments to each recipient.

Many professionals, from sales teams sending tailored proposals to small business owners distributing individual invoices, frequently ask: "How can I automate sending emails in bulk, especially when each email needs a different attachment?" The good news is that you can achieve this powerful automation right from Excel, using a VBA macro generated with the help of ChatGPT.

The Problem: Why Manual Mail Merges with Unique Attachments Fail

Traditional email automation tools or basic mail merge functions are excellent for sending generic attachments or personalized email bodies. However, they typically struggle with the scenario where every recipient requires a *different* file. For a tech startup that needs to share unique certificates with candidates, or any business sending customized documents, the manual process can become overwhelming. This is where the power of macros comes in, offering a solution to automate what would otherwise be a long, manual process.

The goal is to send bulk emails with unique attachments from Excel efficiently, ensuring each recipient receives their specific document without manual intervention.

Step 1: Setting Up Your Excel Sheet for Automation

The foundation of this automation lies in a well-structured Excel sheet. This sheet will serve as the data source for your emails, telling the macro who to send the email to, what the subject should be, and crucially, which specific file to attach. Based on common requirements, you'll need at least these four columns:

• Name: The recipient's name for personalization in the email body.
• Email: The recipient's email address.
• Subject: The subject line for the email.
• Attachment Filename: The full path to the unique file you want to attach for that specific recipient. This is critical. For example, if you're sending certificates, this column would specify exactly which certificate file belongs to which email ID.

Here’s an example of how your Excel sheet should look:

Name	Email	Subject	Attachment Filename
Priya Sharma	priya.sharma@example.com	Your Offer Letter - Juno School	C:\Offers\Priya_Sharma_Offer.pdf
Amit Singh	amit.singh@example.com	Your Offer Letter - Juno School	C:\Offers\Amit_Singh_Offer.pdf
Sneha Gupta	sneha.gupta@example.com	Your Certificate of Completion	D:\Certificates\Sneha_Gupta_Cert.pdf

Ensure that the file paths in the "Attachment Filename" column are absolutely correct and include the full directory path and file extension (e.g., `.pdf`, `.docx`).

Step 2: The Perfect ChatGPT Prompt to Generate Your VBA Code

Now comes the exciting part: letting ChatGPT do the heavy lifting of writing the VBA code. The key is to be very specific in your prompt. You want a VBA macro that will "send PDF files as attachments to multiple email IDs separately with a message saying, 'Hi [Name], please find the attached PDF file.'" Here’s a prompt you can copy and paste, along with an explanation of why each part is important:

Write a VBA macro for Excel that automates sending personalized emails with unique attachments via Outlook.

The macro should:
1. Read data from an Excel sheet named "Sheet1".
2. Assume the data starts from row 2 (row 1 contains headers).
3. The columns are:
   - Column A: Recipient Name
   - Column B: Email Address
   - Column C: Email Subject
   - Column D: Full Path to Unique Attachment File (e.g., C:\Documents\File.pdf)
4. For each row, create a new Outlook email.
5. The email should be addressed to the email address in Column B.
6. The subject should be taken from Column C.
7. The email body should be personalized, starting with "Hi [Recipient Name]," (using the name from Column A) followed by "Please find the attached document."
8. Attach the unique file specified in Column D to each email.
9. Display each email before sending (so I can review it).
10. Include error handling for missing files or email addresses.

Why this prompt works:

• "VBA macro for Excel... via Outlook": Specifies the tool and the email client.
• "Read data from... Sheet1... starts from row 2": Gives clear instructions on where to find the data.
• "Columns are: ...": Defines the exact mapping of your Excel data to the email fields. This is crucial for the script to understand your setup.
• "For each row, create a new Outlook email": Confirms the bulk, separate email requirement.
• "Email body should be personalized... 'Hi [Recipient Name],...'": Ensures the transcript's personalization request is met.
• "Attach the unique file specified in Column D": This is the core requirement for unique attachments.
• "Display each email before sending": A safety measure, especially when you're first testing. You can modify the script later to send directly if confident.
• "Include error handling": Makes the script more robust.

This detailed prompt will help ChatGPT generate a robust and accurate script. For more tips on generating code, check out our guide on how to generate VBA code for Excel with ChatGPT.

Step 3: The VBA Script Explained

After using the prompt above, ChatGPT will provide a VBA script. Here’s a typical example of what you might receive. We'll then break down the key lines.

Sub SendBulkEmailsWithUniqueAttachments()

    Dim OutlookApp As Object
    Dim MailItem As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim recipientName As String
    Dim emailAddress As String
    Dim emailSubject As String
    Dim attachmentPath As String
    Dim emailBody As String

    ' Set reference to the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Ensure your sheet name is "Sheet1"

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

    ' Check if Outlook is running, if not, create an instance
    On Error Resume Next ' Ignore errors for now
    Set OutlookApp = GetObject("Outlook.Application")
    If OutlookApp Is Nothing Then
        Set OutlookApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0 ' Resume normal error handling

    If OutlookApp Is Nothing Then
        MsgBox "Outlook application could not be started. Please ensure Outlook is installed and configured.", vbCritical
        Exit Sub
    End If

    ' Loop through each row of data, starting from row 2 (after headers)
    For i = 2 To lastRow
        recipientName = Trim(ws.Cells(i, "A").Value)
        emailAddress = Trim(ws.Cells(i, "B").Value)
        emailSubject = Trim(ws.Cells(i, "C").Value)
        attachmentPath = Trim(ws.Cells(i, "D").Value)

        ' Basic validation
        If emailAddress = "" Then
            MsgBox "Row " & i & ": Email address is missing. Skipping this row.", vbExclamation
            GoTo NextIteration
        End If

        ' Create the email body
        emailBody = "Hi " & recipientName & "," & vbCrLf & vbCrLf & _
                    "Please find the attached document." & vbCrLf & vbCrLf & _
                    "Regards," & vbCrLf & "Juno School Team"

        ' Create a new Outlook mail item
        Set MailItem = OutlookApp.CreateItem(0) ' 0 = olMailItem

        With MailItem
            .To = emailAddress
            .Subject = emailSubject
            .Body = emailBody
            
            ' Attach the unique file
            If attachmentPath <> "" Then
                If Dir(attachmentPath) <> "" Then ' Check if file exists
                    .Attachments.Add attachmentPath
                Else
                    MsgBox "Row " & i & ": Attachment file not found at " & attachmentPath & ". Email will be sent without this attachment.", vbExclamation
                End If
            End If
            
            .Display ' Display the email for review. Change to .Send to send automatically.
        End With

NextIteration:
        Set MailItem = Nothing ' Clear MailItem for the next iteration
    Next i

    MsgBox "Emails prepared. Please review and send them from Outlook.", vbInformation

    ' Clean up OutlookApp if it was created by the script
    ' If OutlookApp was created by the script, it's good practice to quit it.
    ' However, if Outlook was already running, quitting it might close the user's active Outlook session.
    ' For simplicity and user control, we'll leave Outlook running.
    Set OutlookApp = Nothing

End Sub

Key Lines Explained:

• Set ws = ThisWorkbook.Sheets("Sheet1"): This line tells the script which Excel sheet to read your data from. Make sure "Sheet1" matches your actual sheet name.
• lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row: This efficiently finds the last row containing data in Column A, so the script knows where to stop.
• Set OutlookApp = GetObject("Outlook.Application") and Set OutlookApp = CreateObject("Outlook.Application"): These lines check if Outlook is already open and, if not, open it to create emails.
• recipientName = Trim(ws.Cells(i, "A").Value): This reads the recipient's name from Column A of the current row (i). Similar lines read the email, subject, and attachment path.
• emailBody = "Hi " & recipientName & "," & vbCrLf & vbCrLf & "Please find the attached document.": This constructs the personalized email body, inserting the recipient's name. vbCrLf creates a new line.
• Set MailItem = OutlookApp.CreateItem(0): Creates a new, blank email message in Outlook.
• .To = emailAddress, .Subject = emailSubject, .Body = emailBody: These lines populate the respective fields of the email.
• If Dir(attachmentPath) <> "" Then .Attachments.Add attachmentPath: This is the core of attaching unique files. It first checks if the file specified in attachmentPath (from your Excel sheet) actually exists, then adds it to the email.
• .Display: This command opens each email for you to review before manually clicking "Send". If you are confident in your setup and want to send them automatically without review, you can change this line to .Send.

Step 4: How to Add and Run the Macro in Excel

Adding and running this VBA script in Excel is straightforward, even if you've never used macros before:

1. Open the VBA Editor: In Excel, press Alt + F11 on your keyboard. This will open the Microsoft Visual Basic for Applications window.
2. Insert a New Module: In the VBA editor, go to the menu bar, click Insert, and then select Module. A new, blank module window will appear.
3. Paste the Code: Copy the entire VBA script provided in Step 3 and paste it into the blank module window.
4. Save Your Workbook: Go back to your Excel workbook. You must save your Excel file as a "Macro-Enabled Workbook" (.xlsm). To do this, go to File > Save As, choose a location, and from the "Save as type" dropdown, select "Excel Macro-Enabled Workbook (*.xlsm)".
5. Run the Macro:
   • Go back to your Excel sheet.
   • Go to the Developer tab in the Excel ribbon. (If you don't see the Developer tab, go to File > Options > Customize Ribbon and check the "Developer" box).
   • Click on Macros (or press Alt + F8).
   • A list of macros will appear. Select SendBulkEmailsWithUniqueAttachments and click Run.

The script will then start processing your Excel sheet, creating and displaying each email with its unique attachment in Outlook.

Troubleshooting Common Errors

While this method is powerful, you might encounter a few common issues:

• "File path not found" Error: This is often due to an incorrect path in your "Attachment Filename" column. Double-check that the path is exact, including the drive letter, all folder names, and the correct file extension. For instance, if a file is in C:\MyDocuments\Offer.pdf, ensure it's not listed as C:\Documents\Offer.pdf.
• Outlook Security Prompts: When running VBA code that interacts with Outlook, you might see security warnings from Outlook, asking if you want to allow a program to access your email. You'll need to grant permission, usually by clicking "Allow" or "Yes" within a specific timeframe. These prompts are a security feature to prevent malicious scripts from sending emails without your consent.
• Blank Emails or Missing Data: Ensure your Excel sheet's column headers and data types match what the script expects (e.g., Column A for Name, Column B for Email). Also, confirm that your sheet is named "Sheet1" or update the VBA code to match your sheet's actual name.
• Macro Doesn't Run / Security Warning: Excel might block macros by default. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select "Enable all macros" (not recommended for general use, but helpful for testing your own trusted scripts) or "Disable all macros with notification" and choose to enable it when you open the file.

By carefully setting up your Excel sheet and understanding the VBA code, you can successfully automate sending bulk emails with unique attachments from Excel, saving countless hours and ensuring accuracy for your critical communications.