Error with SendObject in Macro

  • Thread starter Thread starter Klang
  • Start date Start date
K

Klang

A coworker and I have had a similar issue and are curious
if there is any kind of fix. We both have all updates
installed on our machines, so we know its not a missing
service pack. While running a macro, we use a SendObject
action to email a query. It repeats this process several
times to email different queries. What looks to be
completely at random, we both get the Access error that
forces Access to close out, backup, and repair. I was
able to rerun the process, with no problem. My coworker
tried the same thing, but it kept killing Access. After
trying 3 or 4 times, it finally worked. We're working in
Access 2003 databases linked to a SQL Server backend.
Anyone have any solutions/answers?
Thanks.
 
I have also experienced this in Access 2000. My solution
was to send using code instead of macros.
This is the complete Email code. I obtained this from one
of the MVP's(Forgot wich one). I have seen quite a bit of
code for sending email. I have tried many of them. If
you are just sending an Access report you can use the
SendObject command. If attachments are files of any type
try the code below. The one below( I have it stored as a
function in a new module) has been the most reliable for me
with MS Outlook and Access 2000 and can send up to 5
attachments or none. Also I am able to pull from an open
form email address of the recipient, CC, BCC, and the
subject and text and file name of the attachment(s).
To reference data on an open form use
=[forms]![formname].[emailaddress]
=[forms]![formname].[subject]
=[forms]![formname].[attachment1]
ect.


Good Luck

Jim

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String,strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for body here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
strAttachment2 = "attachment3"
strAttachment3 = "attachment4"
strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
If Len(strAttachment2) <> 0 Then
olMail.Attachments.Add (strAttachment2)
If Len(strAttachment3) <> 0 Then
olMail.Attachments.Add (strAttachment3)
If Len(strAttachment4) <> 0 Then
olMail.Attachments.Add (strAttachment4)
End If
End if
End if
End if
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function


Jim
 
Here is the short way
Private Sub Command32_Click()
On Error GoTo Email_Initial_Request_Err
Dim mvar, Submitted, strproblem, strsubject
Dim Mydate

strproblem = Me![subject]
strsubject = Me![Text]

'
On Error GoTo Email_Initial_Request_Err

DoCmd.SendObject , "Email Initial Request",
"MicrosoftExcel(*.xls)", "Email address1, "Email address2",
strproblem, strsubject, False, ""
'true will open the email before sending. False sends
immediatly
Beep
MsgBox "Email has been sent.", vbOKOnly, ""

Email_Initial_Request_Exit:
Exit Sub
 
Back
Top