Sending outlook email via visual basic macro

Question

Attempting to use excel macro to send out email list excel macro.

Answer 1

Do you have MSOUTL.OLB in C:\Program Files\Microsoft Office\root\Office16 ?

If so, reference it. If not then you do not have Classic Outlook installed or it needs to be repaired.
https://support.microsoft.com/en-us/office/repair-an-office-application-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b

Answer 2

I tried that code. I am getting error indicating that oulook object is not available. I tried to use references to add Microsoft Outlook Object Library but do not see it in the list of references.

Answer 3

To send an email using an Excel macro that utilizes Outlook, you can use Visual Basic for Applications (VBA). Below is a basic example of how to set up your macro to send emails to a list of recipients stored in an Excel workbook:

1. Open Excel and press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
2. Insert a new module by right-clicking on any of the items in the Project Explorer, selecting Insert, and then Module.
3. Copy and paste the following code into the module:

      Sub SendEmails()
          Dim OutlookApp As Object
          Dim OutlookMail As Object
          Dim Recipient As Range
          Dim EmailBody As String
      
          ' Create a new Outlook application
          Set OutlookApp = CreateObject("Outlook.Application")
      
          ' Loop through each recipient in column A
          For Each Recipient In ThisWorkbook.Sheets("Sheet1").Range("A1:A10") ' Adjust the range as needed
              If Recipient.Value <> "" Then
                  Set OutlookMail = OutlookApp.CreateItem(0)
                  With OutlookMail
                      .To = Recipient.Value
                      .Subject = "Your Subject Here"
                      .Body = "Your email body here"
                      .Send
                  End With
              End If
          Next Recipient
      
          ' Clean up
          Set OutlookMail = Nothing
          Set OutlookApp = Nothing
      End Sub
   

4. Adjust the range in the code to match where your email addresses are located in the Excel sheet (e.g., A1:A10). You can also customize the subject and body of the email.
5. Run the macro by pressing F5 or by selecting Run from the menu.

This will send an email to each recipient listed in the specified range in your Excel sheet.

---

References:

• Sending Email to a List of Recipients Using Excel and Outlook
• Automating Outlook from Other Office Applications