Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
2 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Error in MSAccess when trying to send mail thru outlook in office 2007
Posted on 2011-03-04
Hi there
I am getting an "Application defined or Object defined error" when I try and send mail from MS Access 2003 thru Outlook in Office 2007. This procedure works fine in 2003 but does n ot want to work in 2007. I have checked all the paramaters being parsed to the code and everything is there.
Your help will be greratly appreciated
Tx Al
I am getting an "Application defined or Object defined error" when I try and send mail from MS Access 2003 thru Outlook in Office 2007. This procedure works fine in 2003 but does n ot want to work in 2007. I have checked all the paramaters being parsed to the code and everything is there.
Your help will be greratly appreciated
Tx Al
Dim blRet As Boolean, AccName, AccMail As String
AccName = DLookup("ACName", "Clients", "ClientID=" & Me.ClientId)
AccMail = DLookup("ACEmail", "Clients", "ClientID=" & Me.ClientId)
blRet = ConvertReportToPDF("Invoice", vbNullString, "C:\database\Invoice No." + Str(Me.InvoiceId) + ".pdf", False, False, 0, "", "", 0, 0)
SendMessageOA True, "Datatrix Invoice Number " + Str(Me.InvoiceId), "Dear " & AccName & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Please find attached the abovementioned invoice for your perusal." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Kind Regards," & Chr(13) & Chr(10) & "Carol Longhurst", AccMail, , , "C:\database\Invoice No." + Str(Me.InvoiceId) + ".pdf"
End Sub
Sub SendMessageOA(ByVal bDisplayMsg As Boolean, _
ByVal sSubject As String, _
ByVal sMsgBody As String, _
ByVal sEmailTo As String, _
Optional ByVal sCCTo As String = "", _
Optional ByVal sBCCTo As String = "", _
Optional ByVal sAttachmentPath = "")
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim IndCheck As Boolean
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(0)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(sEmailTo)
' objOutlookRecip.Type = sEmailTo
' Add the CC recipient(s) to the message.
If sCCTo <> "" Then
Set objOutlookRecip = .Recipients.Add(sCCTo)
' objOutlookRecip.Type = sCCTo
End If
' Add the BCC recipient(s) to the message.
If sBCCTo <> "" Then
Set objOutlookRecip = .Recipients.Add(sBCCTo)
' objOutlookRecip.Type = sBCCTo
End If
' Set the Subject, Body, and Importance of the message.
.Subject = sSubject
.Body = sMsgBody
'.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If sAttachmentPath <> "" Then
Set objOutlookAttach = .Attachments.Add(sAttachmentPath)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
' Should we display the message before sending?
If bDisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
4
9 Comments
Active today
You code is working, check value in sEmailTo.
May be
AccMail = DLookup("ACEmail", "Clients", "ClientID=" & Me.ClientId)
is returning something wrong
May be
AccMail = DLookup("ACEmail", "Clients", "ClientID=" & Me.ClientId)
is returning something wrong
Active 1 day ago
That is not the problem. As I said This code works fine on a machine that has Office 2003 It has been working fine for years. The value for sEmailTo is coming from AccMail which the string above in the Sub
SendMessageOA True, "Datatrix Invoice Number " + Str(Me.InvoiceId), "Dear " & AccName & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Please find attached the abovementioned invoice for your perusal." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Kind Regards," & Chr(13) & Chr(10) & "Carol Longhurst", AccMail, , , "C:\database\Invoice No." + Str(Me.InvoiceId) + ".pdf"
SendMessageOA True, "Datatrix Invoice Number " + Str(Me.InvoiceId), "Dear " & AccName & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Please find attached the abovementioned invoice for your perusal." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Kind Regards," & Chr(13) & Chr(10) & "Carol Longhurst", AccMail, , , "C:\database\Invoice No." + Str(Me.InvoiceId) + ".pdf"
Active today
I have tested your code with Access and Outlook 2007 and it is working if I have assigned to AccMail some string with address ("myaddress@mydomain.com")
Active 1 day ago
That could be the problem. Are you using Access 2003? If so do you know which reference includes the SendOA libraries etc?
Active 1 day ago
I have just double checked my code and there is definitely a value coming back with the code but I hard coded the e-mail address anyway and still got the same error. I know that it is not the code that is the issue but as you say maybe one of my references is causing the problem.
Active today
OK. I have now same error on computer with Office 2010 only.
It is happened if outlook is not opened. Can you try to open outlook before running program?
It is happened if outlook is not opened. Can you try to open outlook before running program?
Active today
You can add call to this sub before calling SendMessageOA:
http://www.ozgrid.com/forum/showthread.php?t=73886&page=1
and one error corrected.
Sub CIOIR()
Dim oOutlook As Object
Dim oNameSpace As Object
Dim oInbox As Object
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNamespace("MAPI")
Set oInbox = oNameSpace.Folders(1)
'Handle errors
Err.Clear 'JIC
On Error Resume Next
'attempt
AppActivate ("Outlook")
If Err <> 0 Then oInbox.Display
'reset
Err.Clear
On Error GoTo 0
'cleanup
Set oInbox = Nothing
Set oOutlook = Nothing
Set oNameSpace = Nothing
End Sub
http://www.ozgrid.com/forum/showthread.php?t=73886&page=1
and one error corrected.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Email SQL Applications not able send to 4 or more recipients | 2 | 37 | |
| Access VBA If Statements | 5 | 18 | |
| Access Data Retrieval | 1 | 15 | |
| need a macro to find the 2 letter word in a cell | 4 | 18 |
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel.
This lets you automatically sync them with…
Suggested Courses
Course of the Month2 days, 18 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.