VBA to open Outlook

[fat controller]

I have a multi-user database, and some of the users are at the more senior end of the age spectrum

There are a few functions within the database that result in an email being sent, however many of the more senior staff often forget to open Outlook, and of course the email sits in the outbox until they open Outlook five weeks later.......

All users have to open the database as a matter of course during the day, so I was wondering if I could have an On Open event on the dashboard form that also opens Outlook? (if it also minimised it to the taskbar, that would be the icing on the bun! )

 

[fat controller]

Cracked it!

 Dim olApp As Object     Dim objNS As Object     Dim olFolder As Object      Set olApp = CreateObject("Outlook.Application")     Set objNS = olApp.GetNamespace("MAPI")     Set olFolder = objNS.GetDefaultFolder(olFolderInbox)      With olFolder
    .Display
OlApp.ActiveExplorer.WindowState = 1 'olMinimized 
End With
End Sub

That should hopefully sort my problem

 

[Gasman]

Nice one FC.

You might want to check if Outlook is currently open, otherwise you might confuse those who do remember to open Outlook each morning.

Also if they start the app for any reason again, it will open another instance of Outlook.

http://www.rondebruin.nl/win/s1/outlook/openclose.htm

FWIW I always had my Outlook as the first app opened, and did that via my startup.
I had so many windows opened that if I did not have them in a set order, it would slow me down. If one crashed it was better for me to shut everything down and start again, however I had a vbs script that ran each day, so not as slow as it sounds.

BTW I am at that end of the spectrum. ROFL

 

[fat controller]

I should have made sure to come back and add to that last night - I discovered that, I had got it wrong last night when I ended up with six instances of Outlook all minimised to the taskbar Doh!

Cured by:

Dim olApp As Object     
Dim objNS As Object     
Dim olFolder As Object      
Dim oOutlook As Object

Set oOutlook = GetObject(, "Outlook.Application")

If oOutlook Is Nothing Then

Set olApp = CreateObject("Outlook.Application")     
Set objNS = olApp.GetNamespace("MAPI")     
Set olFolder = objNS.GetDefaultFolder(olFolderInbox)      

With olFolder     .Display 
OlApp.ActiveExplorer.WindowState = 1 'olMinimized  
End With
Exit Sub
Else
Exit Sub
End If
End Sub

 

[Gasman]

Why not use the same object variable for getting Outllok as creating it.?

Also it appears to be good practice for objects at least, to set to Nothing when you exit the module, as Access sometimes does not clean up after itself.

Set olApp = Nothing
Set objNS = Nothing
Set olFolder = Nothing

BTW it's the weekend, why are you still working. ;-)

 

[fat controller]

Thanks

I will add that now, and (for the time being at least) that is my project complete.

I am really chuffed with the number of functions that the database is handling on a day and daily basis, especially given that I have had the grand total of nil in respect of formal training in Access (or any other software for that matter). What pleases me more than anything is the fact that almost without exception, it has made life easier for the staff in each department that it has been developed for, and the staff that use it seem to genuinely like it.

More pleasing still is that our staff group consists of people from their twenties to their seventies, and all are using it without difficulty.

I haven't really been working as such this weekend - more making the final refinements to something that I have spent the last 18 months developing.

Oh, and to answer your question, I thought I needed a separate variable - no real reason why.

 

[Gasman]

I know where you are coming from.

Before I changed sites I created a database for colleagues to use (myself included) and everyone thought it was a better way of working, rather than separate Excel sheets.

This was far removed from my proper job.

It got pulled before I left, as it was not an 'official' bank program, but I've found out since that it has passed governance and they will be using it again.

 

[fat controller]

I know where you are coming from.

Before I changed sites I created a database for colleagues to use (myself included) and everyone thought it was a better way of working, rather than separate Excel sheets.

This was far removed from my proper job.

It got pulled before I left, as it was not an 'official' bank program, but I've found out since that it has passed governance and they will be using it again.

I know that one - the only people that don't like or appreciate my database is the directors --- who never actually use the thing. The usual comments such as "that isn't in his remit" etc etc/

Funny, when I retorted the other day "Fine, stop using it now then - I will happily disable it, and we can go back to paper and pen" - - there was a sudden change of attitude....

 

[fat controller]

OK - hit a bit of a wall with this one today; rolled out the new front end to everyone, and it became immediately apparent that Windows XP machines would not run the database, with the error pointing to a missing reference related to Outlook.

In short, I took out the Outlook bits and bobs, and removed the reference to MS Outlook 14.0 Object Library and it now runs on XP machines - - however, it now doesn't have the functionality that was intended.

Now, I know XP is no longer supported, and I know that really we should have new PC's - but, to say our company is 'thrifty' is putting it mildly.

