New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Automated Email

Macros, VBA, Excel Automation, etc.

Re: Automated Email

Postby pecoflyer » Thu Mar 17, 2016 10:46 am

Please be careful to post in the correct forum.
This post was posted in the "Tips and Tutorials". I moved it for you.


As for your query, look at http://www.rondebruin.nl/win/s1/outlook/bmail9.htm
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Automated Email

Postby NoSparks » Thu Mar 17, 2016 11:37 am

Your first request's file contained code to send email.
Is the problem having AG26 initiate the process because it contains a formula ?
In that case you need to use the Worksheet_Calculate event.
Let us know if you need assistance.

Please, don't post files requiring a password.
It's trivial to bypass Excel passwords, but just the need to do so will have people ignore you.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Automated Email

Postby NoSparks » Sat Mar 19, 2016 8:05 am

In case you come back, and assuming my assumption above is right, here's your file with code added.

Rota Template - Copy.xlsm

It uses the Worksheet_Calculation event of an added sheet that's not to be used for anything else. It can be hidden.

In your existing but unused standard Module1 --> declared a public variable
In the Workbook Module --> use the Workbook_Open event to assign value to the variable
In the worksheet Module of the added sheet --> use the Worksheet_Calculate event to call whatever

To test, increase the value in AF21.
The routine will only fire once, the first time AG26 exceeds 60000, not every time it changes while still exceeding 60000.
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Automated Email

Postby NoSparks » Mon Mar 21, 2016 8:58 am

I don't have or use Outlook, so I can't test the email part.

Try replacing the code on the sheet module with this.
I used the email address from the code of your first question.
Change .Display to .Send to actually send the email.

Code: Select all
Private Sub Worksheet_Calculate()

  If Range("A1").Value >= 60000 Then
    MsgBox "The value is 60000 or more"      '<-- comment out if not wanted
    Call SendTheEmail
  End If
 
End Sub

Private Sub SendTheEmail()
' see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "The whatever it is has gone over 60000"

    On Error Resume Next
    With OutMail
        .To = "tarkeshwari.Sengupta@in.fujitsu.com"
        .CC = ""
        .BCC = ""
        .Subject = "60000 or more"
        .Body = strbody
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Automated Email

Postby NoSparks » Mon Apr 11, 2016 6:49 am

Did you change .display to .send as per the comment in the code ?
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 32 guests