Outlook App call issue

[txgeekgirl]

I have a DB that is hosted on my new blade. I had to change the way the DB sends auto emails because we put a 2003 Ent O/S on it with MO2003. On the old server we were running MO2000 to get around the security with auto emails.

I have some code that takes care of it in the new version but I cannot get the call code cleaned up enough to work.

BTW - we use this in-house for communications between referrals, svc coords, drs, and nurses. Nothing leaves the network.

If you all see anything - I would love to know what the issue is.

'This is the procedure that calls the exposed Outlook VBA function...
Sub SendMessage(StrTo As String, strCC As String, strSubject As String, strMessageBody As String, Optional strAttachmentPaths As String)
    Dim objOutlook As Object ' Note: Must be late-binding.
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
    Dim strDraft As String
    
        
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
MsgBox blnNewInstance
    If objOutlook Is Nothing Then
       'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True
MsgBox blnNewInstance
        'We need to instantiate the Visual Basic environment... (messy)
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
        objExplorer.CommandBars.FindControl(, 1695).Execute
        
        objOutlook.Application.Visible = True
        
        objExplorer.Close
                
        Set objNameSpace = Nothing
        Set objExplorer = Nothing
   
    End If
MsgBox blnNewInstance
 
    strDraft = "N"
  
    MsgBox StrTo
    MsgBox strCC
    MsgBox strSubject
    MsgBox strMessageBody
    blnSuccessful = objOutlook.FnSendMailSafe(StrTo, strCC, "", _
                    strSubject, strMessageBody, _
                    strDraft, strAttachmentPaths)
    
    If blnNewInstance = True Then objOutlook.Quit
    Set objOutlook = Nothing
    
End Sub

 

[darbid]

HI ttxgeekgirl,

Here is not a solution but it might give you some things to look at.

I think that this IF clause should end like I have put here

 If objOutlook Is Nothing Then
       'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True
'MsgBox blnNewInstance

End If

THis does not work

objOutlook.Application.Visible = True

but I am not sure what you are trying to do. GENERALLY in outlook you are looking for .display for items or .activate for explorers etc.

Does this open the VBEditor? If yes why do you want that?

objExplorer.CommandBars.FindControl(, 1695).Execute

objOutlook.FnSendMailSafe

Are you trying to call a function here? for example this below?
http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-Without-Security-Warning

If yes then you need to have just this below

blnSuccessful = FnSendMailSafe(StrTo, strCC, "", _                    strSubject, strMessageBody, _
 strDraft, strAttachmentPaths)

 

[txgeekgirl]

Thank you for the thoughtful and insightful response.

I had only the following up to see if I could get it to do something:

objOutlook.Application.Visible = True

You are right in that I am using the code from EverythingAccess to get around the security issue with this DB/Outlook relationship. I did however, modify the Outlook Macro as such to include the variables I need to pass.

Public Function FnSendMailSafe(strTo As String, _
                                strCC As String, _
                                strBCC As String, _
                                strSubject As String, _
                                strMessageBody As String, _
                                strDraft As String, _
                                Optional strAttachments As String) As Boolean

I think you have hit something on the head though. The other DB I use this code for actually takes incoming emails and disburses them.

This one send emails originating from the DB, built by choices on a screen and assignment, as an alert to service providers that they need to review a patients file. So I am going to get rid of the extra mapi coding as maybe it isn't necessary.

Maybe I can get this working today and be done with it. Thanks for the help.

 

[txgeekgirl]

OK - I am getting an Object does not support this property or method error. I am putting my code in order of how it's called.

From Form - they are globally defined variables as str but for some reason don't know that so I forced them:

 SendMessage CStr(strEmail1), CStr(strEmail2), CStr(strEmailSubject), CStr(strEmailMsg)

To Module (I commented out extraneous):

'This is the procedure that calls the exposed Outlook VBA function...
Sub SendMessage(StrTo As String, strCC As String, strSubject As String, strMessageBody As String)
    Dim objOutlook As Object ' Note: Must be late-binding.
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
 
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
 
    If objOutlook Is Nothing Then
       'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True   
    End If
