Need vba code to send email and screenshot to individual if form textbox exceeds certain amount.

Need vba code to send email and screenshot to individual if form textbox exceeds certain amount.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Isn't someone looking at the screen?  Why would you need to email someone else?  When would you want this to happen?  After they changed the value but Before they saved the record or After?  If Before, what if they don't actually save?  Please tell us what you are trying to accomplish.
deer777Author Commented:
Currently I am summing the value of another field in this calculated field.  

If the calculated field exceeds 3 then the manager wants to know immediately to perform certain actions.
Jeffrey CoachmanMIS LiasonCommented:
Agree with Pat.

Some ways to avoid this:
1. Create a combobox with only valid entries
2. Set the "Field Size" property in the table to be the max text length

What you are asking is fairly involved for something that can be avoided...

send email and screenshot to individual
"THE" individual making the mistake?
to "Some other" individual...?

Instead of allowing them to make a mistake,  and then sending an email,
...why not prevent the mistake from occurring in the first place...?

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

deer777Author Commented:
Email will be sent to another individual.  

I asked the same question but was told to just send an email immediately.

Do not have to have a screenshot just thought it would be more informative.
deer777Author Commented:
The email should be sent after the record was saved.
Jeffrey CoachmanMIS LiasonCommented:
The screenshot requirement complicates things a bit too much (IMHO)
...and there is still may be a lot that is unknown about your requirements

So lets keep this simple as a start...

As an alternative, can display the values directly in the body of the email.

Using 100 as the triggering threshold, could use code like this on the after update event of the form:

Private Sub Form_AfterUpdate()
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    If Me.Result > 99.99 Then
        With objOutlookMsg
            Set objOutlookRecip = .Recipients.Add("")
            objOutlookRecip.Type = olTo
            .Subject = "Excessive Amount"
            .Body = Me.Val1 & " + " & Me.Val2 & " = " & Me.Result
            .Importance = olImportanceHigh
        End With
    End If

Very basic sample is attached
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Open in new window

This should get you off to a good start...

Give it a try and see if you can modify it to get what you need


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
You can use the code I have posted for my crashreporter on my blog:
TheSmileyCoder: Crash Reporter
Its original intent is to screenshot the the application whenever a crash occurs, and email that info to the developer, but you should be able to modify it to screenshot your form, and email that instead.
Jeffrey CoachmanMIS LiasonCommented:

Oh, ...and for my proposal you will have to set a reference to the Outlook VBA library in your code.
deer777Author Commented:
Jeff, not having any luck in the code working.  I am probably doing something wrong.  The textbox on the form is calculated based on the sum of another textbox on the continuous form.  I have the code running in the after update of the total textbox which holds the sum.  I updated my references to include Outlook.  Even tried putting the code in the form current but had no luck there.  Just not receiving email at all.
Jeffrey CoachmanMIS LiasonCommented:
IMHO, ...doing calculations in the query might be a better option.

I cant speculate on why this is not working for you without a sample of the db you are using...

But first things first, ...does my sample perform as needed?
The code needs to be in the Form's AfterUpdate event as Jeff coded it.  Put a breakpoint in the code so you can step through it to see where it is going astray.
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.