1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open a Form at the time of closing any workbook

Discussion in 'VBA Macros' started by msharma864512, Oct 26, 2018.

  1. msharma864512

    msharma864512 Member

    Messages:
    128
    Hi,

    I am trying to write a code, which will help me in filling up a form.

    I have created an automated tool and for that I need to track the saving generated form it. And for that I am trying to write a code so that everytime someone uses that file and whenever that person closes the file a window will popup and he/she will be asked to fill up certain details mentioned below:

    Project Name:
    GPN
    Employee Name:
    Saving Hours:
    Saving USD:

    As soon as that person closes the file after filling the above details. Then this form will be sent to pre-defined email id :leo.msharma@gmail.com in an excel spreadsheet.

    Please help me with this one.

    You can use any blank spreadsheet to write the code.
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    See attached

    Attached Files:

  3. msharma864512

    msharma864512 Member

    Messages:
    128
    Alan,

    Thank u very much for helping on this.

    However my actual task is to send the log sheet directly to an email id as soon as the file get closed.

    In this case pre-defined email id is : leo.msharma@gmail.com
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    What email program do you use. It makes a difference what program you are using to originate the mail. You may want to look at this link on how to send emails from Excel using VBA.

    http://www.rondebruin.nl/win/section1.htm

    See what you can come up with and post back here with your code and what is happening good or bad. We can assist, but you need to show some effort here.
  5. msharma864512

    msharma864512 Member

    Messages:
    128
    Hi Alan,

    I have pasted the code mentioned by you in the spreadsheet, however its still not sending the mail
  6. msharma864512

    msharma864512 Member

    Messages:
    128
    here is the attachment wherein i have added the code

    Attached Files:

  7. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    Check this part of your code. It will show the user form
    Code (vb):

     Kill TempFilePath & TempFileName & FileExtStr

        Set OutMail = Nothing
        Set OutApp = Nothing

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        Form1.Show '' Add this code
  8. msharma864512

    msharma864512 Member

    Messages:
    128
    Charles,
    I have checked this code, but its still not working
  9. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    I looked at the code. What do you mean by:
    I ran the email code and it worked.
  10. msharma864512

    msharma864512 Member

    Messages:
    128
    Charles,

    I tried running this code from my spreadsheet, but its not showing anything.

    can you advise me how it worked on ur system, and how can i make it run.

    Because what I am looking at is as and when as i close the file and after filling the form, it should automatically send the mail
  11. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    Just for the record, I can not offer any assistance here as I do not use Outlook as my email program. Hence, I cannot test your code. I am sure you are in good hands with Charles from this point on. Do you get an error message when you run the code? If so, what line of code is highlighted when you get the error message and click on debug?
  12. msharma864512

    msharma864512 Member

    Messages:
    128
    Alan,
    I am not getting any error message.

    If you help me with gmail mails too then also it would be helpful.
  13. charlesdh

    charlesdh New Member

    Messages:
    29
    Here's copy that worked when I tested it. I have my email if send too.
    You can use it to see if it come too me. Also you need to make sure the email you were using is correct.
    Charles

    Attached Files:

  14. msharma864512

    msharma864512 Member

    Messages:
    128
    Charles,

    I have received the mail you sent, but unfortunately its not working on my system.

    For your information, I am using Outlook-2013 as an email platform and excel 2013
  15. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    I have 2010 for Excel and also Outlook. I do not think that's the problem.
    Are you getting any error messages?
    Try stepping thru the code to see if it's actually getting the point where it's suppose to send the email.

    Charles
  16. msharma864512

    msharma864512 Member

    Messages:
    128
    Charles,

    I tried debugging the code, but couldn't see any error.

    While running the file too, i am not getting any error message.
  17. AlanSidman

    AlanSidman Active Member

    Messages:
    407
  18. msharma864512

    msharma864512 Member

    Messages:
    128
    Alan,

    I did go through my code with F8 to verify each step. And while debuging the code its working fine.

    But whenever I try it while closing the workbook.

    Its not working
  19. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    You want the code to run when you close the workbook. If the user form is active prior to closing the file you can do a "Call" to run the email code when you click the "Close" button.
    You should not use the "X" in the top left conner of the worksheet to close the workbook.
    In you "Workbook" module use this code to call the userform
    Code (vb):


    Private Sub Workbook_Open()
    Form1.Show
    End Sub
     
    and this code you the call to send the email
    Code (vb):


    Private Sub CommandButton1_Click()
    Dim lr As Long
    Dim ws As Worksheet
    Set ws = Sheets("Log")
    lr = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & lr) = Me.TextBox1.Value
    ws.Range("B" & lr) = Me.TextBox2.Value
    ws.Range("C" & lr) = Me.TextBox4.Value
    ws.Range("D" & lr) = Me.TextBox5.Value
    ws.Range("E" & lr) = Me.TextBox6.Value
    ws.Range("F" & lr) = Date
    ws.Range("G" & lr) = Time
    Call Mail_ActiveSheet
    Unload Form1

    End Sub
     

    Charles
    Last edited: Oct 31, 2018
  20. msharma864512

    msharma864512 Member

    Messages:
    128
    Cha
    Charles,

    I tried adding the codes you gave to my module, but its still not working.

    It would be very helpful, if you could do that from your end and i can try afterwards.
  21. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    I moved the code you had in the spreadsheet module to a code module
    The way I have the file working is that when you ope the file the userform will be displayed so that the user will be able to add data. When they click the "Close" button this will cause the email to go out and the close the workbook with out saving it. If the way I have it set you can make changes to it.

    Charles

    You will need to change the email address!

    Attached Files:

    Last edited: Nov 2, 2018
  22. msharma864512

    msharma864512 Member

    Messages:
    128
    Charles,

    Unfortunately, its still not working. And on a different note, I want the form to be updated when someone try closing the file and then it should send the sheet to designated mail automatically.

    Regards,
    Manish
  23. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    I added a button to the form so that you save the new data. When you click the colse button it will send the email. It work on my system.
    You will need to change the email address. I left mine in it.
    If this still does not work for please tem me what the file is doing if you have error or whar ever.
    As mention the file I'm attaching does work for me!

    Attached Files:

  24. msharma864512

    msharma864512 Member

    Messages:
    128
    Charles, Unfortunately its still not working.

    And its not showing any error.

    One more thing, I dont want the form to appear at the time of opening the file, it should come at the time of closing the file.

    Can we try adding a different code here to email the worksheet.

    Maybe we can try adding a button in the worksheet, and we can send the mail by clicking on the worksheet.
  25. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    I can still see my code working at my end. I used the email you had in the original workbook for the "To" address. I have my Email in "CC". so if you test I should see the email.
    In this copy I modified the code so that it will send the Email and then produce the user form. You can then add data and then click the "Close" button. This will close the workbook and save the changes you made.

    Charles

    Attached Files:

Share This Page