10-01-2019 08:50 AM
10-01-2019 08:50 AM
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 -
10-01-2019 02:54 PM
@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.
10-03-2019 08:40 AM
@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
10-03-2019 08:56 AM
@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.
10-03-2019 09:19 AM
' sendemailtopositions Macro
' Macro recorded 3/19/2010 by d.namdar
Dim strDate As String
Dim strDate2 As String
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
Let me know what you think. And do you work for microsoft?
10-03-2019 10:31 AM
@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.
10-03-2019 11:20 AM
@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.
10-03-2019 12:49 PM
@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
10-03-2019 01:59 PM
@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.
10-04-2019 05:38 AM
@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
10-04-2019 08:19 AM
@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.
10-04-2019 09:02 AM
@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.
10-04-2019 09:30 AM
@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.
10-04-2019 10:33 AM
@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).
10-04-2019 10:50 AM
@unhappyuser A subscript out of range error on that line indicates that 1) you don't have a sheet named ARB, and Excel throws an error because it can't find it, or 2) you have a sheet named something similar to "ARB", but not exactly, so again, Excel can't find it. Try putting an apostrophe in front of that line of code, which will turn it into a comment that VBA will ignore, and try stepping through again.
Just a heads up that it's generally not necessary to select sheets in code. But that line of code isn't in what you originally posted, so I'm not sure what's going on.