Auto Email list

[Tablecloth98]

I'm currently trying to set up an automated email system. The system is supposed to go down a list of email addresses and email every customer. The code I have so far in vba is:
Sub Email()

Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String
EmailSubject = "Distribution Test"

SendTo = Range("B5")

EmailBody = "Distribution List Test Mail"

Set App = CreateObject("Outlook.Application")
Set Itm = App.createitem(0)

With Itm
.Subject = EmailSubject
.To = SendTo
.Body = EmailBody
.Send
End With

Set App = Nothing
Set Itm = Nothing

End Sub

Where it says SendTo = Range("B5") I'd like to have it email all addresses in column B starting with B5.
I'd also like to specify which email address to send the messages from but can't figure out how to do this.
In the subject line I need to lookup a value from a column (let's say column c) that corresponds to the correct email address.

Does anybody know how I can do this? (Apologies if these are silly questions. I'm very new to macros)

 

[DanteAmor]

Hi @Tablecloth98 . Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this.
Change "email@domain.com" to the email from which you will send the emails.

Sub Email()
  Dim App As Object, Itm As Object
  Dim EmailSubject As String
  Dim SendTo As String
  Dim EmailBody As String
  Dim ccTo As String
  Dim i As Long
  
  For i = 5 To Range("B" & Rows.Count).End(3).Row
    Set App = CreateObject("Outlook.Application")
    Set Itm = App.createitem(0)
    
    SendTo = Range("B" & i).Value
    EmailSubject = Range("C" & i).Value
    EmailBody = "Distribution List Test Mail"
    
    With Itm
      .SentOnBehalfOfName = "email@domain.com"   'specify which email address to send the messages from
      .Subject = EmailSubject
      .to = SendTo
      .Body = EmailBody
      .Display
    End With
    
    Set App = Nothing
    Set Itm = Nothing
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Tablecloth98]

Hi @Tablecloth98 . Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this.
Change "email@domain.com" to the email from which you will send the emails.

Sub Email()
  Dim App As Object, Itm As Object
  Dim EmailSubject As String
  Dim SendTo As String
  Dim EmailBody As String
  Dim ccTo As String
  Dim i As Long
 
  For i = 5 To Range("B" & Rows.Count).End(3).Row
    Set App = CreateObject("Outlook.Application")
    Set Itm = App.createitem(0)
   
    SendTo = Range("B" & i).Value
    EmailSubject = Range("C" & i).Value
    EmailBody = "Distribution List Test Mail"
   
    With Itm
      .SentOnBehalfOfName = "email@domain.com"   'specify which email address to send the messages from
      .Subject = EmailSubject
      .to = SendTo
      .Body = EmailBody
      .Display
    End With
   
    Set App = Nothing
    Set Itm = Nothing
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

That works perfectly you're a legend. Thank you so much