MsgBox blnNewInstance
 
   [COLOR=red]blnSuccessful = objOutlook.FnSendMailSafe(StrTo, strCC, "", strSubject, strMessageBody)[/COLOR]
MsgBox "Return"
    'If blnNewInstance = True Then objOutlook.Quit
    'Set objOutlook = Nothing
 
End Sub

I have highlighted in red where the error is occuring. If I put blnSuccessful=FnSendMailSafe.... it just bombs out. it has to have that objOutlook call to know to find the FnSendMailSafe Function in the Outlook Macros.

In Outlook:

Public Function FnSendMailSafe(strTo As String, _
                                strCC As String, _
                                strBCC As String, _
                                strSubject As String, _
                                strMessageBody As String, _
                                Optional strAttachments As String) As Boolean

 

[DJkarl]

2 things I noticed when using this method to send mails via outlook.

1) the function has to be in your ThisOutlookSession class.
2) declare the Application_Startup sub even if it's blank.

Sub Application_Startup()
    
End Sub

 

[waynephillips]

Also, check your Outlook code compiles OK (otherwise it wont expose the function). Also check your outlook security level is set correctly (low / medium).

Hope this helps

 

[txgeekgirl]

I have the Application_Startup() Sub at the top of the script.

My security is set to low.

It has to do with the passing of the Variables from Access to Outlook. With my other program, it is working so slick - just as intended.

Like I said - the difference is that the other DB looks at Outlook for incoming mail and routes it by subject line by parsing the char string and pulling out the type of report and who it needs to go to.

This one is taking information entered into an Access DB, creating the variables based on assignment and then needing to send an email.

The call is exactly where it's bombing. It acts like it isn't stuffing values in correctly or getting them into outlook.

 

[waynephillips]

Furthermore, I see that you've removed this code:

'We need to instantiate the Visual Basic environment... (messy)
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
objExplorer.CommandBars.FindControl(, 1695).Execute

objExplorer.Close

Set objNameSpace = Nothing
Set objExplorer = Nothing

It's needed (when you start a new Outlook instance in code) to ensure that the VBA environment has been initialized properly. If you remove that code, you risk the function not being accessible via COM.

 

[txgeekgirl]

I thought DarBid said to get rid of it. I am putting it back. It's just commented out.

 

[darbid]

I thought DarBid said to get rid of it. I am putting it back. It's just commented out.

Ok do not listen to me, I am sorry, I thought this (FnSendMailSafe) was some standard function and did not realise that it is part of Outlook and attempts to initiate a safe outlook object.

 

[DJkarl]

Out of curiosity havae you tried calling the function without trying to get the return value?

[COLOR=black]Call objOutlook.FnSendMailSafe(StrTo, strCC, "", strSubject, strMessageBody)[/COLOR]

 

[waynephillips]

It looks like you've added a parameter called strDraft:

Public Function FnSendMailSafe(strTo As String, _
strCC As String, _
strBCC As String, _
strSubject As String, _
strMessageBody As String, _
strDraft As String, _
Optional strAttachments As String) As Boolean

But you're not passing that parameter:

blnSuccessful = objOutlook.FnSendMailSafe(StrTo, strCC, "", strSubject, strMessageBody)

You need to add that parameter to your call (or make it optional, as the strAttachments parameter is).

 

[txgeekgirl]

OK - Guys - we are getting so close.....

I got an email to go through but man was it unhappy getting there.

I had stripped out the Draft and Attachment as this code doesn't really need it whereas the other one has to have both. I will have to add attachment back here.

Darbid - I wasn't dissing you. Please - you helped me look at it separately from the other DB and really analyze it for what it is.

I am taking out all msgbox's and running again.

 

[txgeekgirl]

Hot diggity! It works. Before I post the correct code, as I like to do that for future people who use something like this... I do NOT want my EU's to see the VBA script from Outlook. It is opening for 2-3 seconds. They would screw something up. I think it needs to briefly run though to work. Is there a way to hide it?