[VBA x Outlook] How to Automatically Send Application Forms via Email | Attach and Send Excel Files Instantly

Background

I used to manually attach Excel application forms to Outlook emails every time I created one. Since the recipient and the body of the email were almost always the same, I thought, “Can I automate this with VBA?” and decided to implement it.

In this setup, simply selecting cell L1 launches Outlook and creates an email with the application form attached.

Assumed Configuration

• There is an Excel file containing the application form (Sheet1).
• Cell L1 on Sheet1 displays “Send Email”, and selecting this cell executes the process.
• The Recipient, CC, Subject, Body, and Attachment (the current workbook) are set automatically.

Preparation (Enable Outlook Library)

Please configure the following settings in the VBA editor:

1. Open Tools -> References.
2. Check the box for “Microsoft Outlook XX.X Object Library”.
3. Click OK.
   • Note: The version number (XX.X) depends on your Office environment (e.g., 16.0).

Module Code (SendEmail)

Write the following code in a standard module (e.g., Module1).

Sub SendEmail()

    Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim currentWorkbookPath As String
    Dim ans As Integer

    Set objOutlook = New Outlook.Application
    Set objMail = objOutlook.CreateItem(olMailItem)
    currentWorkbookPath = ThisWorkbook.FullName

    ' Email sending confirmation message
    ans = MsgBox("Do you want to send the email?", vbYesNo + vbExclamation, "Confirm Email Sending")

    ' Save the workbook just in case
    ActiveWorkbook.Save

    If ans = vbYes Then
        MsgBox "Please click the send button after checking the email content."

        With objMail
            .To = "" ' Enter recipient email address
            .CC = "" ' Enter CC (Optional)
            .Subject = "Application Form"
            .BodyFormat = olFormatPlain
            .Body = "Mr. Hayashi," & vbCrLf & vbCrLf & _
                    "I have attached the application form." & vbCrLf & _
                    "Best regards," & vbCrLf & vbCrLf & _
                    "Mori"
            .Attachments.Add currentWorkbookPath
            .Display ' Display the email (if you want to review it)
            ' .Send ' Uncomment this line to send without reviewing
        End With

        Set objOutlook = Nothing

    Else
        MsgBox "Email sending was canceled."
    End If

    ' Save again
    ActiveWorkbook.Save

    ' Return focus to cell A1
    ThisWorkbook.Sheets(1).Range("A1").Select

End Sub

Trigger Settings (Activate when cell L1 on Sheet1 is selected)

Next, add the following code to the Sheet1 module in the VBA editor.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("L1")) Is Nothing Then
        Call SendEmail
    End If
End Sub

Notes on Execution

1. Email Addresses: Make sure to fill in the actual email addresses for .To and .CC.
2. Safety: If you do not want to send the email automatically, comment out .Send and use .Display so you can check the content first. This is safer.
3. Sending: If you enable .Send, .Display is not necessary.

Summary

By integrating Excel VBA and Outlook, you can automate the task of sending application forms.

• Triggers can be set flexibly, such as “Cell Selection”.
• This is very effective in reducing copy-paste work and transmission errors.

If you use Outlook in your business workflow, please try incorporating this method.