Sending Mass emails using VBA

Jafbr

New Member
Joined
May 31, 2015
Messages
7
Hello All,
I'm trying to use VBA to send a mass email I keep however getting an error message on this line

Call SendEmail(Sheet1.Range("A" & row_number), Sheet1.Range("J1"), Sheet1.Range("J2"))

Does anyone know where the error is? This is the entire code.


Sub SendEmail(what_address As String, subject_line As String, mail_body As String)


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.Send



End Sub
Sub SendMassEmail()


row_number = 1


Do
DoEvents
row_number = row_number + 1
Call SendEmail(Sheet1.Range("A" & row_number), Sheet1.Range("J1"), Sheet1.Range("J2"))

Loop Until row_number = 6


End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hey Tim,
Thanks for the reply, I'm getting the same error using this:

Call SendEmail(Sheets("Sheet1").Range("A" & row_number), Sheets("Sheet1").Range("J1"), Sheets("Sheet1").Range("J2"))

Any other tips?
 
Upvote 0
If it were me, I'd combine into one. Something like

Code:
Option Explicit

Sub SendMassEmail()
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem, ToContact As Outlook.Recipient
  Dim what_address As String, subject_line As String, mail_body As String
  Dim row_loop As Long
  
  Set olApp = CreateObject("Outlook.Application")
  
  
  For row_loop = 1 To 6         '< adjust as needed
    Set olMail = olApp.CreateItem(olMailItem)
    
    what_address = Sheets("Sheet1").Range("A" & row_loop)
    subject_line = Sheets("Sheet1").Range("J1")
    mail_body = Sheets("Sheet1").Range("J2")
    
    With olMail
      .display
      
      Set ToContact = .Recipients.Add(what_address)
      .Subject = subject_line
      .htmlbody = mail_body & .htmlbody
    End With
    
    Set ToContact = Nothing
  Next row_loop
  
  Set olMail = Nothing
  Set olApp = Nothing
End Sub


Tim
 
Upvote 0
Hey Tim,
Thanks! I'm getting an error on this line:

what_address = Sheets("Sheet1").Range("A" & row_loop)

any idea what it might be?
 
Upvote 0
If you are running the macro on the active sheet, you can change to Range("A" & row_loop) - if the loop start row and end row is set up correctly. In the example, start row is 1 and end row is 6.


Tim
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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
Back
Top