Creating E-mail in Outlook (Any)

Topic

New Reply

From Access, I need to be able to open a new e-mail message in Outlook, specifiy the “To:” address, and then append 1 or more files (I’m not appending Access objects). I just need to open it, so user can “customize” the message” and then manually send it.

I’m really lousy at Office automation, so does anyone know how to do this, or know where to point me so I can learn? And do you know if it will work with Outlook Express?

Reply | Quote

Replies

The attached text file contains a function SendMail. You can copy it to a standard module.
You’ll need to set a reference to the Microsoft Outlook n.0 Object Library where n=9 for Office 2000, 10 for Office XP, 11 for Office 2003 and 12 for Office 2007.

Example of its use:

SendMail “john@this.com;mary@that.org”, , , , , Array(“C:MyFilesReport.doc”, “C:MyFilesInvoice.xls”), True

Outlook Express does not have an exposed object model, so you can’t use it for this purpose.

Reply | Quote

Thanks, I’ll take a look at it.

I think I may have a problem, since I’m pretty sure the client that wants this capability is using Outlook Express. I have in the past used CDO to create and send e-mails, which works pretty well. But I can’t figure out how to give him a way to create the body of the message in HTML (essentially duplicating his Outlook template). Maybe saving the template to a file, and then opening it in Word might work.

And does your procedure actually send the e-mail, or just create it and allow the user to make changes?

Reply | Quote

The last argument to the function that I posted is the boolean ShowMail. If True is specified, the e-mail is displayed on screen, and the user can edit it. If False is specified, the e-mail is sent immediately.

I’m afraid I don’t know how to control Outlook Express; as I mentioned it doesn’t have an object model that is available to the programmer.

Reply | Quote

About the only thing I’ve been able to do with Outlook Express from Access is to use the Application.FollowHyperlink method. At least it gets them there.

Reply | Quote

You could even specify the To: address, but you won’t be able to add attachments that way.

Reply | Quote

This is very interesting I have been trying to do something simular.

How do you call the module. And where do you reference outlook, I’m presuming its not as simple as adding the SendMail code to the event of a button

Thanks

Kevin

Reply | Quote

1) Create a new code module by clicking New in the Modules section of the database window, or by selecting Insert | Module in the Visual Basic Editor.
2) Copy the code from my attachment and paste it into the module.
3) In the Visual Basic Editor, select Tools | References…
4) Scroll down until you see Microsoft Outlook n.0 Object Library (where n = 9, 10, 11 or 12 depending on your Office version).
5) Tick the check box for this reference.
6) Click OK.

You can call SendMail from anywhere in your code, for example from the On Click code of a command button. The syntax is

Function SendMail Recipient, CC, BCC, Subject, Message, Attachment, ShowMail

Recipient, CC, BCC, Subject and Message are strings. If you want to include multiple e-mail addresses in Recipient, CC or BCC, separate them with semicolons.
Attachment is an array of strings.
ShowMail is either True or False.

Example:

Private Sub cmdSend_Click()
SendMail “you@somewhere.com”, “”, “”, “This is a test”, “Please see the attached files”, Array(“C:This.txt”, “C:That.txt”), True
End Sub

Reply | Quote

Hans,

I had a chance to play with your function to send an e-mail.

I created a blank database with nothing in it other than a module that included your function, and made the reference to the Outlook library. When I tried to compile, it said there as an ambiguous reference to “SendEmail”! I changed the name of your function and it compiled, but I couldn’t figure out where the other SendEmail was. Strange. But it worked fine.

Anyway, I’m going to play around with it some more, specifically trying to change it to late-binding so I don’t have to reference the Outlook library. I let you know how it comes out.

Reply | Quote

Did you happen to name the module SendMail too? That would confuse VBA.

I have attached a version that uses late binding.

Reply | Quote

Thanks for the late-binding attachment.

No, it was just Module1. Although come to think of it, I tried importing the file first and it didn’t seem to take; I just ended up copying and pasting. Maybe Access had created an object and I just missed it?

Reply | Quote

> Maybe Access had created an object and I just missed it?

Perhaps, but I’d expect it to become visible at some time, even if not immediately.

Reply | Quote

Except that we had a little problem with Outlook at first which locked-up the database, so nothing was saved and I had to redo it.

Oh well, it doesn’t matter.

Reply | Quote

I created a test using the late binding, and it works great.

Only 1 problem, however. You had a line for adding attachments that was this:

.Attachments.Add Attachment(Counter), 1 ‘ olByValue

Regardless of the size of my Attachment array, the Attachments field in the e-mail only contained a single file. I had to use this instead:

.Attachments.Add Attachment(Counter)

Now I just have to convince my client to upgrad to Outlook from Outlook Express!

Reply | Quote

Hi,

Is there away of making Array(“C:This.txt”, “C:That.txt”), as hyperlinks and not attachments

Thanks

Reply | Quote

It depends on the format of the e-mails. You can’t insert a hyperlink into a plain text e-mail.

See the code in JohnBF’s Outlook Attachment Removal Manager in post 443,421 for an example of inserting hyperlinks into the body of an e-mail.

Reply | Quote