Macros to Send an Email using Excel.

What is the purpose of using Excel to email someone?

Reviewing the advantages of sending emails from Excel before getting into the specifics is vital because some of you who came upon this tutorial may not understand the concept.

These advantages consist of:

1. Optimizing your process.
2. Cutting down on the time required for the creation and delivery of emails.
3. Reducing the possibility of errors during the creation and delivery of emails.

Everything can be automated. For example, suppose your coworkers or employees frequently update spreadsheets or entire documents. In that case, Excel can automatically send you emails whenever a cell value changes, the document is updated in Excel, or a condition is satisfied.

You can add customer data from Excel spreadsheets to your emails to make them more engaging and personalized.

Five Ways to Use a Macro to Send Email using Excel

1. Sending Emails with the Outlook Object Library

We will enable "Microsoft Outlook 16.0 Object Library" to send emails from Excel in the first macro. Additionally, we must use Excel to log into our Outlook accounts.

Steps:

• We are going to open the Visual Basic window first.
• Open Excel and turn on the developer tab.
• The Developer tab contains the Excel email-sending features; however, some of you may not see this tab by default inside the toolbar.
• Navigate to File -> Options -> Customize Ribbon to make this change.

This should launch a new dialog box with the Developer tab enabled or disabled, among other tabs.

• First, choose Visual Basic from the Developer tab.

Alternatively, you can open the VBA window by pressing ALT + F11.

• Second, choose "References..." under Tools i.e., Tools>>>select Reference.

• Thirdly, click OK after selecting "Microsoft Outlook 16.0 Object Library."(Based on your Excel version, if it is 10, you can choose 14.0)

We will, therefore, activate Outlook Object Library.

Using Outlook Object Library for Email Transmission

• Them by choosing Insert >>> Module.

Here, we'll type our code.

• Enter the following code after that.

Explanation of the code:

1. The Sub Procedure Macro_Send_Email is the first thing we call.
2. Next, the variable types are being declared.
3. Thirdly, we have chosen Outlook to be our mail program.
4. Next, we are going to select cell D3 as our email address.
5. Then, our code sets the email content.
6. Ultimately, our Email is displayed here using the "VBA Display Property." As a result, sending the emails will require us to manually click Send. We can also send emails without displaying them by using the "Send Property" feature.
7. The ModuleModule should then be saved and closed.

The code will now be run.

• To get started, choose Macros under the Developer tab. i.e., Developer tab >>> select Macros.

The dialog box for macros will open.

• Secondly, choose "Macro_Send_Email" as our Sub Procedure.
• Lastly, click Run.

We will view the email window once the code has been executed. We can select Send now. So now you know how to send an email via Excel using VBA, the first way.

2. An Excel macro for sending emails from a Gmail account

We only require less safe app access of the Gmail account to use this method. Furthermore, we must activate Microsoft CDO via the References menu.

Steps:

• First, open the reference dialog box as instructed in the first method.
• Second, click OK after choosing "Microsoft CDO for Windows 2000 Library."
  
• Thirdly, open your Google account's settings and select Security.
• Lastly, enable access to less secure apps.
  
• Now, we have to open the macro code.
• As shown in the first method, open the mode and write the below code:

Code:

Explanation of the code:

1. The Sub Procedure Send_Gmail_Macro is the first thing we call.
2. Next, the variable types are being declared.
3. The email content is being set in our code, thirdly.
4. We then give our login information. Here, your ID and password must be typed by you.
5. Next, we have port-forwarding to 465.
6. Our Email is finally being sent.

Next, run this code after saving it.

An email to the provided address has been sent successfully.

3. Send an Email from a Column to a List of Recipients

Using Macro from Excel, we will send emails to seven recipients as part of the third approach. Since we'll locate the final row in our dataset, a longer list will function with our code. Emails will be sent from cells C5-C10.

Steps:

• Enter this code in the Module window first, as instructed in the first method

Code:

Explanation of the code:

1. We are first invoking our Macro_Send_Email_From_A_List Sub Procedure.
2. We're choosing the variable types in the second place.
3. Thirdly, Outlook is the mail application that we have chosen.
4. Next, we locate the final row in our dataset, which is 10.
5. After that, we entered 5 as the beginning value for "variable z" because our Email begins in row 5. Additionally, since our emails are in the C column, we have entered 3 in the Cells property.
6. The email content is then set in our code.
7. In conclusion, we use ".Display" to show our Email here. As a result, in order to send the emails, we must manually click Send. Additionally, we can send emails without displaying by using ".Send".

Next, save the ModuleModule and launch it.

We can observe that BCC is being used to display all of our emails. Finally, all we have to do is hit Send to finish this task.

4. Macro for Emailing a Single Sheet

We will email our target individual the Active Worksheet in this section. This is where we have to choose where our Excel file is saved.

Steps:

• Enter this code in the Module window first, as instructed in the method

Code:

Explanation of the code:

1. First, we call upon the Macro_Email_Single_Sheet subprocedure.
2. We're choosing the variable types in the second place.
3. Thirdly, the Active Sheet is copied and saved as a different Workbook.
4. Next, we decided to use Outlook as our mail application.
5. The email content is then set in our code.
6. Following that, the Sheet is attached to the Email.
7. Lastly, to display our Email, use ".Display". As a result, in order to send the emails, we must manually click Send. Additionally, we can send emails without displaying by using ".Send".

Next, save the ModuleModule and launch it.

The Sheet name will appear in the window. Click Send to finish the job.

5. Using Cell Value to Send Email Macro

We made some slight modifications to our dataset for the final approach. The dataset now includes the "Payment Due" column. We will directly send an email containing the city "Bristol." Since it is evident that row 5 holds it, we will only be sending that person an email.

Steps:

• Enter this code in the Module window first, as instructed in the method.

Code:

Explanation of the code:

1. Initially, we will refer to this Sub Procedure as Send_Email_Condition.
2. Second, we're designating "Conditions" as our Sheet and declaring the different types of variables.
3. Thirdly, the final row number has been located. Furthermore, row 5 is our last row in the code since our value begins there.
4. Next, execute Send_Email_With_Multiple_Condition, our second Sub Procedure.
5. Next, we chose Outlook to be our mail application.
6. Next, our code sets the email content.
7. We use the Attachment method to attach the Excel file to the Email.
8. Then, our Email is displayed here using the ".Display" extension. As a result, in order to send the emails, we must manually click Send. Additionally, we can send emails without displaying by using ".Send".

Next, save the ModuleModule and launch it.

Finally, we have demonstrated yet another way to send emails from Excel using VBA Macro.