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

  • 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

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.
 

AlanSidman

Active Member
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.
 
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.
Hi Alan,

I have pasted the code mentioned by you in the spreadsheet, however its still not sending the mail
 
Hi Alan,

I have pasted the code mentioned by you in the spreadsheet, however its still not sending the mail
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.
here is the attachment wherein i have added the code
 

Attachments

charlesdh

Member
Hi,
Check this part of your code. It will show the user form
Code:
 Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Form1.Show '' Add this code
 
Hi,
Check this part of your code. It will show the user form
Code:
Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Form1.Show '' Add this code
Charles,
I have checked this code, but its still not working
 
Hi,
I looked at the code. What do you mean by:

I ran the email code and it worked.
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
 

AlanSidman

Active Member
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?
 
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?
Alan,
I am not getting any error message.

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

charlesdh

Member
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
 

Attachments

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

charlesdh

Member
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
 
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
Charles,

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

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

charlesdh

Member
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:
Private Sub Workbook_Open()
Form1.Show
End Sub
and this code you the call to send the email
Code:
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:
Cha
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:
Private Sub Workbook_Open()
Form1.Show
End Sub
and this code you the call to send the email
Code:
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
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.
 

charlesdh

Member
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!
 

Attachments

Last edited:
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!
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
 

charlesdh

Member
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!
 

Attachments

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!
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.
 

charlesdh

Member
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
 

Attachments

Top