Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail excel file on close (Excel 00)

    Ok, I have a procedure for the workbook_close event...it automatically opens an outlook message with the workbook attached. my problem is that my blank template gets sent, not the completed workbook. Where do I /how do I write the save procedure so that my completed file gets sent, and not my blank template? attached below is the code i have:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .To = "hazlettjm@gcemnf-wiraq.usmc.mil"
    .CC = ""
    .BCC = ""
    .Subject = "Morning Report"
    .Body = ""
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail excel file on close (Excel 00)

    Are you sure this is a good idea? If it works, you'll get an e-mail if the user opens and closes the workbook. You open your mail, open the attached workbook and close it, and hey presto, you send a mail to yourself, unless you disabled macros when opening the workbook.

    The problem with your code is that it sends the saved version of the workbook, not the version that is open. To send the opened version, use

    ActiveWorkbook.SendMail "hazlettjm@gcemnf-wiraq.usmc.mil", "Morning Report"

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail excel file on close (Excel 00)

    <hr>Are you sure this is a good idea? If it works, you'll get an e-mail if the user opens and closes the workbook. unless you disabled macros when opening the workbook.<hr>
    I have a MsgBox that shows on the opening of the workbook that says "If this has already been sent, close the email dialogue that will open upon closing this workbook." Or something to that effect. This is necessary because the report must be sent by 8am daily, 7 days a week. Some of my clerks forget to mail it, and the kick in the face comes back at me. This report is only referenced once or twice a day at most, so it is practical to leave the email tag on there.
    <hr>You open your mail, open the attached workbook and close it, and hey presto, you send a mail to yourself,<hr>
    As a remedy to "presto", I added the line .Display, giving the user the option to close the email if it's already been sent.

    <hr>The problem with your code is that it sends the saved version of the workbook, not the version that is open. To send the opened version, use

    ActiveWorkbook.SendMail "hazlettjm@gcemnf-wiraq.usmc.mil", "Morning Report"<hr>
    I'm confused on this part. In my code, I have the line ".Attachments.Add ActiveWorkbook.FullName". I figured that would work, however, it doesn't.

    Hope that clarifies <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail excel file on close (Excel 00)

    The SendMail instruction would replace the entire code you have:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.SendMail "hazlettjm@gcemnf-wiraq.usmc.mil", "Morning Report"
    End Sub

    SendMail sends a copy of the workbook as currently displayed on your screen, including unsaved changes. The code you had doesn't look at the displayed version of the workbook, it sends the version as last saved to disk.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail excel file on close (Excel 00)

    Hans,
    Is it possible to display the mail instead of "presto"? <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail excel file on close (Excel 00)

    No, that's a downside of SendMail. You could ask the user whether the workbook should be sent:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("Do you want to send the workbook?", vbYesNo + vbQuestion) = vbYes Then
    ActiveWorkbook.SendMail "hazlettjm@gcemnf-wiraq.usmc.mil", "Morning Report"
    End If
    End Sub

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail excel file on close (Excel 00)

    Genius. Sheer genius. Thanks.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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