Run-time error 91

[jchillal]

Hello all,

I am trying to run the below macro, but getting Run-time Error 91 : Object variable or With block variable not set
Can you please help. It directs error at line SheetNames = EmailInfo.Columns(1).Cells.Value

Please Login or Register  to view this content.

[gmr4evr1]

Should be

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

[jchillal]

Hi gmr4evr1,

Thanks for the reply, i still get the same error message with this code

Please Login or Register  to view this content.

[jchillal]

Can anyone please help on this.

[CK76]

Please Login or Register  to view this content.

After above line stick in following and see what comes up in immediate window.

Please Login or Register  to view this content.

If it only returns single cell address, your workbook structure needs to be changed or referenced cell changed in code.
Upload sample workbook which produces the error you mentioned.

[jchillal]

Hi CK76,

Thanks for the reply. The macro works I keep the "C:\Data\Source.xls" open and run it. Not sure why.

Another error I am getting now is run-time error '-2147221080 (800401a8) it stops at the below code

Please Login or Register  to view this content.

[jchillal]

Can anyone help me to solve please.

[CK76]

I'd suggest uploading sample file. Or at least let us know what value variables hold at the time of the error. Hard to trouble shoot without it.

[jchillal]

Hi CK76,

I could fix these problems. Now I require to modify the range reference in this macro. The macro currently picks up all the sheets names in ColumnA and then the corresponding emails address columns B onwards. How do i modify it to pick the sheets names anything down D2 and then corresponding emails address from Columns E onwards.

[CK76]

Change...

Please Login or Register  to view this content.

To...

Please Login or Register  to view this content.

[jchillal]

thanks for the reply , when I change this, I get the error message 'run-time error '9': Subscript out of range

and stops at the below line

Please Login or Register  to view this content.

[CK76]

I'll need to see sample workbook(s) where it produces error. As I've written previously, it's difficult to debug your code without seeing how your data set has been set up in each workbook.

[jchillal]

Attached is the sample file

[jchillal]

hi CK76 , did you get chance to look into it?

[CK76]

You hadn't implemented my modification. Also, you had 1st row empty. The code did not account for that. Try below.

Please Login or Register  to view this content.

[jchillal]

Thanks for the reply. Now I get runtime error 438 : Object doesn't support this property or method and stops at the below line

Please Login or Register  to view this content.

[CK76]

Oh, just change to.

Please Login or Register  to view this content.

[jchillal]

Thanks for the help CK. It works fine. Just that I had to modify the j reference to 4 instead of 1 as it was taking sheet names as email addresses

Please Login or Register  to view this content.

Once the macro completes, the "Source" file automatically closes. Not sure why.

[CK76]

? I assumed this line was intended?

Please Login or Register  to view this content.

If you don't want to close file. Remove the line.

[jchillal]

yep, got it. Thanks for fixing these.