How To Send Outlook Email via Seperate Sub [Excel VBA]

In this tutorial, we will create a separate procedure to send an Outlook email. Specifically, we will grab the email details from our main sub and pass it on as arguments into a separate sub which is exclusively designed to send an email based on those arguments. Why do this? It will make our main sub look less cluttered, make it more convenient while sending multiple emails in a loop and make it easier to manage errors as well.

Blank Email Template

Ok. Lets start with the basic email code. Here’s the code template that we are going to use. .

  1. Add a reference to Outlook
  2. Create an email object
  3. Fill in the email details and send

For more information on how to setup this code, please check out the How To video or the tutorial.

Sub Send_Email_Blank()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 1: Add a Reference to Outlook
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tools -> References -> Microsoft Outlook X.X Object Library
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 2: Create a Blank Email
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 3: Fill in the Email Details and Send
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With oMail
    .Display
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .HTMLBody = "" & .HTMLBody
    '.Attachments.Add
    '.Send
    .Display
End With

End Sub

Single Email with Details

Lets start filling in the details.

For the folder path for attachments, email body and subject we will create constants as these won’t change regardless of whom the email is being sent to for our scenario.

For the To, the CC and the attachment file, we will refer to our worksheet data. Our data range is here.

For now, we will just read the top line of data, that is, in row 2.

Option Explicit
Const csFolderPath As String = "C:\Youtube\Outlook Tutorial 01\Outlook\Multiple Email Loop\"
Const csBody As String = "Hi there,<br><br>" & _
                "Please find our latest statement attached.<br><br>" & _
                "Regards,"
Const csSubject As String = "Statement of Overdue Invoices (Food Jar Co)"

Sub Send_Email_No_Seperate_Sub()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 1: Add a Reference to Outlook
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tools -> References -> Microsoft Outlook X.X Object Library
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 2: Create a Blank Email
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WORKSPACE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim emTo As String, emCC As String, emAttach As String
emTo = wsData.Range("A2").Value
emCC = wsData.Range("C2").Value
emAttach = wsData.Range("E2").Value
emAttach = csFolderPath & emAttach

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 3: Fill in the Email Details and Send
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With oMail
    .Display
    .To = emTo
    .CC = emCC
    .BCC = ""
    .Subject = csSubject
    .HTMLBody = csBody & .HTMLBody
    .Attachments.Add emAttach
    '.Send
    .Display
End With

End Sub

Email with Separate Sub

We will now create a separate sub to send this email. The sub will take in our variables as arguments.

Copy over step 2 and Step 3 code from the main sub into the new sub. And change the variable names to the argument names that are being passed through the new sub.

For the final bit. We need to call the Send Email sub from our main sub. We will pass in our three variables as arguments.

Option Explicit
Const csFolderPath As String = "C:\Youtube\Outlook Tutorial 01\Outlook\Multiple Email Loop\"
Const csBody As String = "Hi there,<br><br>" & _
                "Please find our latest statement attached.<br><br>" & _
                "Regards,"
Const csSubject As String = "Statement of Overdue Invoices (Food Jar Co)"

Private Sub Send_Email_from_Seperate_Sub_No_Loop()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 1: Add a Reference to Outlook
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tools -> References -> Microsoft Outlook X.X Object Library

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WORKSPACE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim emTo As String, emCC As String, emAttach As String
emTo = wsData.Range("A2").Value
emCC = wsData.Range("C2").Value
emAttach = wsData.Range("E2").Value
emAttach = csFolderPath & emAttach

Call Send_Email(emTo, emCC, emAttach)

End Sub

Private Sub Send_Email(psTo As String, psCC As String, psAttach As String)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 2: Create a Blank Email
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 3: Fill in the Email Details and Send
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With oMail
    .Display
    .To = psTo
    .CC = psCC
    .BCC = ""
    .Subject = csSubject
    .HTMLBody = csBody & .HTMLBody
    .Attachments.Add psAttach
    '.Send
    .Display
End With

End Sub

Send Emails in a Loop

If there is an error in the new Send Email sub, we wouldn’t want the whole procedure to just breakdown. Rather, we can record the fact that an error has occurred, alert the user of the same and gracefully exit the whole sub. Alternatively, if there are no errors, we could simply communicate that the email was sent successfully.

For this, first we need to change the Sub to a Function which returns a True if there are no errors, and False if there were errors.

Private Function Send_Email(psTo As String, psCC As String, psAttach As String) As Boolean

