By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Email With attachment woes

P: n/a
Hullo Everybody

I am trying to send a email from WITHIN Access 2000 with a report
attached that is generated WITHIN Access in the normal way, but all the
help examples I have seen uses an external file like C:\test.txt

I have been using:
DoCmd.SendObject acReport, stDocName, acFormatRTF,

But when the message body is more than 70 characters long (random), it
craps out, and it seems that the general opinion is that SendObject is
too unreliable to use for a runtime application.

So I have now moved to the method below which almost works except for 2
things. .........What I need to know is how to:

1.Add an attachment of one of the internal reports (rptOrderEmail) to
the email.

2. Supress the handling form with the message "Another program is
trying to access your Inbox" a pain.

Thanks in Advance
Bob

Private Sub CmdEmail_Click()
On Error GoTo Error_Handler
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strDocName As String
Dim rptInvoiceEmail As Report_rptOrderEmail
strDocName = "rptOrderEmail"
Dim objOutlookAttach As Outlook.Attachment

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = Me.txtSupplierPrimaryEmail
.Subject = Me.txtSubject
.Body = Me.MemoSupplierNotes
Set objOutlookAttach = .Attachments.Add(strDocName)
.Send
'.ReadReceiptRequested
End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Option Compare Database
Option Explicit

Dim mGetDPG As String
Dim mGetRecipients As String
Dim mOutlook As Outlook.Application
Dim mOutlookMsg As Outlook.MailItem
Dim mOutlookRecip As Outlook.Recipient
Dim mOutlookAttach As Outlook.Attachment
Dim mstrAddRecipients As String
Dim mAttachmentPath As String
Dim mSession As Outlook.NameSpace
Dim myPathToLookForFile as string
Dim i As Integer
Dim strRemovePath As String
Dim frm As New Form_frmEmailSend

Public Function CreateMail(ByRef astrRecip As Variant, ByRef
astrRecipCC As Variant, strSubject As String, strMessage As String,
Optional astrAttachments As Variant) As Boolean
' This procedure illustrates how to create a new mail message
' and use the information passed as arguments to set message
' properties for the subject, text (Body property), attachments,
' and recipients.
Dim ParaA(5) As String
Dim strSearchCriteria As String
Dim objNewMail As Outlook.MailItem
Dim varRecip As Variant
Dim varAttach As Variant
Dim blnResolveSuccess As Boolean
Dim golApp As Outlook.Application
Dim initializeOutlook As Boolean
Dim fs
Set fs = Application.FileSearch

initializeOutlook = True

On Error GoTo CreateMail_Err

' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If initializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If

Set golApp = New Outlook.Application
Set objNewMail = golApp.CreateItem(olMailItem)

With objNewMail
' For Each varRecip In astrRecip
.Recipients.Add astrRecip
.CC = astrRecipCC
' Next varRecip
blnResolveSuccess = .Recipients.ResolveAll

With fs
..
fs.LookIn = myPathToLookForFile

' Can include full name or part of the file name for difference
months, times, etc,
strSearchCriteria = "*".rtf"

' .FileName = "*.rtf"
.filename = strSearchCriteria

If .Execute > 0 Then
'
For i = 1 To .FoundFiles.Count

ParaA(i) = fs.FoundFiles(i)

objNewMail.Attachments.Add ParaA(i),
olByValue, i

Next i

End If

End With

.Subject = strSubject
.Body = strMessage

If blnResolveSuccess = True Then
' .Send
Else
' MsgBox "Unable to resolve all recipients. Please check " _
' & "the names."
.Display
End If
End With

CreateMail = True

CreateMail_End:
Exit Function
CreateMail_Err:
CreateMail = False

' there is some code I seen, were by the prompt warning can be bypass,
click oK automatic.
Select Case Err.Number

Case Is = 287

MsgBox "You clicked No to the Outlook security warning. " & _
"Return the procedure and click Yes to access e-mail" & _
"addresses to send your message. For more information," & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp. "

Case Is = -2009989111

MsgBox Err.Number & "" & Err.Description = "Reciepents Error"

Case Is = -1525219325

MsgBox Err.Number & " " & Err.Description = "Attachment Error"

Case Is = 438

MsgBox Err.Number & " " & Err.Description
Case Else

MsgBox Err.Number & "" & Err.Description

End Select
Resume CreateMail_End
End Function
Public Property Get GetDPG() As String

GetDPG = mGetDPG

End Property

Public Property Let GetDPG(ByVal vNewValue As String)

mGetDPG = vNewValue

End Property

Public Property Get GetRecipients() As String

GetRecipients = mGetRecipients

End Property

Public Property Let GetRecipients(ByVal vNewValue As String)

mGetRecipients = vNewValue

End Property

Private Sub Class_Initialize()
'Set mGetDPG = Nothing
'Set mGetRecipients = Nothing
Set mOutlook = Nothing
Set mOutlookMsg = Nothing
Set mOutlookRecip = Nothing
Set mOutlookAttach = Nothing
'Set mstrAddRecipients = Nothing
'Set mAttachmentPath = Nothing
Set mSession = Nothing
End Sub

Nov 13 '05 #2

P: n/a
Thanks loringdo

I was beginning to think it was impossible

bob

Nov 13 '05 #3

P: n/a
Bob:

To suppress the Outlook Security Warning, you can download & install
the freeware called Express ClickYes v1.2. I've utilized it for quite
some time, and have had no issues.

Here's where you can get it:
http://contextmagic.com/express-clickyes/

HTH,
Jana

Nov 13 '05 #4

P: n/a
Hi Jana

Thats an interesting piece of kit. I was hoping to build some sort of a
Suppresser into the software, and update the existing userbase with a
new MS Access front end. However, it might be an idea to tell them to
get this software.

Thaks for the suggestion

Bob

Nov 13 '05 #5

P: n/a
Hi Bob:

You're welcome!

Yes, bypassing Outlook Security on your own is going to be rough. I
figure why reinvent the wheel? Since this is freeware and allows the
user to decide when to use it & when not to, it does exactly what I
need. Also, you can turn it on in the middle of a process without any
grief.

Happy coding,
Jana

Nov 13 '05 #6

P: n/a
Hi Jana
Thanks for your help

THe problem with this that if you bypass Outlook Security for the
program that you want it for ie my Access.mde, you are also bypassing
Outlook Security for Malware, spyware etc.

Regards Bob

Nov 13 '05 #7

P: n/a
Welcome,

loringdo

Nov 13 '05 #8

P: n/a
Jana thanks for the link, I been reading a lot on Security Warning
utility. Better yet they give some code to use also,

Thanks for sharing,

loringdo

Nov 13 '05 #9

P: n/a
You're welcome, Bob. The software has the ability to be turned on and
off, so I only enable it when I'm actually running code that sends out
emails. Otherwise, it's disabled, thereby minimizing the opportunity
for Malware, Spyware, etc.

It's not a perfect solution, but it accomplishes what you're looking
for.

Jana

Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.