On Error VBA Outlook

[Neilster]

Hi Guy's

I have this code working however only if Outlook is open. I know that I need to create a On Error Handler and some VBA to open Outlook when it's not running.
Can anyone please help or point me in the right direction. Many thanks

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

sUsername = Environ$("username")
Dim filesPath As String
Set myOlApp = CreateObject("Outlook.Application")
filesPath = Environ$("USERPROFILE")
Set myitem = myOlApp.CreateItemFromTemplate("Template.msg")

With myitem

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

.Display

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

 

[Gasman]

I copied some code from the net.



In your email code segment.

    ' See if Outlook is open, otherwise open it
    If fIsOutlookRunning = False Then
        Call OpenOutlook
        DoEvents
    End If

    ' Create the Outlook session.
    Set objOutlook = GetObject(, "Outlook.Application")

And then two other pieces of code

Public Function fIsOutlookRunning() As Boolean
Dim W As Object
Dim processes As Object
Dim process As Object

fIsOutlookRunning = False

Set W = GetObject("winmgmts:")
Set processes = W.execquery("SELECT * FROM win32_process")

For Each process In processes
    If process.Name = "OUTLOOK.EXE" Then
        
        fIsOutlookRunning = True
        Exit For
        
    End If
Next

Set W = Nothing
Set processes = Nothing
Set process = Nothing

End Function

Public Sub OpenOutlook()

    'Depending on your version of Access, Access.hWndAccessApp could also be Application.hWnd
    If ShellExecute(Access.hWndAccessApp, vbNullString, "Outlook", vbNullString, "C:\", 1) < 33 Then
        MsgBox "Outlook not found."
    End If
End Sub

HTH

 

[Neilster]

Nice Gasman, Nice! thanks for your reply (-:

 

[Neilster]

but now i get a 'compiler error' (expected sub, functon, or property) on the line

Call OpenOutlook

 

[Neilster]

Right sorted that out, now i get 'compile error' sub or funtion not defined on line

If ShellExecute

 

[Gasman]

Probably a reference problem, but I am at work now, so cannot check mine. Have a google.

 

[gemma-the-husky]

I presume your outlook issue is happening at .display line

Can you put a error handler there? Although it may not produce a rte, and may not do anything?

if you have .save instead of .display it will probably put the e-mail into drafts, even if outlook is not open. Try that.

 

[Neilster]

Thanks Gemma-the-husky, I have tried that but just comes up with a 'Run time error' Temple.msg might already be open, this is even when Outlook isn't running?

 

[Neilster]

It's because of this line of code -

Set myitem = myOlApp.CreateItemFromTemplate("Template.msg")

 

[Gasman]

It's because of this line of code -

provide the FULLPATH to the template?, but that would indicate that your code was not working initially?

 

[Neilster]

If Outlook is ruuning then it will open the template, if acces isn't running it get run time error '-2147287030(80030002)':

'We can't opent the 'Template.msg'. It'spossible the file is already open, or you don't have permission to open it.

To check your permissions, right-click the file folder, then click properties.'

Not sure why this is happening ??

 

[Gasman]

I will check my Create from Template line of code tonight, but I believe I used the full path, as you have to for attachments etc.?

 

[Neilster]

I'm pretty sure I've the correct path, the code you gave ealier dosen't work for some reason, i'll wait for your reply. Thanks very much.

 

[gemma-the-husky]

you haven't entered a path. What gasman is suggesting, is that you need to specify the full path, eg something like

"c:\mytemplates\template.msg"

 

[Neilster]

Now i get compile error 'sub or function not defind' on this line. shellexecute bit

If ShellExecute(Access.hWndAccessApp, vbNullString, "Outlook", vbNullString, "Template.msg", 1) < 33 Then
MsgBox "Outlook not found."
End If

 

[Gasman]

I have

    ' Get appdata path
    strAppdata = Environ("Appdata")
    

' Set paths
    strTemplatePath = strAppdata & "\Microsoft\Templates"
    strSigPath = strAppdata & "\Microsoft\Signatures\Ssafa.htm"

and I use it here

        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")

A pic of my references is attached.

I must admit most times when I have not used a path to files and have received an error. I *think* it might work if the file is in the same folder as Access is run from (unlikely in my case) so then I always specify paths, and for ease of use/maintenance use variables to hold the values.

 

[Gasman]

You could try this method?

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

or

http://www.ozgrid.com/forum/showthread.php?t=73886

as I cannot remember where P picked up the code from and cannot find it again easily at least.

 

[Neilster]

Ive tried that link before and it open and mininsese out striaght away and the gives me the same error message as metioned before about it being open already.

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

Call MyMacroThatUseOutlook

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

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


sUsername = Environ$("username")

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

 

[Neilster]

Like I said when Outlook is running it finds the path no problem. so I can't work it out.

 

[Gasman]

Like I said when Outlook is running it finds the path no problem. so I can't work it out.

I would not waste time tying to work it out, as in this case as outlook can be not running you have to cater for that situation, so see if the full path helps and check your references against mine.

As my sig states, I am just a newbie at Access, so I generally just use what works. It might not be the most efficient sometimes, but it gets the job done and then if I see a better way later, I take note for the future.