Sending An Email

Can someone please tell me why this won't work?
I have a spreadsheet that feeds from an Access database.
It refreshes every minute.
When certain conditions are met, cell G12 changes from "NO" to "YES".
I don't understand why the following code won't work.
The cell CHANGES from NO to YES, but it doesn't trigger the email to be sent.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G12").Value = "YES" Then
ActiveWorkbook.Send
Recipients = "gretafree42@hotmail.com"
Subject = "NOC AGING" & Format(Date, "dd/mm/yy")
End If

End Sub

Thank you in advance for any help you can give me.
Gee
0
Utf
2/19/2010 8:08:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
448 Views

Similar Articles

[PageSpeed] 51

It keeps getting stuck on "ActiveWorkbook.Send"

"Gee" wrote:

> Can someone please tell me why this won't work?
> I have a spreadsheet that feeds from an Access database.
> It refreshes every minute.
> When certain conditions are met, cell G12 changes from "NO" to "YES".
> I don't understand why the following code won't work.
> The cell CHANGES from NO to YES, but it doesn't trigger the email to be sent.
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Range("G12").Value = "YES" Then
> ActiveWorkbook.Send
> Recipients = "gretafree42@hotmail.com"
> Subject = "NOC AGING" & Format(Date, "dd/mm/yy")
> End If
> 
> End Sub
> 
> Thank you in advance for any help you can give me.
> Gee
0
Utf
2/19/2010 8:21:01 PM
I figured it out!

Private Sub Worksheet_Calculate()
If Range("G12").Value = "NO" Then
Range("A2").Select
End If
If Range("G12").Value = "YES" Then
Range("A1").Select
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("G12").Value = "YES" Then
ThisWorkbook.SendMail Recipients:="recepient@net.com", Subject:="AGING"
End If
End Sub


"Gee" wrote:

> It keeps getting stuck on "ActiveWorkbook.Send"
> 
> "Gee" wrote:
> 
> > Can someone please tell me why this won't work?
> > I have a spreadsheet that feeds from an Access database.
> > It refreshes every minute.
> > When certain conditions are met, cell G12 changes from "NO" to "YES".
> > I don't understand why the following code won't work.
> > The cell CHANGES from NO to YES, but it doesn't trigger the email to be sent.
> > 
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Range("G12").Value = "YES" Then
> > ActiveWorkbook.Send
> > Recipients = "gretafree42@hotmail.com"
> > Subject = "NOC AGING" & Format(Date, "dd/mm/yy")
> > End If
> > 
> > End Sub
> > 
> > Thank you in advance for any help you can give me.
> > Gee
0
Utf
2/19/2010 10:16:01 PM
Pay attention to the fact that the event will be triggered every time ANY 
cell is changed.
this means that even if cell A1 will be changed, the macro will check for 
the value in cell G12.

you may want to change the condition from "If Range("G12").Value..." to "If 
Target.Address = "$G$12"..."

this will trigger the event only if the specific cell (G12) will change.

> Can someone please tell me why this won't work?
> I have a spreadsheet that feeds from an Access database.
> It refreshes every minute.
> When certain conditions are met, cell G12 changes from "NO" to "YES".
> I don't understand why the following code won't work.
> The cell CHANGES from NO to YES, but it doesn't trigger the email to
> be sent.
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Range("G12").Value = "YES" Then
> ActiveWorkbook.Send
> Recipients = "gretafree42@hotmail.com"
> Subject = "NOC AGING" & Format(Date, "dd/mm/yy")
> End If
> End Sub
> 
> Thank you in advance for any help you can give me. Gee
> 


0
Raz
2/20/2010 10:26:59 AM
Pay attention to the fact that the event will be triggered every time ANY 
cell is changed.
this means that even if cell A1 will be changed, the macro will check for 
the value in cell G12.

you may want to change the condition from "If Range("G12").Value..." to "If 
Target.Address = "$G$12"..."

this will trigger the event only if the specific cell (G12) will change.

> Can someone please tell me why this won't work?
> I have a spreadsheet that feeds from an Access database.
> It refreshes every minute.
> When certain conditions are met, cell G12 changes from "NO" to "YES".
> I don't understand why the following code won't work.
> The cell CHANGES from NO to YES, but it doesn't trigger the email to
> be sent.
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Range("G12").Value = "YES" Then
> ActiveWorkbook.Send
> Recipients = "gretafree42@hotmail.com"
> Subject = "NOC AGING" & Format(Date, "dd/mm/yy")
> End If
> End Sub
> 
> Thank you in advance for any help you can give me. Gee
> 


0
Raz
2/20/2010 10:27:05 AM
Reply:

Similar Artilces:

