Repost - Automating Excel and Outlook from Access

[Guest]

Good afternoon,
I'm familiar with the SendObject method in VBA. However, I do not believe
that this is the right solution to my question. What I would like to do is
generate an automated email from Access (VBA), but instead of including an
Access Object, I would like to attach another MS Office file - particularly
one in Excel. I'd like to make it Edit Message: no, so the user cannot
change anything.

Many of you will remember I was working on a budget tracking tool for my
employer. Well, I'm working with a different company now who would like to
do the same thing, but with a different process. So, I have these CERs
(Capital Expense Request) that need to be either OKAY or RETURNed back to the
Engineer (my peer group). If they are Okay, I need to just email the CER -
Excel spreadsheet per our Finance department over which I have no control -
to the Distribution Center General Manager for hardcopy signature and
submittal. If they are not okay, they need to be RETURNed to the engineer
for editing and re-preparation for my boss.

The continuous form is a set of instructions in the header for my manager
with each record containing a hyperlink leading to the file on the network,
including command buttons OKAY and RETURN. The hyperlink is for the manager
to open the CER to review. After reviewing, he needs to make his assessment
and use the application to carry out his command. I just cannot figure out
how to get Access to actually sendobject on a non-MS Access Object.

Can someone please help me out?

Thank you in advance!
Derek Wittman

 

[Geoff]

Derek,
You need to look up Automation.
You can automate Outlook, create an email, add an attachment, send the
email (with or without user intervention), all from within Access
without using the SendObject method.
Check out http://www.slipstick.com
Geoff

 

[Geoff]

I'm posting this reply for a second time as the first time it didn't
appear:

Derek,
You need to look up Automation.
You can automate Outlook, create an email, add an attachment, send the
email (with or without user intervention), all from within Access
without using the SendObject method.
Check out http://www.slipstick.com
Geoff

 

[Geoff]

Sorry for the repeated messages...

I didn't mean this to turn into a question of my own, but I don't
understand why Outlook Express downloads the header of my reply post
and then, when I try to download my post, I'm told the message has
expired. My repeats were an attempt to understand what was going on.

If anyone has a clue, I'd like to hear. Thanks...

Geoff

 

[Dirk Goldgar]

Sorry for the repeated messages...

I didn't mean this to turn into a question of my own, but I don't
understand why Outlook Express downloads the header of my reply post
and then, when I try to download my post, I'm told the message has
expired. My repeats were an attempt to understand what was going on.

If anyone has a clue, I'd like to hear. Thanks...

I'm no expert on this, but as I understand it, there are multiple
servers hosting the microsoft.public newsgroups. Sometimes they get out
of sync, so that one server has the message and another -- or others --
don't. A message for which you already have downloaded the header can
appear to vanish from the server because (a) your attempt to download
the body hits a server to which the message hasn't propagated yet, or
(b) a glitch in the synchronization of the servers causes your original
message to be considered bogus, and it is deleted from the server. I'm
not entirely sure about (b), but I know that (a) happens.

 

[Guest]

Geoff,
I think I'm having a foggy day as I cannot seem to find what I'm looking for
on Sue's site. I'll continue to look, but if someone sees it on her site and
can send me the URL, that would be fantastic and MUCH appreciated.

Thank you!
Derek

 

[Geoff]

Dirk,

Thanks for your reply.

I felt I owed this group an explanation of the apparently potty
repeats of the same post. I've posted an enquiry to the Outlook
Express newsgroup and I'll see what turns up. It has occurred to me
that it might also be a memory problem. The problem resolved itself
after I reset the newsgroup (ie deleted some 10,000 messages and
downloaded just 300 headers). My local dbx file for the group would
then have been much smaller.

Thanks again.
Geoff

 

[Geoff]

Derek,

Sorry for the lack of detail in my previous post. Here is a more
detailed reply.

Below is:

1. Sample code.
2. Two book recommendations.


Sample Code:

Export your Access object to a hard disc file, so that you can then
attach it to an Outlook mail item.

The code below assumes that, in your Access project, you have set a
reference to the Outlook object library. To do this, in your Access
project, in the VBA editor, open the Tools menu, click References, and
in the References dialog, check Microsoft Outlook Object Library.
Copy and paste this code and give it a test drive.

Sub TestSendMailItem()

