Object Variable or With block variable not set

[Doedtman]

Hello!
I'm having some problems with a macro that I'm trying to set up. I'm trying to send out emails to a list of addresses in an excel spreadsheet, but I keep getting this error: "Object variable or With block variable not set" I'm not sure what this means, or how I can go about fixing this. Any help would be greatly appreciated!

Sub SendEmailWithAttachment()
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim strEmail As String, strName As String
Dim lRowCount As Long

Set objOutlook = CreateObject("outlook.application") ' Start Outlook

lRowCount = 2 'get email address from column B

Do Until ActiveSheet.Cells(lRowCount, 2) = "" ' check for email
strEmail = ActiveSheet.Cells(lRowCount, 2).Value ' get email Address
strName = ActiveSheet.Cells(lRowCount, 1).Value ' get name of file
Set objOutlookMsg = objOutlook.CreateItem(olMailItem) ' create new email msg
With objOutlookMsg ' Fill email
.Subject = ActiveSheet.Cells(lRowCount, 6).Value 'Get subject from column F
.Body = ActiveSheet.Cells(lRowCount, 7).Value ' Get body from column G
.To = strEmail
.Attachments.Add ("C:\Documents and Settings\" & strName & ".pdf")
.Send
End With
lRowCount = lRowCount + 1
Loop

objOutlook.Quit
Set objOutlook = Nothing
Set objOutlookMsg = Nothing
End Sub


Thanks,
Jodi

 

[Norie]

Jodi

Where in the code do you get the error?

 

[Doedtman]

Do Until ActiveSheet.Cells(lRowCount, 2) = ""

 

[Norie]

Strange.

Anyway, try replacing ActiveSheet with an reference to the worksheet the data is on.

Dim wsData As Worksheet
Set wsData = Worksheets("EmailData")
 
Do Until wsData.Cells(lRowCount, 2) = "" ' check for email
      strEmail = wsData.Cells(lRowCount, 2).Value ' get email Address
 
....

 

[BarCodeWiz]

Indeed strange. Works here...

 

[Doedtman]

If I change the code to what you suggested I get a different error:

"Subscript out of range"

 

[Norie]

Did you change the worksheet name from the one I used 'EmailData' to the name of the worksheet your data is on?

By the way, where are you actually running the code?

 

[Doedtman]

I'm running the code in excel. I did change the worksheet name. Here's what the new code is:

Sub SendEmailWithAttachment()
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim strEmail As String, strName As String
Dim lRowCount As Long

Set objOutlook = CreateObject("outlook.application") ' Start Outlook

lRowCount = 2 'get email address from column B
 
Dim wsData As Worksheet
Set wsData = Worksheets("Email")
 
Do Until wsData.Cells(lRowCount, 2) = "" ' check for email
    
strEmail = wsData.Cells(lRowCount, 2).Value ' get email Address
strName = ActiveSheet.Cells(lRowCount, 1).Value ' get name of file
Set objOutlookMsg = objOutlook.CreateItem(olMailItem) ' create new email msg
With objOutlookMsg ' Fill email
.Subject = ActiveSheet.Cells(lRowCount, 6).Value 'Get subject from column F
.Body = ActiveSheet.Cells(lRowCount, 7).Value ' Get body from column G
.To = strEmail
.Attachments.Add ("C:\Documents and Settings\" & strName & ".pdf")
.Send
End With
lRowCount = lRowCount + 1 
Loop

objOutlook.Quit
Set objOutlook = Nothing
Set objOutlookMsg = Nothing
End Sub

 

[Doedtman]

It's working now! I was going line by line in VB and it kept showing an error. But when I click on the command button in excel, it works perfectly. Not sure why. Thank you very much for your help!

Jodi

 

[Norie]

Jodi

You should replace ActiveSheet with wsData throughout the code, I only posted an example of how to change a couple of lines.