Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Small Email Sheet Question (2000)

    The following code, thanks to many here, copies the active sheet to a new workbook, and uses the sendmail method to open OE and send the sheet as an attachment. The code as is works fine. The question I have is if the user closes OE without sending the email, then OE is closed, but the workbook created in the code remains visible in a new window. Is there any way to "read" some property of the OE window so that if the user closes it without sending the message, the new workbook is closed? (I may have this in the wrong forum)

    Here is what I am using:

    <pre>Sub MailActiveSheet()
    On Error Resume Next
    Dim strPath As String
    resp = MsgBox("This will email the active sheet. Proceed?", vbOKCancel)
    If resp = vbCancel Then End
    strPath = ActiveWorkbook.Path
    If Right(strPath, 1) <> "" Then
    strPath = strPath & ""
    End If
    ActiveSheet.Copy
    With Cells
    .Copy
    .PasteSpecial xlPasteValues
    End With
    [A1].Select
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=strPath & ActiveSheet.Name
    Application.DisplayAlerts = False
    ActiveWorkbook.SendMail Recipients:=""
    Application.ScreenUpdating = True
    End Sub
    </pre>


    Thanks,

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Small Email Sheet Question (2000)

    Nevermind...if I add Activeworkbook.Close to the end all seems well...

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Small Email Sheet Question (2000)

    Just one remark on your code. Are you deliberately using End in stead of Exit Sub in this line:

    If resp = vbCancel Then End

    Be aware that End resets all global variables in your project, which is not always desired. I never use End.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Small Email Sheet Question (2000)

    Thanks Jan, I had not thought of that!

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Small Email Sheet Question (2000)

    I tend to have a Global boolean variable in my projects called bVarsOK, which I test at crucial subs to see if variables have lost their values, e.g.

    Public bVarsOK as Boolean
    '......
    If Not bVarsOK then ReadIni


    Then in the ReadIni Sub (end of the sub) :

    bVarsOK=True
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •