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
Smitty Smith's avatar
Smitty Smith
Former Employee
Oct 01, 2019

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.

  • 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?  

Resources