Can't Send Messages Until I Press F9 Key
This is a multi-part message in MIME format. --------------030800050502050601040701 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit After I compose a message and click Send, the message does go its way until I press F9 or choose Tools|Send/Receive. I'm using Outlook 2003 on Windows XP. In case it matters, there's a toolbar for ZoneAlrm Outlook Addin with Junk filters (I use the Zone lite firewall freebie). Is there a setting in Outlook I need to set to automatically send messages? Should I disable the Zone addin? If so, how? Thanks. ---...

Backing Up Outlook Express Emails
Does anyone know how to back up email's in Outlook Express? Backup your dbx-files For more assistance ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://communities.microsoft.com/newsgroups/default.asp?icp=InternetExplorer Good Luck! -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Cindy" <anonymous@discussion...

Cannot move emails between folders
I am the Administrator on my home machine. If I try to move an email from the Inbox to another folder I am told 'I don't have permission, check the folder properties for permission' There is nothing in the folder properties about permissions. This has only recently started happening. XP Home and Outlook XP with all updates. ...

error sending from 5.5 to 2000
When a user sends from an exchange 5.5 mailbox to a user in exchange 2000, they receive the following error: The message was undeliverable because the recipient specified in the recipient postal address was not known at this address The MTS-ID of the original message is: c=US;a= ;p=Express-Scripts;l=TEMMS014-041001192533Z-210060 <mail5.express-scripts.com #5.1.1> The same user on the 5.5 server can send to other users on the 2000 server. I looked the error up in technet and got 3 kb articles with only 1, kb295377 being anything close to my problem. I checked the entr...

How do I send a welcome message to a new created mailbox automatically?
My exchnage send a message automatically but I need to change the message where do I change it? Thanks, Ron On Tue, 21 Jun 2005 16:35:53 -0700, "Ron" <rony@geotestinc.com> wrote: >My exchnage send a message automatically but I need to change the message >where do I change it? > >Thanks, >Ron > Exchange doesnt do that unless you have something scripted. If you are referring to the welcome message in Outlook, its called welcome.msg , but I dont think its been used since Outlook 2002. So where would I plug in the script? Also I searched for welcome.ms...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Sending a workbook as a mail attachment
Hey Folks, Is there an easy way to send a workbook via email based on the date an time? Our company uses excel for some of our reporting and rather then having the reps remember to send the workbook i'd like to have it send itself automatically - say on Saturdays or Sundays. Ideas? Nel post news:1165167749.298713.321880@l12g2000cwl.googlegroups.com *Gord* ha scritto: > Hey Folks, > > Is there an easy way to send a workbook via email based on the date an > time? Our company uses excel for some of our reporting and rather > then having the reps remember to send the wor...

iPhone Email to Entourage Using Exchange
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I answer an email on my iPhone, it does not register it in Entourage as answered. This function worked fine until the business I work for put everyone on Exchange. Is there a setting for this that I can change? <br><br>Thanks! ...

Send from Word
When I hit the send button in Word or Excell I get a pop up that say choose profile with Outlook in the drop down. That is the only name in the drop down. If I hit OK I get the following Message. The MAPI Spooler could not be started. Close and then restartall mail-enabled applications.MAPI 103 (0000004C7) If I choose to set up a new profile I get a Microsoft Exchange Setup Wizard. As I try to setup a new profile I come to the following, The following unrecoverable error has occured: The .DLL file for this service is either damaged or could not be found. Any ideas? Doug, What versi...

How to view the same email message at the different PC users
My husband & I use the same PC(Windows XP) but have an individual user accounts. We have several email addresses for individual usages and shared usages and all enable to receive and view by Outlook 2000 inbox. We have noticed that the emails came in while I'm on Outlook (or minimized at the bottom) can not view when my husband goes to his Window user and open the Outlook. And vise versa. We want to be able to look at all emails that came into the Outlook inbox at both Window user's Outlook inboxes. How can we do that? ...

Send 1 address to 2 mailboxes
I want to create an address and have any mail sent to that address delivered to two separate users' mailboxes. How can I do that. I've tried simply adding the address in the two users' "Email Addresses" tab in the User's properties of ADU&C. But it wouldn't allow me to add the same address to the second user ("This email address already exists in this organization"). I've also tried creating a new user for the address and forwarding the email to the 2 other users (Exchange General tab - Delivery Options button - Forward To field). But ...

Email to generate case
Hi, we've recently implemented CRM in out organization, we are a small IT support company, and as such we recieve support queries by email. I have set up CRM so that it puts inbound email in our support queue as email activities, is there any way to get CRM to automatically convert these email activities into new cases? Many thanks, Dan. c360 has an add-on that will do this (www.c360.com). -- Matt Wittemann http://icu-mscrm.blogspot.com "Dan Baird" wrote: > Hi, we've recently implemented CRM in out organization, we are a small IT > support company, and as suc...