End Function

Let’s change the logic in the main sub first.


If Send_Email(emTo, emCC, emAttach) = False Then
MsgBox "Error Occured"
Else
MsgBox "Sent Ok"
End If

Coming back to our Function. Right at the top, we will put in our error handler.

On Error GoTo SystemErrorHandler

Now, if an error occurs, it will go to the Line Label SystemErrorHandler, which we will define now.


SystemErrorHandler:
Send_Email = False

However, if the email is sent and we reach the end of the With statement without any errors, then , lets return True and exit the function.

    
Send_Email = True
Exit Function

And that’s the function.

Full Sample Code


Option Explicit
Const csFolderPath As String = "C:\Youtube\Outlook Tutorial 01\Outlook\Multiple Email Loop\"
Const csBody As String = "Hi there,<br><br>" & _
                "Please find our latest statement attached.<br><br>" & _
                "Regards,"
Const csSubject As String = "Statement of Overdue Invoices (Food Jar Co)"

Sub Send_Email_from_Seperate_Sub_No_Loop()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 1: Add a Reference to Outlook
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tools -> References -> Microsoft Outlook X.X Object Library
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WORKSPACE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim emTo As String, emCC As String, emAttach As String
emTo = wsData.Range("A2").Value
emCC = wsData.Range("C2").Value
emAttach = wsData.Range("E2").Value
emAttach = csFolderPath & emAttach

If Send_Email(emTo, emCC, emAttach) = False Then
    MsgBox "Error Occured"
    Else
    MsgBox "Sent Ok"
End If
    
End Sub

Private Function Send_Email(psTo As String, psCC As String, psAttach As String) As Boolean
On Error GoTo SystemErrorHandler
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 2: Create a Blank Email
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 3: Fill in the Email Details and Send
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With oMail
    .Display
    .To = psTo
    .CC = psCC
    .BCC = ""
    .Subject = csSubject
    .HTMLBody = csBody & .HTMLBody
    .Attachments.Add psAttach
    '.Send
    .Display
End With
    Send_Email = True
    Exit Function
SystemErrorHandler:
    Send_Email = False
End Function

Function in a Loop

Let’s loop over the range on data that we have here and send an email for each row. The beauty of this is that there is absolutely no change to the function that we have just created. Ok.

We will just change the logic when calling the Send Email function. Returning a message each time the function is a called would mean a message for each row of data in context of a loop. Here, we will declare a Boolean variable bErrorOccured which captures if there was atleast one error and lets us know (after the loop is over) whether there was any error or not.

Option Explicit
Const csFolderPath As String = "C:\Youtube\Outlook Tutorial 01\Outlook\Multiple Email Loop\"
Const csBody As String = "Hi there,<br><br>" & _
                "Please find our latest statement attached.<br><br>" & _
                "Regards,"
Const csSubject As String = "Statement of Overdue Invoices (Food Jar Co)"

Private Sub Send_Email_from_Seperate_Sub_No_Loop()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 1: Add a Reference to Outlook
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tools -> References -> Microsoft Outlook X.X Object Library

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WORKSPACE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim emTo As String, emCC As String, emAttach As String
Dim lrow As Long
lrow = wsData.Range("A" & wsData.Rows.Count).End(xlUp).Row
Dim bErrorOccured As Boolean
bErrorOccured = False

Dim i As Long
For i = 2 To lrow
    emTo = wsData.Range("A" & i).Value
    emCC = wsData.Range("C" & i).Value
    emAttach = wsData.Range("E" & i).Value
    emAttach = csFolderPath & emAttach
    If Send_Email(emTo, emCC, emAttach) = False Then
        bErrorOccured = True
    End If
Next i
    
If bErrorOccured = True Then
    MsgBox "Atleast One Email wasn't sent"
    Else
    MsgBox "All Emails were Sent Successfully"
End If
End Sub

Private Function Send_Email(psTo As String, psCC As String, psAttach As String) As Boolean
On Error GoTo SystemErrorHandler
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 2: Create a Blank Email
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 3: Fill in the Email Details and Send
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With oMail
    .Display
    .To = psTo
    .CC = psCC
    .BCC = ""
    .Subject = csSubject
    .HTMLBody = csBody & .HTMLBody
    .Attachments.Add psAttach
    '.Send
    .Display
End With
    Send_Email = True
    Exit Function
SystemErrorHandler:
    Send_Email = False
End Function

Related Posts