Outlook Automation from Access Question 2

[Faded]

Hi There!

I am creating an app in Access that hooks into Outlook for automatic messaging. I am having two problems.

1) Even though I have opened an instance of outlook programmitically, I get an error when the code executes unless I have Outlook open on my desktop. How can I make this work if I don't have Outlook running at the time?

2) When I call the .Send method, a msgbox pops up on my monitor saying "This application is trying to send a message on your behalf...Do you want to allow this?" Yes/No. How can I get around this? I want the message to send without the users confirmation/feedback.

Thanks in advance if anyone can help out.

Faded

 

[lxn]

Hi Faded
Um about the first one, what u can do is to write a method using GetObject to determine if the Outlook application is running already, if it is, maybe pop up a msgbox, if it's not, then have it open.
Probably something like this :

public sub <whatever>

Const cnstApp = &quot;outlook.application.8&quot;
Dim lbooRetry As Boolean
Dim App As Object

On Error GoTo usdOutlookIsOpen_Err

lbooRetry = False
Set App = GetObject(, cnstApp)
If lbooRetry = True Then
Call Shell(&quot;Outlook.exe&quot;, 1)
usdOutlookIsOpen = False
Else
usdOutlookIsOpen = True
End If
Exit Function

usdOutlookIsOpen_Err:

If Err.Number = 429 And lbooRetry = False Then
lbooRetry = True
Resume Next
Else
'Handle other errors
End If
end sub

As to ur second question, I don't know how to avoid it, because I do the same thing when I send emails, and I never got a msg like that, maybe it's set up that way in ur outlook property or something, no idea. sorry couldn't help on that.

-- Merlin

 

[Stickarm]

Sounds like you have the latest Outlook security Patch on your computer. We ran into the same problem. The patch does not allow you to send an email from another application without receiving a confirmation box for each email. We have not been able to come up with a solution that works everytime. We ended up on our end setting up a computer that we only allow outgoing email on, and did not install the patch. And you cannot just uninstall the Patch you would need to Completely uninstall and reinstall Office. Check out this link for more infor. regarding you second question.

http://office.microsoft.com/assistance/2000/Out2ksecFAQ.aspx

 

[Faded]

Thanks Merlin and Stickarm!

Merlin, I have used the code you showed, and it opens Outlook as it should, but when I try to create a mailItem, I get a run-time error saying &quot;Object variable or With block variable not set.&quot; and the debugger refers me to the following line of code.

Set OLMailTech = App.CreateItem(olMailItem)

Yet when I run this code with outlook open in advance, this successfully creates the mail item.

Do you think this may be a problem of timing? ex: The Set OLMailTech code is executing before the Outlook instance is open? If so, how do I delay the Set OLMailTech from executing until OL is open?

Thanks,
Faded

 

[lxn]

Yeah, I think that would be the problem. If there's no outlook instance available, a mail item cannot be created because it doesn't know what App is. Um I don't know exactly what ur code looks like, but from what I see here, I think one thing u can do is to remove the &quot;Call Shell(&quot;Outlook.exe&quot;, 1)&quot; line, and make this sub a function which returns as a boolean. (this is actually what i was doing, I just changed the coding a little bit the first time to fit what u wanted ) Um then you could call this function from where you are creating the instance of Outlook and mail item, if the function returns True, then it's safe to create a mail item because an instance of Outlook already exists; if the function returns False, then you can put the line &quot;Call Shell(&quot;Outlook.exe&quot;, 1)&quot; right there, so it'll create an instance of Outlook first, and then it'll be safe to create a mail item after it.

Don't know if this is what you wanted, hope it helps a bit.

-- Merlin

 

[Faded]

Well, I have tried to implement your suggestions, and must be doing something wrong. I can't seem to make it work. Right now I just have a new instance of Outlook open along with an Access msgbox saying to resend the mail. The error is logged to a text file and handled so the app doesn't choke, but still lacks the functionality of a one click send for users when Outlook is not currently running.