Is there any way to add this library to XP machines, or better still to somehow load it into the database (even if that means having a DLL file saved somewhere in alongside the back end?

 

[Gasman]

Your code works for me, but have Outlook 11 Object Library as I only have Office 2003.

Outlook 14 is 2010 I believe.?

Never done anything like this, but 'late binding' might be the way?

http://stackoverflow.com/questions/15958722/microsoft-outlook-object-library-in-access-vba

Hopefully one of the experts can jump in?. Gina?, where are you.

Edit: I *think* you are already using late binding.

http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm

Try taking out the reference to Outlook 14 and running on your PC to test and then one of the errant ones.?

 

[Gasman]

Another way might be to...

Look to see what the file is on you PC. You get that info from highlighting the reference.
Copy that to somewhere everyone can access.
On start of database, test to see if the file is there, if not use the FileCopy function to copy to the correct location?

Might not be the ideal/expert way, but would be a workaround perhaps.?

You have got me interested again.

 

[fat controller]

OK - there are more strange happenings, that I suspect are all related, and this all appears to be a reference library issue.

It seems that the Windows 7 machines are happy because they are running Office 2010, with which the 'Microsoft Office 14.0 Outlook Object Library' is compatible - I believe it is 11.0 (or maybe 12.0 that is compatible with Office 2003); what is now making matters worse, is that the database also uses Microsoft Office 14.0 Access Database engine Object Library and Microsoft Office 15.0 Object Library and Microsoft Access 14.0 Object Library

In short, all of these are much more up to date than the XP machines are expecting or able to deal with, and when the database opens it cannot find the relevant library files it needs to be able to run.

Now, disabling the code to automatically start Outlook etc has got things running on two of the XP machines, but one in particular cannot handle even the simplest parts of the code (and I do mean simple, such as If statements or even DCount statements)

On my Windows 7 and Windows 8 machines, I can see no way to reference the earlier libraries - it looks as though they have (understandably) been scrapped in favour of the newer versions. Even if I could find them, would they work alongside the newer versions?

All of the machines (including the XP ones) use the database in the runtime environment, so I do not have a way to reference the libraries from those machines, and of course to be able to open the master front end (accdb file) would need at least Office 2007 to be on one of those machines, which would negate the problem...

Now, I have made the point today that it is beyond ridiculous that we are still running machines of this age, and with XP which is essentially unsupported by MS nowadays, however we are not going to get replacement machines overnight.....

so, ladies and gents - is there a workaround?

 

[Gasman]

Way out of my league.

I've played around with my setup and have problems with the GetObject if my Outlook 11 library is not ticked.

This link http://stackoverflow.com/questions/19184650/late-binding-to-open-outlook-from-access states 'Outlook is the only MS Office application where GetObject does the same thing as CreateObject. Unlike other MS Office application, CreateObject doesn't create multiple instances of Outlook.'. Now only one Outllook.exe is running in Task manager, but I still get a window of Outlook available for each run of the code?, which is what you were trying to avoid.

FWIW this is my code that works without the reference to the Library

Const olFolderInbox As Long = 6

Sub OpenOutlook()
Dim olApp As Object
Dim objNS As Object
Dim olFolder As Object
'Dim olFolderInbox As Object
'On Error Resume Next
'    Set olApp = GetObject(, "Outlook.Application")
'    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
        Set objNS = olApp.GetNamespace("MAPI")
        Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
        With olFolder
        .Display
        olApp.ActiveExplorer.WindowState = 1 'olMinimized
        End With
'    End If
Set olApp = Nothing
Set objNS = Nothing
Set olFolder = Nothing

End Sub

Which lead me to thinking...if I could kill all instances of Outlook, then only the one I open would exist?. Not ideal perhaps but from http://bytes.com/topic/access/answers/493331-close-all-instances-microsoft-outlook-vba I found

Dim objs as Object
Dim obj as Object
Dim strSQL as String
Dim strWMI as String
strWMI = "winmgmts:"
strSQL = "Select * From Win32_Process "
strSQL = strSQL & "where Name = 'OUTLOOK.EXE'"
Set objs = GetObject(strWMI).ExecQuery(strSQL)
For Each obj In objs
obj.Terminate
Next
Set objs = Nothing

which if not useful now, might come in handy for the future.
I used something similar in vbscript to kiil all instances of IE before opening my windows for the day.

I'm looking forward to seeing how the experts would handle this.

 

[fat controller]

Aye, t'is a bit of a difficult one this; if it were only the Outlook problem, I could almost understand it, but its the fact that even the basic stuff is throwing me a wobbler

If I new which library files, I suppose I could use filecopy to copy them to the System 32 folder on the user's PC, but that could be scuppered by permissions issues as out machines are (rightly) set up to prevent users faffing about....

I am an admin on most, if not all of the machines, so I could do it manually I suppose - but I would need to know what files to copy, and would also be concerned that I would make the database work and scupper their Office programs.

Oh the other hand, I am VERY reluctant to start pulling the database to bits as it is actually working beautifully at the moment, on all but the 'odd' XP machines

 

[Gasman]

Let's hope a few experts pop in then.

 

[Gasman]

Might be worth starting a new post re the reference issue?

 

[fat controller]

Yeah, I think I will. Had a bit of an idea last night (you know, the dropping of to sleep moment!), but not had a chance to try it yet. I'll post back if it works

 

[Meltdown]

Does your company have a web site?

Have you considered using the mail server of your web host to send the emails and bypass all the Outlook shenanigans.

You would have to register a dll on each machine.

Regards
Melt

 

[fat controller]

OK - an update (sorry for the delay)

The problems proved to be related to the missing MS Office 14.0 Objects Library on the XP machines, so rather than faff about any more I simply modified a copy of the front end, removing the reference to that library and commenting out the associated VBA, and it now runs fine. Noises have been made across the business for the XP machines to be replaced anyway, so this should (hopefully) be a short term issue.

However........

I now have a new problem, and it is again related to the Outlook check/open process; if a user opens the db and it then opens Outlook, the users Inbox will not update. The status shows as being connected to MS Exchange, but that is about it. Using 'Send/Receive also does not update the folder. The only way to get Outlook to update the folder is to select 'Update Folder' from the Send/Receive tab on the ribbon in Outlook. When in this state, emails send just fine.

If the user opens Outlook before opening the db, the problem doesn't exist?

I have got the three lines setting the objects to nothing before exiting the module, but they seem to make no difference (I have tried it with them commented out)

 

[Gasman]

I think you'd better start a new thread for this problem. This is out of my league, but logic says if you are not making any reference to outlook, other than that you had before and not including the errant object library, then it should work as before? Have you kept previous versions?

I don't know what your IT dept is like, but it should be easy enough to start outlook or put outlook shortcut in the startup folder of each user via a logon script.?

Good luck anyway.