Problem with Pathfile opening on any desktop/user using VBA

[Neilster]

Hi Guy's I'm really struggling with this one, My Outllook Template is saved into 'Documents folder' when Outlook is running everything is fine when I click the commoand button to open the email 'Template' on my form in Access.

Now if Outlook is closed and I click the button I get an error message saying that the that Access can't open the 'Template' it's possible thet file maybe open ect.

I need this to open on any desktop/user.

I have asked before and have done endless searches to try and sovle the issue but to no avial and it's paramount that I get the issue solved for the company. the code belowe as follows. I really hope you can help (And thanks to all those that have tried previously).

The code in red is the problem when Outlook is not running. :banghead::banghead::banghead:

Dim myOlApp As Outlook.Application
Dim myitem As Outlook.MailItem
Dim n As Integer
Dim sUsername As String
Dim filesPath As String


Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItemFromTemplate("Template.msg")

sUsername = Environ$("username")
filesPath = Environ$("USERPROFILE")

With myitem

For n = 0 To Me.EmailList.ListCount - 1
.Attachments.Add (Me.EmailList.ItemData)
Next n

myitem.Subject = Nz("")
myitem.To = Nz(Me.txtCustomerEmailAddress1)
myitem.Display

End With

 

[Minty]

You need to check if outlook is running and if not get the user to open it?
Use this function to check

Public Function IsOutlookOpen() As Boolean
Dim oOutLook As Object

On Error Resume Next
    
    Set oOutLook = GetObject(, "Outlook.application")
    
    If oOutLook Is Nothing Then
        IsOutlookOpen = False
    Else
        IsOutlookOpen = True
    End If
    
End Function

Then If the answer to this is False you have a couple of options;
1) Open a message box that says "Outlook need to be open, please either open it and press ok, or press cancel to abort the operation"

2)Force outlook open

 

[Neilster]

Thanks Minty, sorry do I put that in a module and call it?

Also it would be good to just force it it open. (-:

 

[Minty]

Create a new module and put that code in. Save the module as something like PublicFunc .

Then in your code - the simplest route (Obviously you can get a lot more sophisticated with this);

Dim myOlApp As Outlook.Application
Dim myitem As Outlook.MailItem
Dim n As Integer
Dim sUsername As String
Dim filesPath As String

If Not IsOutlookOpen() Then 

Msgbox "Outlook needs opening - open it now please , then close this message box" 

End If

...Rest of your outlook code here
Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItemFromTemplate("Template.msg")

sUsername = Environ$("username")

 

[Neilster]

Nice one Minty! Is there anyway of forcing it open without the message box???

Other than that solves most of the issue. Thank very very much. (-:

 

[Minty]

Forcing it open is not so straight forward - Outlook is a bit "different" than most applications.
This link explains more http://www.rondebruin.nl/win/s1/outlook/openclose.htm

As you can see due to how outlook works, and different versions of ms office, there isn't any simple way.

 

[Neilster]

I'll have a look, howevr I've put an error handler on the message box so that now it promps the user just to open Outllook, if they click OK - before it went to an error message for debugging. Now it just runs the error handler and all is good! just making it user friendly as users will ultamitly just click OK without opening Outlook first.

Other than that thank you so much for all your help Minty. (-: