change a1 cell in xls file from within access

[lazynewt]

This is a bit of a mixed request.

I have a button that needs pressing around 3-5pm every day. iF this button is not pressed by 5pm i want to send an email to 3 addresses as a warning "these can then be forwarded to sms's for a realtime alert. My idea so far is

1. if the button is clicked it alters cell a1 in alert.xls workbook from "1" to
"0".

2. then each day windows schedule runs the excel doc at 16:55. a macro auto runs inside the xls doc that checks cell a1 and sends an email (if the cell = "1" ) if it equals 0 then it does nothing.

3. then each day at 11am a windows schedule runs and sets the cell back to 0

i have the code for sending the email at a set time. I am just unsure how i can make the button in the access db to open the xls doc change a1 to 1 then close it again.

Ill post all the code for the ontime send email shortly. And i realise this is a strange way of doing this but im very open to suggestions.

thanks in advance.

 

[dmonney]

Try This

dim sfile as string
sfile = "(location Dir)/alert.xls"
Set xlapp = CreateObject("Excel.Application")
    xlapp.Application.Visible = False
    Set xlwkb = xlapp.Workbooks.Open(sfile)
xlapp.ActiveSheet.Range("A1").FormulaR1C1 = 1
 xlapp.Quit
 Set xlapp = Nothing
Set xlwkb = Nothing

 

[lazynewt]

Thanks dmonney i can see how that will work. Ill test it asap.

on a side note this is the code im "trying to use to check cell a1 and ontime send an email. Im getting an error at

Set rng1 = Worksheets("test2").Range("a1")

code is

Sub sendemail()

Application.OnTime TimeValue("09:09:00"), "MyMacro"




Dim rng1 As Range
Dim email, ref, origin, destination, notes As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set rng1 = Worksheets("test2").Range("a1")
If LCase(rng1.Value) = "1" Then Close

Else



'**gathers information from your form.  this sets the string variable to your fields
email = "lazynewt@gmail.com"
ref = "reference"
origin = "wagedayay"
destination = "lazynewt@gmail.com"
notes = "the direct debit instruction has not been sent today"

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
    .To = email
    .Subject = ref & " " & origin & " " & destination
    .Body = notes
    .Send
End With

'**closes outlook
Set objEmail = Nothing
objOutlook.Quit

End Sub

Is there a better way to check cell a1?

thanks in advance.

 

[lazynewt]

ok i think im getting somewhere. This revised code will close the book if cell a1 = 1 if it does not equal 1 then it will send an email. Just need to check the ontime event and add the code you gave me dmonney. Also I will need to bypass that annoying warning outlook gives when sending an email automatically. Any further ideas/changes are much appreciated guys.
Heres the code.

Sub sendemail()

Application.OnTime TimeValue("16:07:00"), "MyMacro"


Dim email, ref, origin, destination, notes As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

If [A1].Value = "1" Then
   With Application
        .ScreenUpdating = False
         
         '       Loop through the workbooks collection
        For Each Wkb In Workbooks
             
            With Wkb
                 
                 '               if the book is read-only
                 '               don't save but close
                If Not Wkb.ReadOnly Then
                     
                    .Save
                     
                End If
                 
                 '               We save this workbook, but we don't close it
                 '               because we will quit Excel at the end,
                 '               Closing here leaves the app running, but no books
                If .Name <> ThisWorkbook.Name Then
                     
                    .Close
                     
                End If
                 
            End With
             
        Next Wkb
         
         
        .ScreenUpdating = True
        .Quit 'Quit Excel
    End With

Else

'**gathers information from your form.  this sets the string variable to your fields
email = "lazynewt@gmail.com"
ref = "reference"
origin = "wagedayay"
destination = "lazynewt@gmail.com"
notes = "the direct debit instruction has not been sent today"

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
    .To = email
    .Subject = ref & " " & origin & " " & destination
    .Body = notes
    .Send
End With

'**closes outlook
Set objEmail = Nothing
objOutlook.Quit

End If
End Sub

===========edit==========

I found this code for stopping the security poppup.

OlSecurityManager.ConnectTo OutlookApp 
OlSecurityManager.DisableOOMWarnings = True 
On Error Goto Finally 
  '... any action with protected objects ... 
Finally: 
  OlSecurityManager.DisableOOMWarnings = False

or

Dim SecurityManager As New AddinExpress.Outlook.SecurityManager 
SecurityManager.ConnectTo(outlookApp) 
SecurityManager.DisableOOMWarnings = True 
Try 
  '... any action with protected objects such as contacts or items... 
Finally 
  ' In any case please remember  
  ' to turn on Outlook Security  
  ' after your code, since now  
  ' it is very easy to switch it off! :-)  
  SecurityManager.DisableOOMWarnings = False 
End Try

How would i implement this into the above code? thanks in advance.

===edit again====

also another problem - i will need to change cell a1 back to "2" before the above code closes the workbook........ (fixed)
I am using this code to change it back to a 2 on close.

     Range("A1") = 1
     ThisWorkbook.Save 'optional

p.s this thread has drifted somewhat but i didnt want to start a new one. (
(please can a mod either split post or amend title as this might help someone else)

poss title change ->>>> how to send email if cell value equals x else reset and close workbook

 

[dmonney]

When your setting range one you have to be as specific as possible otherwise bad things happen Try this

Set rng1 = [COLOR=red]xlapp.[/COLOR]Worksheets("test2").Range("A1")

 

[dmonney]

I have tried six ways from sunday to disable outlook security. My view is it either can't be done or it opens up my computer to potential problems in the future.
instead of this

With objEmail
    .To = email
    .Subject = ref & " " & origin & " " & destination
    .Body = notes
    .Send
End With

With objEmail
    .To = email
    .Subject = ref & " " & origin & " " & destination
    .Body = notes
[COLOR=red]    .Display[/COLOR]
End With

This will open up the messege and the user will have to click send.

 

[lazynewt]

Thanks dmonney. I agree getting rid of the popup whilst maintaining security is a bit challenging. After some searching i found that mapilab have a free add on for outlook that fixes this issue. Heres the link incase your looking in future.

http://www.mapilab.com/outlook/security/

thanks for the code updates. I really need to read up on
xlapp
aswell as alot of ms access code... still just getting used to it.

thanks again dmonney.

 

[georgedwilkinson]

I really need to read up on
xlapp
aswell as alot of ms access code... still just getting used to it.

Be cautious...xlapp is a variable name, not a feature or class of Access. You're not likely to get much good information looking it up.

A couple of keywords that might yield good results would be: Excel, OLE, automation, VB, VBA, Excel.Application.