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.
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