none
Creating/Sending Outlook Messages via Access VBA RRS feed

  • Question

  • I am having trouble with a code I've written to automate Outlook to attach and Send Access Reports via a Push Button Macro.  This code has been working create for a couple forms for months, the message is created, the subject line and recipients are correct and the correct attachment is there.  Then when I push send, I get a message "The Operation Failed."  Sometimes on a second time the message will send and sometimes it will not work on subsequent attempts.  What might cause this?  I'm not sure if it's something in the VBA Code(which seems unlikely, because it's worked properly for months) or something on the Outlook end...My VBA procedure code is pasted below:

    Private Sub Command143_Click()
    Dim objOutlook As Object
        Dim objOutlookMsg As Object
        Dim objOutlookRecip As Object
        Dim objOutlookAttach As Object
        Dim strFileName As String

        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(0) ' olMailItem

        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            If Not IsNull([Forms]![Purchase Orders]![PoEmailTo]) Then
                Set objOutlookRecip = .Recipients.Add([Forms]![Purchase Orders]![PoEmailTo])
                objOutlookRecip.Type = 1 ' olTo
            End If

            ' Add the CC recipient(s) to the message.
            If Not IsNull([Forms]![Purchase Orders]![PoCcTo]) Then
                Set objOutlookRecip = .Recipients.Add([Forms]![Purchase Orders]![PoCcTo])
                objOutlookRecip.Type = 2 ' olCC
            End If

           ' Add the BCC recipient(s) to the message.
            If Not IsNull([Forms]![Purchase Orders]![PoBccTo]) Then
                Set objOutlookRecip = .Recipients.Add([Forms]![Purchase Orders]![PoBccTo])
                objOutlookRecip.Type = 3 ' olBCC
            End If

            
      

            ' Set the Subject and Body of the message.
            .Subject = "PURCHASE ORDER " & Me.[poid]
            .Body = "Attached please find Purchase Order#: " & Me.[poid] & ".   Please confirm receipt at your earliest convenience.  Sincerely, Company." & vbCrLf & vbCrLf

            ' Create PDF
            strFileName = "z:\dashboard\Purchase Orders\PO # " & _
                Me.poid & " " & Forms![Purchase Orders].Form!Text140 & ".PDF"
            DoCmd.OutputTo ObjectType:=acOutputReport, _
                ObjectName:="Purchase Order", OutputFormat:=acFormatPDF, _
                OutputFile:=strFileName, AutoStart:=False
            ' Add attachment to the message.
            Set objOutlookAttach = .ATTACHMENTS.Add(strFileName)

           ' Use one of the following, not both
            .Display ' to display the message so that the user can review it
        End With
        Set objOutlook = Nothing

    End Sub

    Thursday, October 23, 2014 7:46 PM

Answers

  • I believe I just solved my problem with the help of my Cloud Support team.  In the Account Settings tab we checked the "Use Cached Exchange Mode" and that seems to have cleared up the problem.  Must have something to do with Office 365 and connectivity working exclusively online.  
    Thursday, October 30, 2014 1:54 PM

All replies

  • Hello,

    Did you try to debug the code? What line of code exactly generates the error?

    P.S. After adding recipients you need  to call the ResolveAll  method (or Resolve for each recipient in the collection) to resolve all the Recipient objects in the Recipients collection against the Address Book. For example:

    Sub CheckRecipients()  
     Dim MyItem As Outlook.MailItem  
     Dim myRecipients As Outlook.Recipients  
     Dim myRecipient As Outlook.Recipient 
     
     Set myItem = Application.CreateItem(olMailItem)  
     Set myRecipients = myItem.Recipients  
     myRecipients.Add("Aaron Con")  
     myRecipients.Add("Nate Sun")  
     myRecipients.Add("Dan Wilson")  
     If Not myRecipients.ResolveAll Then  
     For Each myRecipient In myRecipients  
     If Not myRecipient.Resolved Then  
     MsgBox myRecipient.Name  
     End If  
     Next  
     End If  
    End Sub

    Thursday, October 23, 2014 7:55 PM
  • jswan:  We have had code running on our production systems for years... and suddenly this Monday various staff members have started having the exact same issue.   I've created a basic 7 line .NET windows form which pulls up a new message in outlook, and when the user types in a "To" address and hits send, the same error!

    We are thinking it's possible a new .NET framework that was received as a windows update just in the last 2 weeks... but have not yet been able to verify.  Have you had any success with troubleshooting the problem?

    -Adam

    Friday, October 24, 2014 8:09 PM
  • Adam,

    What code did you use for reproducing the issue? Could you please also specify the build number of your Outlook?

    What is the target .net framework of your application?
    Friday, October 24, 2014 8:24 PM
  • Dim oApp As Outlook.Application Dim oEmail As Outlook.MailItem oApp = New Outlook.Application oEmail = oApp.CreateItem(Outlook.OlItemType.olMailItem) oEmail.Display()

    Here is the code... but it's impossible to debug any issue because once the popup message displays for a new message, the control is out of Visual Studio's hands... the issue is (as mentioned above) when you click 'send'.

    We have applications running on .NET 2.5, .NET 3.5 and .NET 4.5... many of which have been running fine for years without issue.

    • This is happening to about 10 of our 40 users.

    • Some running Windows 7 32 bit, others running Windows 7 64 bit.

    • Clicking "New Message" directly from outlook, allows the emails to be sent successfully.

    • Nothing is logged in the System / Windows Event Logs.

    • All Windows Updates on our working systems are also showing on the systems having the issue.

    • This is happening to users running both Outlook 2010 or Outlook 2013

    • A specific Build Version as an example:   Version 15.0.4659.1001


    Friday, October 24, 2014 8:40 PM
  • Well we can't say we know what caused the problem, but we did find a solution just last night. Still very strange that this suddenly started happening to about 15% of our users.

    1. Delete the users Roaming Profile on their PC (while logged in as administrator)
    2. Have the user log in again
    3. Control Panel -> Mail -> Show Profiles
    4. Delete the record
    5. Add a new record

    This does have some unfortunate side effects...

    • The users desktop items are all rearranged
    • The users homepage & favorites are gone
    • The network printers need to be added back in
    • Certain locally installed programs need to be "setup" the first time again
    Thursday, October 30, 2014 11:49 AM
  • I believe I just solved my problem with the help of my Cloud Support team.  In the Account Settings tab we checked the "Use Cached Exchange Mode" and that seems to have cleared up the problem.  Must have something to do with Office 365 and connectivity working exclusively online.  
    Thursday, October 30, 2014 1:54 PM