Reminder for a received email for follow up
I would like to put a reminder on an email already received. This reminder would basically call my attention to the email (even though the email is in a different folder). Is there a way I can easily do this? I'm using Outlook 2003 SP1. Thanks John Off the top of my head, quick and easy, drag and drop a copy into your calendar. -- K. Orland Such as are your habitual thoughts, such also will be the character of your mind; for the soul is dyed by the thoughts - Marcus Aurelius "John" wrote: > I would like to put a reminder on an email already received. This remi...

makro to return email
I want a makro that if a check box is checked, a email is send to a recipient. See your other mail and http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Esrei" <anonymous@discussions.microsoft.com> wrote in message news:174801c50514$05025190$a401280a@phx.gbl... >I want a makro that if a check box is checked, a email is > send to a recipient. ...

Phantom emails
Why would outlook 2007 try to send emails about every 60 seconds when there is no emails to be sent in the outbox? Carolyn On Mon, 24 May 2010 08:45:01 -0700, Carolyn <Carolyn@discussions.microsoft.com> wrote: >Why would outlook 2007 try to send emails about every 60 seconds when there >is no emails to be sent in the outbox? >Carolyn What is the indication Outlook is trying to send e-mails? -- To reply via e-mail, remove The Obvious and .invalid from my e-mail address. Could it be a stuck read receipt? See http://www.howto-outlook.com/howto/deletereadrecei...

Exchange 2003: Missing emails
Hello, Strange problem, not sure if anyone else experiencing: We are running Exchange 2003 SP1 on Windows 2000 SP4. A manager came to me and asked where some files had gone in their folders. I checked through their mailbox, and sure enough the emails that had been filed away were missing. Checking through the backups, the email was last in the folder about 2 weeks ago. Does anyone know how i would audit what has happened here? i am not asking what has happened to these emails, but moreso who has accessed this mailbox, and when, in the last 2 weeks, thanks in advance John- Did your...

Unable to receive email from CRM to Outlook
Hi, I have configured the CRM router and am trying to send mail from CRM. I am able to send the mail without any error in CRM. When I click the send button the email form closes. However, I am not getting the mail in outlook. Can anyone provide any hints on what could be the problem... Thanks and Regards Amit Amit, Is it an internal mail? the CRM Exchange Router only works with external mail. Thanks Simon "AmitK" <amitkarnik78@gmail.com> wrote in message news:1129643395.576146.287270@g14g2000cwa.googlegroups.com... > Hi, > I have configured the CRM r...

Emails are automatically opening
When I select an email in outlook xp the emails are automatically opening the first highlighted email. Yes, ;) I have checked my settings in folder views - double click is selected, even if I reset it to single then back to double still no go. Also once I close a message, the next one automatically opens - just close not move or deletes. ...

Outlook 2003 not sending some messages
I'm using Windows XP Media Center, and Outlook 2003. Some messages don't leave the outbox. I don't get error messages, and the outgoing message doesn't appear in italics like it's waiting to get mailed. I can't find a similarity between messages that don't go out. Thank you for any help. Sarah ...

Email Tracking from Shared Outlook Inbox
We have our system setup so that some users have Full Access rights to other users inboxes. The problem comes when a user attempts to track an email from a mailbox other than their own. From an open message the user does not have the Track in CRM button, but if they attempt to track using the buttton available from the inbox, they get an error message stating that they cannot track from the Outlook store to the CRM store. Does anyone have any ideas on how to correct this? Thanks! You have to drag the email into the inbox of the CRM user before tracking it. -- Matt Wittemann, CRM ...

Problem in restricting users from sending/receving internet email
I have found a helpful articel here: http://www.msexchange.org/tutorials/MF009.html I read it through couple times but I would like to ask some questions before I start the work on our Exchange 2000 server. 1. The "group" I am going to create for adding users into the restricting list, will it be a Security group or Distribution group? What group scope should I give it? 2. Under the section of "Restricting Users from Sending Internet Base Email", the article tells us to create a *new* SMTP connector and add the group into the "Dilivery Restriction" page. Will ...

User accout shows new email
On start up a message shows in my user account that I have new email messages but am using Outlook 2003 and all incoming emails have been deleted. How do I clear the message ...

Default email addresses keep changing.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I upgraded to version 12.2.4 and every so often the default email addresses that I have chosen, switch to another email address such as the default was Home and is now Office. Even after I change them back, it can change again. <br> This is a problem because when I compose an email, I now have to be very careful which default address appears so that I send the email to the right place! <br> We have more than one Mac. I run Office Business; some use Student/Teacher. Some use S...

playing sounds in email #2
I wonder if anyone can please help me to know how to send an email which will automatically play a sound file when it is previewed or opened by the recipient I have received such emails occasionally, and I now have a serious reason (as opposed to a 'fun' one, which is what all these others were!) to want to do this. Thanks, Jeff ...