Bulk Excel VBA Send Email with Attachments – 2 Multiple recipients

Excel VBA Send Outlook Email with Attachment

Using VBA code, it is easy to integrate most of the MS Office application from another counterpart. Similarly, we can use Excel VBA send email with attachment using Outlook app.

From with in the code, you can set all the email fields like To, Cc, Bcc, Subject, Email Content etc., We can do all this from Outlook itself.

Then why use Excel?

Sending Outlook Emails from Excel

Sending emails through Microsoft Outlook is a crucial task for many businesses and organizations. However, manually opening Outlook and composing emails can be time-consuming and repetitive. Fortunately, Visual Basic for Applications (VBA) offers a solution to automate this process. With Excel VBA, you can create a script that will send emails through Outlook, saving you time and effort.

It is because, many computer professionals use email address list in a Excel sheet. This is mainly used for one of this purpose:

1. Send Organization Announcements
2. Broadcast Marketing or Promotional offers
3. Festival Wishes
4. Regular follow-ups
5. Invoice or billing
6. Regular sales, performance, forecast, stock market reports to predefined group of people

In all these cases, people find it easy to maintain the emailing list in a Excel rather than in Outlook contact distribution list. One of the key advantages of using VBA to send Outlook emails is that it allows you to take advantage of a running instance of Outlook, rather than creating a new one. This not only saves resources but also makes the process faster.

So, Lets see how to do this in Excel VBA Macro.

Excel VBA to Send Outlook Email to multiple recipients

The Excel macro code creates a Outlook object or instance. Then uses this to create a new email item. Then fills in the To address, Subject test, Mail content etc.,

Once all the required fields are filled in, it sends the email. Then this newly created email goes into Outbox where it will be delivered once the Outlook “Send/Receive” frequency refresh kicks in.

Copy & paste this code in out VBA project.

Sub Send_Outlook_Email_Using_VBA()
    ' Define variable fields & objects used
    Dim OutlookApp As Object 'Outlook.Application
    Dim OutlookMail As Object 'Outlook.MailItem

    ' Check if Outloook is already running
    On Error Resume Next
    Set OutlookApp = GetObject(, "Outlook.Application")
    If OutlookApp Is Nothing Then
        ' If Outlook is not running, then create an instance
        Set OutlookApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0

    ' Create a new Email Item
    Set OutlookMail = OutlookApp.CreateItem(0) 'olMailItem=0 - Check external reference enumurations
    
    ' Set values for the new email item
    With OutlookMail
        .To = "recipient@email.com"
        .CC = "cc@email.com"
        .BCC = "bcc@email.com"
        .Subject = "Email Subject"
        .Body = "Email Body"
        .Attachments.Add "C:\file.txt" 'Add attachments
        .Send
        
        ' Use .Display instead of .Send to do testing
        '.Display
    End With
    
    ' Release the objects
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

This code uses the GetObject function to check if there is a running instance of Outlook. If it is found, the existing instance is used, otherwise a new instance is created using CreateObject function. Then the rest of the code is same as the previous example, it creates a new mail item, specifying the recipient’s email address, subject, and body of the email and sends it.

You may also need to add reference to Microsoft Outlook Object Library by going to Tools -> References in VBA editor – in case you dont want to use dynamic runtime binding.

Excel VBA Send Bulk Email with Different content

Using VBA to send Outlook emails is a great way to save time and streamline your workflow. With this code, you can easily automate the process of sending emails through Outlook to multiple receipients (different email address with different content), allowing you to focus on other important tasks.

In summary, VBA and Outlook can be used together to automate the process of sending emails, saving time and effort. With this VBA code, you can check for running instances of Outlook and utilize it or create a new instance. This code also allows you to add attachments, CC and BCC to the emails. Additionally, having a reference to the Microsoft Outlook Object Library is necessary to run this code. Making use of this technique can help businesses and organizations to optimize their workflows and improve efficiency.

External References:

1. Outlook OlItemType enumerations – OlMailItem

Related Topics that might Interest you:

1. Automate sending emails using Outlook VBA
2. Choose Outlook account to send email from VBA Code
3. Send Mass email from Excel VBA