Macro issue with email connectivity

Copper Contributor

so i have a macro that i send each day for work - it's on my excel spreadsheet. it's supposed to automatically send my pnl sheet to the designated people on email. it works fine on my home laptop, but here on this computer it doesn't work for some reason

not sure if it's because there is an issue that the email is not linked somehow? or that maybe my antivirus is blocking - 

21 Replies

@unhappyuser Unfortunately, that's not much to go on...Does the code error? If so, what's the error? If it doesn't throw an error, but doesn't do anything else either, then I'd suggest stepping through the code with F8. It will help point you in the right direction.

 

It'll also help if you posted the code.

@Smitty Smith Can you explain the "step through the code with F8"?  I'm not a techie, so not sure what that means.  When I click "send PNL", nothing happens.  I then tried to click F8 as you mentioned, but nothing happened so I'm assuming there is something else I need to do here to gather the code.  Pls advise.  Thanks

 

 

@unhappyuser I'd first check and make sure your macro is assigned to the "Send PNL" button. If that doesn't fix it, then press Alt+F11 to open the VB Editor, then find the module with your mail routine in it. Once there, pressing F8 will step through the code one line at a time, so you can see what's going on. If the code throws an error you'll be able to see where.

 

FYI - Pressing F8 in Excel toggles Extend Selection. I point that out because it can drive you nuts if you turn it on and don't realize it.

@Smitty Smith 

 

Sub sendpnlemailandprint()

' sendemailtopositions Macro
' Macro recorded 3/19/2010 by d.namdar
' Mail_SheetsArray()

Dim strDate As String
Dim strDate2 As String
ActiveWorkbook.Save
Sheets(Array("POSIT", "RECAP", "-P&L-", "MTD")).Copy
strDate = Format(Date, "mm-dd-yy") & " " & Format(Time, "h-mm-ss")
strDate2 = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs "Todd PnL " & ThisWorkbook.Name _
& " " & strDate2 & ".xlsx"
ActiveWorkbook.SendMail Array (I removed the actual email addresses here fyi)
"Pierce P/L " & strDate2
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

 

Let me know what you think.  And do you work for microsoft?  

@unhappyuser Your code looks fine to me. What happened when you stepped through it?

 

And yes, I do work for Microsoft. I manage all the support content for Excel.

@Smitty Smith Not sure what you mean when u say "stepped thru it."  Basically i click on it and the email should be sent, but just nothing happens.  The process works totally fine on my home laptop, but not at my work setup.  Let me know.

 

Also, do you remote in ever to solve problems (or does someone on your team)?  Guessing it would be a lot easier for a tech to make sense of all this and fix.  

@unhappyuser Step though: press Alt+F11 to open the VB Editor, then find the module with your mail routine in it. Once there, pressing F8 will step through (or run) the code one line at a time, so you can see what's going on. If the code throws an error you'll be able to see where.

 

For remote access, I don't really have any way to do that, but you can always upload a sanitized (no sensitive private/personal information) copy of your workbook to OneDrive and share a link here.

@Smitty Smith I sent you the code that came up.  Was that not what you needed?  If not can you specify more detailed instructions because I tried your last set (after i sent you the code I had) and nothing different came up

@unhappyuser So what happened when you stepped through the code? Did it actually send an email?

 

Unfortunately, I've got meetings all afternoon, so I can't test it until later.

@Smitty Smith Not sure what u mean by saying "stepped thru the code".  I sent you the code that came up.  When i click "send email" from the button the excel macro, basically nothing happens (email is not sent out).  Pls advise when you have time or what u see in the code I sent.  Thx

@unhappyuser Stepping through the code with F8 executes the code one line at a time. This lets you test each line to see if it's executing properly. When you just click the button and it doesn't do anything doesn't tell you what's going on under the hood.

 

To step through the code, press Alt+F11 to open the VB Editor. Find the module with your code, then place your cursor anywhere within the procedure. Press F8, and Excel will highlight the first executable row in yellow. Keep pressing F8 until you complete the procedure.

 

FYI - I tweaked your code with my email address and it executes just fine, so you might want to check your button and make sure the macro is actually assigned to it.

@Smitty Smith there are 4 different modules that come up when i do the F11 function.  When I started clicking F8, on the third click I got an error message that said "run time error 9, subscript out of range"

 

pls advise.  Not really sure what else to do here

@unhappyuser OK, first, are you in the mail routine? Second, which line of code throws the error (it'll be highlighted in yellow)?

@Smitty Smith When you say "are you in the mail routine" i assume you mean am I using the alt-f11 combo while in the excel file that has the issue - answer is yes.

 

Nothing comes up when i hit f8 and try to identify the error because before i can keep hitting that multiple times to locate the error, i get the error message i wrote in my previous post.

@unhappyuser OK, I'd imagine your code is falling apart on this line:

 

Sheets(Array("POSIT", "RECAP", "-P&L-", "MTD")).Copy

 

Which tells me that one of those sheets doesn't exist, or isn't spelled the same way as it is in the code.

@Smitty Smith My questions then is "why does the macro and quick button work perfectly fine on my home computer, yet not on my work one"?  It's the same exact excel spreadsheet for both.  That is what I'm not understanding.  I feel like it has to somehow be the link between the macro on excel and the setup of my microsoft e-mail or the like.  Interested in your thoughts here.  

@unhappyuser Unfortunately, I have no idea. Like I said, the code works fine for me when I adjust it for my email address.

 

I did notice one thing when I ran it that Outlook throws a redemption notification (it's the dialog that says an app is trying to send a mail). You can get around that with this: http://dimastr.com/redemption/home.htm, although that generally doesn't cause an error, just a pause before the mail sends.

 

Ultimately, you'll need to figure out which line of code is throwing the subscript out of range error. 

@Smitty Smith How do I figure out what is causing that subscript error when I can't hit F8 more than 3 times to find the problematic code (before I get the error message)?  

@unhappyuser The error message is what's important. When it pops up, choose the Debug option. The message will disappear, and the offending line of code will be highlighted in yellow. That's what you need to fix (presuming it's the only error).