Thanks for your suggestions and feedback.

Faded

 

[lxn]

Hmm, well I don't know exactly how you coded it, if you don't mind the trouble and would like to post the related coding in here, I'm sure a lot of the programmers here would be glad to help you check it out. Good luck.

 

[fsweb]

ABout the security box that comes up, there is an option on Options>Security (Outlook Express 6 anyway) that you tick off and there is no box poping up everytime Access sends an email. I am not sure if it is the same with Outlook, as I don't have the latest Outlook version.

I am also running Access to send several hundred emails to clients automatically, so I use a computer which has Outlook Express 6 with the option of &quot;Warn me when other applications try sending email as me&quot; ticked off.
Obviously I tick it again (for security purposes) once the mailing is done...
I hope that helps.
frank

 

[lxn]

Frank, thanks for the input. However, I doubt that Outlook has that option. Cos if it's that easy, there wouldn't be so many ppl having this problem and can't seem so solve it. I don't have the latest outlook to check with either, so if anyone with the latest outlook and it's latest sercurity patch could confirm this, it would be great.

 

[Faded]

Here's the code.

Private Sub cmdMailer_Click()

Const cnstApp = &quot;outlook.application&quot;
Dim App As Object
Dim OLMailTech As MailItem
Dim OLMailRequestor As MailItem
Dim SendToTech As String
Dim SendToRequestor As String
Dim DisplayName As String
Dim Category As String
Dim Priority As String

Set App = GetObject(, cnstApp)

'Sets who will get the mail
SendToTech = Me!tech1
SendToRequestor = Me!cboRequestor

'gets priority and category for the email subject line
Priority = GetPriority(priorityID)
Category = GetCategory(categoryID)

'Creates email to tech with the problem description in the message body.
'The following set command is where I get the error.
Set OLMailTech = App.CreateItem(olMailItem)
With OLMailTech
.To = SendToTech
.Subject = &quot;Code &quot; & Priority & &quot; for &quot; & cboRequestor & &quot;, problem with &quot; & Category
'Puts [none] in message body if there is no description
If IsNull(Me!txtProblemDescription) Or Me!txtProblemDescription = &quot;&quot; Then
.Body = &quot; [none] &quot;
Else
.Body = txtProblemDescription
End If
.Display
End With

'Creates email to user with an acknowledgment of task.
Set OLMailRequestor = App.CreateItem(olMailItem)
With OLMailRequestor
.To = SendToRequestor
.Subject = &quot;Request for Technical Support&quot;
.Body = &quot;We're working on it!&quot;
.Display
End With

End Sub

Thanks,
Faded

 

[lxn]

Okay, I think this would work:

Change the Sub I provided to this Boolean Function first:

public Function usdOutlookIsOpen As Boolean

Const cnstApp = &quot;outlook.application.8&quot;
Dim lbooRetry As Boolean
Dim App As Object

On Error GoTo usdOutlookIsOpen_Err

lbooRetry = False
Set App = GetObject(, cnstApp)
If lbooRetry = True Then
'Deleted the Call Shell Line
usdOutlookIsOpen = False
Else
usdOutlookIsOpen = True
End If
Exit Function

usdOutlookIsOpen_Err:

If Err.Number = 429 And lbooRetry = False Then
lbooRetry = True
Resume Next
Else
'Handle other errors
End If
end Function

Then, add this condition before you create the mail item:

'Checks if Outlook is already open, if not, open outlook first:
If usdOutlookIsOpen = False Then
Call Shell(&quot;Outlook.exe&quot;, 1)
End If
Set OLMailTech = App.CreateItem(olMailItem)

......

Oh, and um, remove the 'Set App = GetObject(, cnstApp)' line from your sub too, cos it's now in the Function.
I think this should fix it, don't have time to test it out though, good luck.

-- Merlin