VBA: Send E-mail using Outlook with E-mail addresses from a cell range

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
I am trying to get my VBA code to send an e-mail, where the subject and body of the e-mail are entered into text fields in a UserForm and the e-mail addresses are all listed in cells A1:A10 in a 2nd workbook on a sheet titled 'Admin Users'. I have the below code which works fine if I want a input an e-mail address into a text box but I can't work out how to get it to get my addresses from the cell range.

Any help would be most helpful.

Thanks.

VBA Code:
Dim oOutlook As Object

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If oOutlook Is Nothing Then
        MsgBox "Please open Microsoft Outlook (e-mail application) and then press 'Submit' again", vbCritical
    Exit Sub
    Else
    End If

'Sends e-mail
Dim OutApp  As Object
Dim OutMail As Object
Dim wbk     As Workbook

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Set wbk = <<<<<< This needs the path for the master workbook

    On Error Resume Next
    With OutMail
'        .To = '<<<<<<< Needs to get e-mail addresses from A2:A10 on sheet 'Admin Users'

        .Subject = Me.TxtEmailSubject
        .body = Me.TxtEmailBody
        
        .Send
    End With
        On Error GoTo 0
        
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set wbk = Nothing
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Try this

In the macro change "book2.xlsx" for the name of your book
Note: The second book must be open.

VBA Code:
Private Sub CommandButton1_Click()
  Dim oOutlook As Object
  
  On Error Resume Next
  Set oOutlook = GetObject(, "Outlook.Application")
  On Error GoTo 0
  
  If oOutlook Is Nothing Then
    MsgBox "Please open Microsoft Outlook (e-mail application) and then press 'Submit' again", vbCritical
    Exit Sub
    Else
  End If
  
  'Sends e-mail
  Dim OutApp  As Object
  Dim OutMail As Object
  Dim wbk     As Workbook
  Dim i As Long, users As String
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  'Set wbk = <<<<<< This needs the path for the master workbook
  
  For i = 2 To 10
    users = users & Workbooks("book2.xlsx").Sheets("Admin Users").Range("A" & i).Value & ";"
  Next
  
  On Error Resume Next
  With OutMail
    .To = users
    .Subject = Me.TxtEmailSubject
    .body = Me.TxtEmailBody
    .display '.Send
  End With
  On Error GoTo 0
  
  Set OutMail = Nothing
  Set OutApp = Nothing
  Set wbk = Nothing
End Sub
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
Perfect! With a slight tweak to make it open the 2nd workbook and it's working a dream.

Thank you kindly.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,364
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top