Send an e-mail using code 1

[Guest_imported]

I am currently using the SendObject command but I get illegal operations on most computers. This appears to be a Microsoft bug - with no real resolution!

Is there any other way to send e-mails (with attachments would be fantastic!)?

 

[BanditWk]

I need the same thing - I have several different types of codes from Tek-Tips, both sets I've run into issues in regards to values, etc. So, I'm wanting the same thing. I was about to put up another Thread, however, you beat me to it so I've marked this one for e-mail notification.

It will be nice to finally get this e-mail off of my plate so I can go on to more fun things.
Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can - no need to send gifts - just send me a smile to show me that I've helped." ~ seen on a cardboard sign held by Roy McCafferty on a corner in Las Vegas

 

[mmayo]

redbearuk -

This has worked for me. As a note, you will need to set a reference to the "Outlook 98 Type Library".

Public Function fSend()
Dim objOutlook As Outlook.Application
Dim objMsg As Outlook.MailItem
Dim objOutRec As Outlook.Recipient
Dim objOutAttach As Outlook.Attachment
Dim myNameSpace As Outlook.NameSpace

Set objOutlook = CreateObject("Outlook.Application&quot

Set myNameSpace = objOutlook.GetNamespace("MAPI&quot

myNameSpace.Logon "Your Name Goes Here", , False, True

Set objMsg = objOutlook.CreateItem(olMailItem)

With objMsg
Set objOutRec = .Recipients.Add("Name of 1st person&quot
objOutRec.Type = olTo
Set objOutRec = .Recipients.Add("2nd person&quot
objOutRec.Type = olCC
Set objOutRec = .Recipients.Add("1st person for CC&quot
objOutRec.Type = olCC
Set objOutRec = .Recipients.Add("2nd person for CC&quot
'objOutRec.Type = olCC
Set objOutRec = .Recipients.Add("1st person for BCC&quot
objOutRec.Type = olBCC
.Subject = "Whatever you wish"
Set objOutAttach = .Attachments.Add("Path to attachment", olByValue, 3, "Title of attachment&quot
.Send
End With

Set objOutlook = Nothing
End Function


****
Just for your enjoyment, there can be issues involving the outlook security patch as well.

As always, if anyone knows a better way, I'd love to hear it!

 

[BanditWk]

My particular issue is not using any address in Outlook. In fact, using an e-mail address in my database because I have a list of clients and I may be wanting to send them a particular type of report or two.

Any modification changes in regards to this? Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can - no need to send gifts - just send me a smile to show me that I've helped." ~ seen on a cardboard sign held by Roy McCafferty on a corner in Las Vegas

 

[mmayo]

BanditWk -

Good question! My initial response is that you should be able to capture the client's address from you db. But, since I haven't tried this, I can't say for sure.

If you need the loop of code to populate from your db, let me know.

Mark

 

[BanditWk]

Yes, please send... I'm trying every stinking avenue to try and get this doggone thing to work - I'm beginning to hate the word "e-mail"... Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can - no need to send gifts - just send me a smile to show me that I've helped." ~ seen on a cardboard sign held by Roy McCafferty on a corner in Las Vegas

 

[KyleS]

Hi, as far as the Outlook automation goes, I don't know if this is a "better" way, but it is another way.

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 = "Name here"

With MailOutLook
.To = Recipient
.Subject = ""
.Body = ""
.Attachments.Add "PATH", olByReference,1, "Approval"
'Use 'olByValue' to send actual file as attachment
'.DeleteAfterSubmit = True 'This would let Outlook send the 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

As for getting multiple names from your dB something like this would work (I'm using DAO here, but ADO would look very simmilar)

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

Set rst = CurrentDB.OpenRecordset("QueryName&quot

Do Until rst.EOF
Recipient = Recipient & rst!EmailField & ";"
Loop

With MailOutLook
.To = Recipient
.Subject = ""
.Body = ""
.Attachments.Add "PATH", olByReference,1, "Approval"
'Use 'olByValue' to send actual file as attachment
'.DeleteAfterSubmit = True 'This would let Outlook send the 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

+ =
+ =

 

[mmayo]

BanditWk -

KyleS has some solid code! I'd say give his (the one for multiple names) a try.

Let us know your progress.

Mark

 

[KyleS]

Thanks Mark!

BanditWK,
Right now this will send the note to everyone in the query you use and the attachment will be a shortcut. As commented in the code changing the "olByReference" to "olByValue" will make it a true 'attachment'.

Also, I left off from the code
if you were to put:
.CC = "" or .BCC = ""
That would let you set who get CC'd and who gets BCC'd.

This can be fairly useful depending on your situation.

There is also the option of .DeleteAfterSubmit
which will remove the note from the user's sent bin. Fairly usefull if the user is automatically sending a large file and you don't want to fill up their sent bin.

But mostly, play around with the MailOutlook item and it's properties/functions. There are all sorts of things you can do from turning off autoforward on the note to requesting a read reciept.

Just have fun and let us know if we can be of any more help! Kyle

+ =
+ =

 

[BanditWk]

Oh Man, KyleS, this looks like it might work... Now, my question is this - this all started out by sending a report that was created in Access as an attachment.

Would I need to export the Report (please say "no", I'd prefer not to do this) and then access it by the "PATH"?

What about multiple attachments? Could I just do an .Attachments.Add for each one? Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can - no need to send gifts - just send me a smile to show me that I've helped." ~ seen on a cardboard sign held by Roy McCafferty on a corner in Las Vegas

 

[bigaustin]

Not long ago I was working on a similar problem. I found a number of helpful articles in the Microsoft Knowledge Base (

http://support.microsoft.com).

I particularly recommend article Q153311, "ACC: Using Automation to Send a Microsoft Exchange Message". Note that this might work with other MAPI clients, not just Outlook. And, as stated above, you'll need to add a reference to Outlook Object in your VB module.

 

[KyleS]

I know if it's a report within Access that SendObjects is the way you're supposed to do it, but we've already established the fact that that won't work for you.

So...

I'm not aware (I could be Way wrong here) of a way to attach an Access object to an Outlook mail message, especially if you want multiples. (Even when using SendObject you need to pick a non-access format).

What I would suggest would be a generic path for each file, something like "C:\Program Files\ExportFolder\Filename.xls"
or whatever. So if you're sending a report and you want it in snapshot format use something like:
DoCmd.OutputTo acOutputReport, "rptConcern", "snapshotformat (.snp)", "C:\Program Files\ExportFolder\Test.snp", False

to export your file then follow it with a ".Add" statement as above (using of course, 'olByValue' so a copy is actually attached to the e-mail) and then use a "KILL" statement to delete the file.


This is just off the top-of-my-head, so it may be an archaic way of doing this, but it will work...


Kyle

+ =
+ =