automatically sending emails from MS Access 2007

[toddr]

I have a MS Access 2007 app which uploads images from a number of users to a
file server. When a user uploads an image i want to generate an email
notifying someone that there is a new image in a particular folder. I want it
to be seemless to the person uploading so i don't want to use sendobject as
that causes a send window to pop up. I found code to use outlook objects in
VBA which work fine on my computer with the full version of Access 2007.
problem is when i package and distribute with Access 2007 Runtime I get an
error on the send command.

I have included references for MS Outlook 12.0 library, DAO 3.6, VBA, etc.
but it still does not work. Anyone have any ideas? I will also post in the
Access VBA forum.

Thanks,

Todd

 

[Ken Slovak - [MVP - Outlook]]

Without knowing what code you're using it's impossible to tell anything.
What errors are you getting?

Do the target machines have Outlook installed? Do they have up to date AV?
Does your code run at all?

 

[toddr]

code looks something like this:


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(DLookup("[EmailRecipient]",
"tblSystemVariables"))
objOutlookRecip.Type = olTo
.Subject = "SHP Imagemover files have been uploaded."
.Body = varMsgTxt
.Send
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End With
End If

target machine has outlook 2007 installed. the error i get is generic since
it is using runtime. something like "on click error..." I can get exact error
if that will help.

the other thing is that if outlook 2007 is open i will get a pop-up dialog
that will ask to allow or deny the send. if i click allow it sends the email.
i thought that by using the code it would just send the email and bypass the
outlook security regardless if outlook is open or not. am i mistaken?

thanks for your help.

 

[toddr]

also, i notice that if i close outlook on my computer and try to run using
the full version of access 2007 i get an error that says:

Run-time error '287':

Application-defined or object defined error

so i guess that means that outlook has to be opened to send using outlook
objects?

 

[Ken Slovak - [MVP - Outlook]]

You can safely assume that if Outlook is not running you can't use it for
anything.

 

[Ken Slovak - [MVP - Outlook]]

The Outlook 2007 security is more flexible than the earlier security, but
for outside accesses to Outlook (not in an Outlook COM addin or the Outlook
VBA project) you need to have an up to date AV package and have the other
settings set up to allow the code to run without security. Mainly an active
AV is critical.

The code looks OK, but I'd add a couple of things. First, I'd add a
NameSpace object to make sure you're fully logged in:

Dim oNS As Outlook.NameSpace

Set oNs = objOutlook.GetNameSpace("MAPI") 'after objOutlook is instantiated

I'd also add resolution of your recipient object after setting the address:

Set objOutlookRecip = .Recipients.Add(DLookup("[EmailRecipient]", _
"tblSystemVariables"))

objOutlookRecip.Type = olTo
objOutlookRecip.Resolve

Other than that I'd need to see the exact error and know what line causes
the error.

 

[toddr]

thank you. i have added the recommended code and i will test to see if it
helps.

what is an AV package and how do i get/install one?

The Outlook 2007 security is more flexible than the earlier security, but
for outside accesses to Outlook (not in an Outlook COM addin or the Outlook
VBA project) you need to have an up to date AV package and have the other
settings set up to allow the code to run without security. Mainly an active
AV is critical.

The code looks OK, but I'd add a couple of things. First, I'd add a
NameSpace object to make sure you're fully logged in:

Dim oNS As Outlook.NameSpace

Set oNs = objOutlook.GetNameSpace("MAPI") 'after objOutlook is instantiated

I'd also add resolution of your recipient object after setting the address:

Set objOutlookRecip = .Recipients.Add(DLookup("[EmailRecipient]", _
"tblSystemVariables"))

objOutlookRecip.Type = olTo
objOutlookRecip.Resolve

Other than that I'd need to see the exact error and know what line causes
the error.

code looks something like this:


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(DLookup("[EmailRecipient]",
"tblSystemVariables"))
objOutlookRecip.Type = olTo
.Subject = "SHP Imagemover files have been uploaded."
.Body = varMsgTxt
.Send
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End With
End If

target machine has outlook 2007 installed. the error i get is generic
since
it is using runtime. something like "on click error..." I can get exact
error
if that will help.

the other thing is that if outlook 2007 is open i will get a pop-up dialog
that will ask to allow or deny the send. if i click allow it sends the
email.
i thought that by using the code it would just send the email and bypass
the
outlook security regardless if outlook is open or not. am i mistaken?

thanks for your help.

 

[toddr]

ok. i don't suppose that there is a way to check and see if outlook is open
or not. so if it is not i can display a message to have the user open it.

the other alternative is just to add an on error resume next so i will just
bypass the code and not send the email.

 

[toddr]

sorry AV means Anti-Virus. i get it. i am just a little slow today. have not
had my second cup of coffee yet. we do have one. Trend Micro package. i see
that in the trust center i can change programatic access. i will give that a
try since we have up to date AV.

thank you. i have added the recommended code and i will test to see if it
helps.

what is an AV package and how do i get/install one?

The Outlook 2007 security is more flexible than the earlier security, but
for outside accesses to Outlook (not in an Outlook COM addin or the Outlook
VBA project) you need to have an up to date AV package and have the other
settings set up to allow the code to run without security. Mainly an active
AV is critical.

The code looks OK, but I'd add a couple of things. First, I'd add a
NameSpace object to make sure you're fully logged in:

Dim oNS As Outlook.NameSpace

Set oNs = objOutlook.GetNameSpace("MAPI") 'after objOutlook is instantiated

I'd also add resolution of your recipient object after setting the address:

Set objOutlookRecip = .Recipients.Add(DLookup("[EmailRecipient]", _
"tblSystemVariables"))

objOutlookRecip.Type = olTo
objOutlookRecip.Resolve

Other than that I'd need to see the exact error and know what line causes
the error.

code looks something like this:


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(DLookup("[EmailRecipient]",
"tblSystemVariables"))
objOutlookRecip.Type = olTo
.Subject = "SHP Imagemover files have been uploaded."
.Body = varMsgTxt
.Send
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End With
End If

target machine has outlook 2007 installed. the error i get is generic
since
it is using runtime. something like "on click error..." I can get exact
error
if that will help.

the other thing is that if outlook 2007 is open i will get a pop-up dialog
that will ask to allow or deny the send. if i click allow it sends the
email.
i thought that by using the code it would just send the email and bypass
the
outlook security regardless if outlook is open or not. am i mistaken?

thanks for your help.

 

[Ken Slovak - [MVP - Outlook]]

As a test for seeing if Outlook is already open you can use GetObject to get
the Outlook instance. If that returns a null object then you use
CreateObject to create it.