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

Help in Email Envelope VBA (If email not sent then undo last action)

Dear team,

Good Day!

Once again looking for help from this forum as most of my queries are resolved from here.

I am preparing a file wherein user has to update IN and OUT time.

The "Time Stamp" (in attached file) button will auto update the time in selected cell then same VBA will prepare email (within excel via Email Envelope) and user has to send the email manually by clicking "Send this Selection" as ".Item.Send" (in VBA) command is asking approval (taking 5 seconds to Allow, which I don't need)

My condition is, if user does not click on "Send this Selection" (if not send email) button then the time stamp (from last filled cell) should be cleared.

83463

In simple words, It's mandatory to send email otherwise timestamp will be cleared.

I hope, I am able to explain the situations.

I have attached the file.

Thanks and Ward Regards
 

Attachments

  • TEST USER - Copy.xlsb
    23.2 KB · Views: 2
I don't think I've heard the question yet. Are you asking how to tell whether five seconds has passed, or how to clear a cell, or...?
 
Hi Bob,

Thanks for your reply. :)

As per above attached scenario, first I have to click on TimeStamp to fill the live time-date on cell then specific cell is auto selected and excel VBA is forcing to send the email.

As of now, if I have not send the email then too time is updated in cell.

I asked, if I not send the email then time-date in active cell should not filled, it should keep as blank (if email not sent).

Sorry for bad English.

Thanks and Regards,
-Khan
 
"Never make fun of someone who speaks broken English; it means he speaks one more language than I do." And I think your English was ok, anyway. So you want your program to watch for a five-second delay, and if it's been five seconds without the user hitting <Send> you want the program to clear out the time/date value in the worksheet?

I understand that. But I think you've already tried to do that, and it isn't working. What did you try, and how does it not work? Give details of your program.

Also, if you're having trouble explaining yourself, try explaining in your own language. I can read and write several, and Google Translate also does a pretty good job.
 
Hi Bob,

Thanks for your support and replies.

Forget about the 5 seconds delay and everything.

Let me explain you from beginning step by step;

Step1
Mr A (cell A3) is started to work on some project.

Step2
Once he started the project, he has to select cell D3 (indicated "IN").

step 3
after D3 selection, he has to click on "Time Stamp" button manually.

step4
Once he click on "Time Stamp" button, a time (in D3) cell in specific format will be updated.

step5
after time stamp, system auto selection entire row till top (auto together with time-stamp button)

step 6
an email envelop will be auto populated (auto together with time-stamp button)

step 7
here Mr A has to click on "Send this Selection" manually (To and Cc email is pre-defined)


[HERE IS IMP POINTS, WHERE I NEED HELP]

On step 7, if Mr A does click on "Send this selection" then email will go to recipience
But, if Mr A does not click on "Send this selection" then email will no go.


WHAT I NEED HELP IS;

1 Till step 6 everything is fine.
2 If at Step 7, if Mr A does send the email then everything will be ok.
3 If at Step 7, if Mr A does not send the email then time stamp on cell D3 should be cleared.

in Simple words, if no email sent then no time-stamp should be updated.


I hope I am able to explain.


Hope from you Bob.

Thanks and Regards,
-Khan
 
Good explanation. I'm not sure this next question matters, but would it be just as good if your program waited until the email was sent and then filled in the time (rather than filling it in immediately and then clearing it out if the operator didn't hit <Send> within the time limit)?

The reason I said I'm not sure it matters is that I think you can do either one, but your program needs to detect an "event" sent by Outlook. Events, if you don't know already, are things that Excel (or in this case Outlook) can sense and automatically run a program in response. For instance, I have a worksheet that detects when I double-click on a particular cell and responds by filling in some fields on that row. I'm pretty sure we'll find that Outlook has an event that detects when an email is sent. I've never written a VBA program to use that event, but, let' see.... Yes, here it is at https://learn.microsoft.com/en-us/office/vba/api/outlook.mailitem.send(even). When the operator sends the email item, a program named <mailobject>.Send is run (if it exists). You can tell Outlook what that program should do.

If I understand this page correctly, the problem is this program has to be in Outlook, not Excel. I would be surprised if there's no way to get Outlook and Excel to communicate with each other about this event, but I don't know how exactly it would work; you'll probably have to experiment.

By the way, rather than making the operator click on the date button, your program can sense the new value in A3 and fill in the date automatically (if you want to go that way). That would be triggered by another program that senses the BeforeChange event. It can be pretty handy, and doing something automatically can be pretty cool. But programming events does take a little extra work and knowledge.
 
Dear Bob,

Thanks for your valuable time. :)

You are really very helpful and you always gives your 100% to understand the situation and try to find the most accurate solution.

Here you are correct, the issue is not the excel, actually excel is performing all its VBA coded correctly.

Only the issue is, I am not be able to connect the Excel and Outlook to communicate with each other.

Your link is really helpful but unfortunately I am really not that much capable to understand the VBA things that deeply.

Still I am trying to find some solution, In case if I find anything , then sure I'll post here.

Thanks and Always Best Regards,
-Khan
 
I'm pretty sure there has to be a way to sense an Outlook event in Excel, but I'd have to experiment to see how it might work.

I work part-time for a handful of different clients, and recently they haven't had much for me to do, so I get bored and look into Chandoo to see what might be happening here. Now it happens I have some things to do for the clients. But when I get bored again, I'll try to remember how to work this; if I do remember, and if I get something working, I'll let you know.
 
Back
Top