Solved

Error in MSAccess when trying to send mail thru outlook in office 2007

Posted on 2011-03-04
9
292 Views
Last Modified: 2012-05-11
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
 Debug error
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

Open in new window

0
Comment
Question by:caandal
[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
 
LVL 40

Expert Comment

by:als315
ID: 35041901
You code is working, check value in sEmailTo.
May be
AccMail = DLookup("ACEmail", "Clients", "ClientID=" & Me.ClientId)
is returning something wrong
0
 

Author Comment

by:caandal
ID: 35042064
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"
 
0
 
LVL 40

Expert Comment

by:als315
ID: 35042090
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")
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 40

Expert Comment

by:als315
ID: 35042093
May be some references are missing?
0
 

Author Comment

by:caandal
ID: 35043530
That could be the problem.  Are you using Access 2003?  If so do you know which reference includes the SendOA libraries etc?
0
 

Author Comment

by:caandal
ID: 35043684
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.
0
 
LVL 40

Expert Comment

by:als315
ID: 35044123
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?
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 35044154
You can add call to this sub before calling SendMessageOA:
 
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

Open in new window

It was taken here:
http://www.ozgrid.com/forum/showthread.php?t=73886&page=1
and one error corrected.
0
 

Author Closing Comment

by:caandal
ID: 35339863
Hi als315

Sorry for the delay in getting back to this issue.  We have been busy with a big rollout and I missed this reply

But it works perfectly
Thank you
Alan
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question