Call Macro in VBA

[jpprespa]

Hello,

I have two macro's. One called SendEmail and one called Sendmassemail. In Sendmassemail, I would like to call SendEmail, but am getting an error 'Expected Variable or Procedure, not module'.

Here's my code

Sub Sendmassemail()


row_number = 1


Do
DoEvents
row_number = row_number + 1

Call SendEmail(Sheet1.Range("A" & row_number), "This is a test e-mail", Sheet1.Range("J2"))

lastRow = Range("A1:A").End(xlUp).Row + 1

Loop Until lastRow


End Sub

I checked the name of my module and it's the same. Am I calling this incorrectly?

Joe

 

[VoG]

You need to rename the module to something other than any subs that you have.

 

[jpprespa]

You need to rename the module to something other than any subs that you have.

Ok, I changed that. Now i get an error 'Object variable or With block variable not set' on 'Set olMail = olApp.CreateItem(olMailItem)'. Sorry, new to this.

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


Dim olApp As Outlook.Application
Set o1App = 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

 

[VoG]

Typo

Set olApp = CreateObject("Outlook.Application")

 

[jpprespa]

Typo

Set olApp = CreateObject("Outlook.Application")

Ok, one last one. I'm getting a Type Mismatch error with my loop. I want to loop through the email addresses in column A until I reach the last row.

Sub SendMassEmail()
Dim lastRow As Long


row_number = 1


Do
DoEvents
row_number = row_number + 1
Call SendEmail(Sheet1.Range("A" & row_number), "This is a test e-mail", Sheet1.Range("J2"))
lastRow = Sheet1.Range("A99999").End(xlUp).Row + 1
Loop Until lastRow = ""


End Sub

 

[VoG]

Try

Sub SendMassEmail()
Dim lastRow As Long, i As Long
lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
    Call SendEmail(Sheet1.Range("A" & row_number).Value, "This is a test e-mail", Sheet1.Range("J2").Value)
Next i
End Sub

 

[jpprespa]

That works great. I change .send to .display so that I could look at the output. For some reason my macro is opening another workbook rather than using the Active workbook when populating the email. I don't have any other workbooks open.

Thanks for your help, you've helped debug most of this.

Try

Sub SendMassEmail()
Dim lastRow As Long, i As Long
lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
    Call SendEmail(Sheet1.Range("A" & row_number).Value, "This is a test e-mail", Sheet1.Range("J2").Value)
Next i
End Sub