Forum Discussion

unhappyuser's avatar
unhappyuser
Copper Contributor
Oct 01, 2019

Macro issue with email connectivity

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 lapt...
macro
unhappyuser's avatar
unhappyuser
Copper Contributor
Oct 03, 2019

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

 

 

Smitty Smith's avatar
Smitty Smith
Former Employee
Oct 03, 2019

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.

  • unhappyuser's avatar
    unhappyuser
    Copper Contributor
    Oct 03, 2019

    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?  

    • Smitty Smith's avatar
      Smitty Smith
      Former Employee
      Oct 03, 2019

      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.

      • unhappyuser's avatar
        unhappyuser
        Copper Contributor
        Oct 03, 2019

        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.  

Resources