' Example call to the SendMailItem subprocedure (below),
' passing to it relevant information:
SendMailItem _
vntTo:="RecipientsEmailAddressGoesHere", _
vntSubject:="My Subject Heading", _
vntBody:="This is the body text of the email", _
vntCC:="CopyEmailAddressGoesHere", _
vntAttachment1:="C:\MySpreadsheet.xls"

End Sub

Sub SendMailItem( _
vntTo As Variant, _
vntSubject As Variant, _
vntBody As Variant, _
Optional vntCC As Variant, _
Optional vntBCC As Variant, _
Optional vntAttachment1 As Variant, _
Optional vntAttachment2 As Variant)

' Sends an email using Outlook.

' In (compulsory):
' vntTo (email addresses of recipients)
' vntSubject (email subject heading)
' vntBody (email body text)
' In (optional):
' vntCC (email addresses of copies to)
' vntBCC (email addresses of blind copies to)
' vntAttachment1 (PathName of 1st attachment,
' eg "C:\MySpreadsheet.xls")
' vntAttachment2 (PathName of 2nd attachment)

Dim objOLK As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objML As Outlook.MailItem
Dim objATS As Outlook.Attachments

On Error GoTo ErrorHandler

' Initialise Outlook object variables:
Set objOLK = New Outlook.Application
Set objNS = objOLK.GetNamespace("MAPI")

' Allow user to logon to Outlook
' (ignored if not on network):
objNS.Logon , , True, False

' Initialise a mail item:
Set objML = objOLK.CreateItem(olMailItem)
objML.To = vntTo & ""
objML.Subject = vntSubject & ""

' Send the mail item in HTML format so as
' attachments won't be in body text and
' will be received by non-Outlook users:
objML.HTMLBody = vntBody & ""

' Set optional CC (Carbon Copy) property:
If Not IsMissing(vntCC) Then
objML.CC = vntCC & ""
End If

' Set optional BCC (Blind Carbon Copy) property:
If Not IsMissing(vntBCC) Then
objML.BCC = vntBCC & ""
End If

' Intialise attachments (if any):
Set objATS = objML.Attachments
If Not IsMissing(vntAttachment1) Then
objATS.Add vntAttachment1
End If
If Not IsMissing(vntAttachment2) Then
objATS.Add vntAttachment2
End If

' If you want the code to put the email in
' the Outbox without user intervention:
'objML.Send

' Alternatively, if you want the user to see
' message before sending:
objML.Display

' Clean up:
Set objATS = Nothing
Set objML = Nothing
Set objNS = Nothing
Set objOLK = Nothing

Bye:

Exit Sub

ErrorHandler:

MsgBox "Error No: " & Err.Number _
& vbNewLine & vbNewLine _
& Err.Description, vbOKOnly + vbExclamation, _
"Error"
Resume Bye

End Sub


Book Recommendations:

1. "Access Cookbook" by Getz, Litwin & Baron
2. "Microsoft Outlook Programming - Jumpstart for Administrators,
Developers, and Power Users" by Sue Mosher (see www.slipstick.com).


Regards
Geoff

 

[Guest]

Geoff,
Thank you for the clarification. I'm sure your previous post was fine. I'm
just a bit foggy in the brain lately. (which doesn't make for an efficient
programmer, true)

Derek,

Sorry for the lack of detail in my previous post. Here is a more
detailed reply.

Below is:

1. Sample code.
2. Two book recommendations.


Sample Code:

Export your Access object to a hard disc file, so that you can then
attach it to an Outlook mail item.

DW: My Access object is the.... form with the command button on it? I'm not
familiar with exporting forms. Further, I want to create (from scratch in
VBA) the Outlook mail item. Not have one existing already. (am I making
sense? I don't mean to be challenging. I'm just not 100% sure that I'm
stating my dilemna correctly).

Thank you!
Derek

 

[Guest]

Used your example code, modifying the name of the file to include as the
attachment. Problem is that my compiler does not recognize
Outlook.Application as a legitimate type in the below statement.
Dim objOLK As Outlook.Application

My configuration:
Windows XP Professional Service Pack 2
Access 2000
Outlook 2003

Any ideas ?

 

[Guest]

Sample code worked after all. Didn't have the Outlook Library checked in the
References the first time.

Now the question is - Is there any way to suppress the message that comes up
saying that "A program is automatically trying to send and email on your
behalf" ?

 

[Alex Dybenko]

try to look here:
http://www.outlookcode.com/d/sec.htm