Send Outlook Message from Access 5

[vikin9]

I tried this question in one of the Access forums without success. Perhaps this is more appropriate here.

I want to launch an Outlook eMail message automatically from Access, but the process stops at the Choose Profile box if Outlook is closed. You must press "Enter" to proceed. I need a way to programatically get past the Choose Profile box.

Details:
I'm using the SendObject method to send the message and everything works fine as long as Outlook is already open.

If Outlook is closed, and you you simply press "Enter," the message sends and Outlook closes again (good). If you Cancel, the message is canceled (which I want to prevent).

This is a distributed database, on shared PCs, on the shop floor. It doesn't matter whose profile is chosen, because the message contains the info we want. How can I automatically pass the Choose Profile box so the message can't be cancelled?

I've tried using SendKey {Enter} but the code is stopped until you respond to the Choose Profile dialog box.

I'd be grateful for any suggestions. Thanks.

 

[KyleS]

Have you tried using actual Outlook objects?

I know the SendKeys is the easier way to ge, but if it's giving you problems maybe the long way will offer an option SendKeys does not...


Sub OutlookTest()
Dim Recipient As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Recipient = "Someone@somewhere"

With MailOutLook
.To = Recipient
.Subject = """
.Body = ""
.Attachments.Add "PATH HERE", olByReference, 1, "Approval"
'* Use olByValue to send actual file as attachment
'.DeleteAfterSubmit = True 'This would let Outlook send th note without storing it in your sent bin
.Send
End With


DriverExit:
On Error Resume Next
Set MailOutLook = Nothing
Set appOutLook = Nothing
Exit Sub

End Sub Kyle

+ =
+ =

 

[vikin9]

Thanks, Kyle!
This is a little advanced for me, but conceptually I see what you're doing. Should Access (97) recognize the Outlook objects, or do I need to define them somehow?

Pardon my ignorance, but let's say I wanted to use your code example to send myself a test message from a button on a form. I have everything else working beyond that. What or where can I go in the Help to learn the basics of setting this up?

THANKS!

 

[KyleS]

To get the Outlook objects you need to go into the module window (where the code goes) and go to Tools --> References and check the box next to "Microsoft Outlook 8.0 Objects"
(I always forget to tell people that!!!)

As for the Help, once you set the reference you should be able to hit F1 on "Outlook" and get some help from there. Otherwise you could check

http://msdn.microsoft.com

and search on "Outlook Automation".

I hope that answers your question, if not, or if you need more assistance, please ask! Kyle

+ =
+ =

 

[vikin9]

Terrific! I referenced the object library as you said, hit my test button and it worked first try! Thanks, Kyle!

(Now I want to know how do the Star Wars thing below your signature.)

 

[KyleS]

Go into your personal profile and for your signature type this in:

[ anakin ] + [ curse ] = [ vader2 ]
+ =

And

[ anakin ] + [ amidala ] = [ lightsaber ]
+ =


just remove the space between the brackets and the words. Kyle

 

[vikin9]

Thanks, again!

 

[Coxylaad]

Hi,
That is a top bit of code there, but I have one more question:
How can you create the mail message then open it for editing using the outlook object?

Ta

Ian

 

[Coxylaad]

oops just worked out how to do it!
sorry!

Ian

 

[Coxylaad]

OK this one is valid.

How to I insert the senders autosignature automatically at the bottom of the email body

 

[KyleS]

Ummm I don't know (I don't have a sig, so this has never come up for me before). I'll look into it and get back to you.

Kyle

 

[KyleS]

OK, just don't set the body = ""

Then the sig shows up just fine.


Kyle

 

[Coxylaad]

Ah, My mistake again!

I assumed that using because the sendobject method doesnt attach the autosig then the outlook object would need some command to attach it too - which it doesnt, it comes in automatically.

Thanks Kyle

 

[KyleS]

No problem


Kyle

 

[sklambert]

Hello,
I posted this in another thread, but no response. I am hoping someone here can help. I am trying to use Outlook to send mail. I am using the following code:

'******begin code*****

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

'**************************************************************
'*create string with email address

strEmail = [EMAIL ADDRESS]

strBody = txtTDate & Chr(13) & Chr(13)
strBody = strBody & "Dear " & [FIRST NAME] & " " & [LAST NAME] & "," & Chr(13) & Chr(13)
strBody = strBody & "Your item was shipped today." & _
" Please let us know when you receive it." & Chr(13) & Chr(13) & Chr(13)
strBody = strBody & "Shipping method: " & [SHIPPING METHOD] & Chr(13)
strBody = strBody & "Date shipped: " & [DATE SHIPPED] & Chr(13)
strBody = strBody & "Tracking / Confirmation number: " & [TRACKING NUMBER] & Chr(13)
strBody = strBody & "Sincerely," & Chr(13) & Chr(13)
strBody = strBody & "Acme Corporation"

'***creates and sends email
With objEmail
.To = strEmail
.SUBJECT = "Your item has been shipped"
.body = strBody
.Send
End With


Set objEmail = Nothing
objOutlook.Quit

Exit Sub

End Sub
'****end code*****

When I try to send mail, I get the following error:

"Run-time error '-1975500795 (8a404005)':

Outlook does not recognize one or more names"

In the debug screen, the .Send is highlighted in yellow. What am I doing wrong? Any help would be appreciated.

 

[dyarwood]

I use instead of To

recipients.Add "Email address"

Also make sure you have got the correct references selected.

 

[KyleS]

Actually, the error you're getting sounds more like Outlook doesn't recognize one of your e-mail addresses. Are you entering the complete address? (i.e. Name@Website.com) Or are you entering their alias in your address book? (i.e. Name)

Kyle

 

[sklambert]

The email address comes from a field on the form in my Access database. The code to send the email is behind a button. I used the following code with the same email address and it works:

Private Sub SENDEMAIL_Click()

'****begin code****
Dim ORIGIN, DESTINATION, SENDTO, SUBJECT, MESSAGE As String

ORIGIN = "ebay2628@yahoo.com"
DESTINATION = [EMAIL ADDRESS]
SENDTO = [EMAIL ADDRESS]
SUBJECT = "Your item has been shipped."
MESSAGE = "Hello," & Chr(13) & Chr(13) & _
"Your item has been shipped via " & [SHIPPING METHOD] & "." & Chr(13) & Chr(13) & _
"The tracking /confirmation number is " & [TRACKING NUMBER] & "." & Chr(13) & Chr(13) & _
"Again, thank you for your purchase." & Chr(13) & Chr(13) & _
"Sharron Lambert"

DoCmd.SendObject , , , SENDTO, , , SUBJECT, MESSAGE, True

End Sub

'****end code****

I'll try again to make absolutely sure the email address is valid.

Also, is there a way to change the FROM (Sender) email address?

 

[sklambert]

Well, it seems you are right Kyle. When I tried the first set of code again and used a good email address, it worked fine. Thanks! I still am wondering how to change the sender email address or if this is possible.

 

[KyleS]

Happy to help Sharron,

As for changing the "FROM" address. That I don't know. I've seen it discussed on these boards before, so you might do a search through this category, as well as the Access VBA category and the VB ones. Something in there ought to give you what you're looking for.

Best of luck,



Kyle