Results 1 to 11 of 11
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Late Binding busted?

    Hey Y'all,

    All of the sudden the following code no longer works with Late Binding! It still works fine with Early Binding.
    Access 2010, Outlook 2010, Win 10 Pro Build 10.0.10586

    Fails at the Red highlighted line with error message:
    mailitemfailed.PNG

    Conditional Compilation variables set in VBE Project Properties.
    LateBinding = 1 : MyDebug = 0 : conAccessVersionID = 2010
    Code:
    '                         +-------------------------+             +----------+
    '-------------------------|      EmailBills()       |-------------| 01/11/12 |
    '                         +-------------------------+             +----------+
    'Requires : PDFCreator {Open Source PDF Printer Driver}
    '           Sleep      {Windows API Function Declaration}
    'Called By:
    'Calls: ClearPDFDirectory()
    '       SetDateForBills()
    '       [Utilities] SwitchPrinters()
    '       [Utilities] zGetDBPath()
    
    Sub EmailBills()
    
       Dim dbName     As Database
       Dim rst        As Recordset
       Dim lRecNo     As Long
       Dim lBillCnt   As Long
       Dim zWhere     As String
       Dim zMsgBody   As String
    #If LateBinding = 0 Then    'Early Binding
       Dim appOL      As Outlook.Application
       Dim miMail     As Outlook.mailitem
    #Else
       Dim appOL      As Object
       Dim miMail     As Object
    #End If
    
       Dim oMyAttach  As Object
       Dim zAttFN     As String
       Dim zBillPath  As String
       
       If Not SetDateForBills() Then
         Exit Sub
       End If
       
       MsgBox "Please Note:" & vbCrLf & vbCrLf & _
              "If Microsoft Outlook is Closed the created Emails " & vbCrLf & _
              "will be sent to the INBOX folder." & vbCrLf & vbCrLf & _
              "If Microsoft Outlook is OPEN {recommended} the created Emails " _
              & vbCrLf & "will be sent to the DRAFTS folder." & vbCrLf & vbCrLf & _
              "When OUTLOOK is properly set press OK", _
              vbOKOnly + vbInformation, _
              "IMPORTANT INFORMATION:"
              
       zBillPath = zGetDBPath() & "EmailBills\"
              
       ClearPDFDirectory
       strDfltPrt = Application.Printer.DeviceName
       SwitchPrinters "PDFCreator"
       
       Set appOL = CreateObject("Outlook.Application")
       Set dbName = CurrentDb()
       Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
       rst.MoveFirst
       
       lBillCnt = 0
       zMsgBody = "Please find your WPOA annual dues statement attached." & _
                  vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                  vbCrLf & "Attachment: "
       Do
         If (rst![EMailDocs] And rst![EMail] <> "") Then
         
           zWhere = "[OwnerID] = " & Str(rst![OwnerID])
       
    'Note: If acNormal is selected the report is send automatically to the
    '      Default printer!
    '      If acPreview is selected the report is sent to the screen.
    
           DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
       
    '******* Rename file with OwnerID
    
    On Error GoTo WaitForPDFCreator
    Try_Again:
    
           Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
             Sleep 750           '** wait 1.25 secs before trying again **
           Loop
           
           Name zBillPath & "rptAnnualBilling.pdf" As _
                zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
    On Error GoTo 0
    '******* Begin Send Email
    
    #If LateBinding = 0 Then
           Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
    #Else
           Set miMail = appOL.CreateItem(0)
    #End If
    
           With miMail
               .To = rst![EMail]
               .Subject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
               .Body = zMsgBody & "Bill" & Trim(Str(rst![OwnerID])) & _
                       " Owner: " & rst![OwnerLName]
               .ReadReceiptRequested = True
               zAttFN = zBillPath & "Bill" & _
                        Trim(Str(rst![OwnerID])) & ".pdf"
               Set oMyAttach = miMail.Attachments.Add(zAttFN)
               .Save
           End With   'miMail
    
           Set miMail = Nothing
           lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
    
    '******* End Send Email
    
         End If
         
         rst.MoveNext        '*** Move to Next Record ***
       
       Loop Until rst.EOF
       
       MsgBox Format(lBillCnt, "#,###") & " Email Bills Created." & _
              vbCrLf & vbCrLf & _
              "Maximize Outlook and Press F8 and select the" & _
              "SendAllDrafts macro then click Run." & _
              vbCrLf & vbCrLf & _
              "If Outlook wasn't open when you created the Email" & _
              vbCrLf & "Bills you will have to move them to the" & _
              vbCrLf & "Drafts folder from the Inbox BEFORE you" & _
              vbCrLf & "run the macro!", vbOKOnly + vbInformation, _
              "Next Step:"
       GoTo GetOut
    
    WaitForPDFCreator:
       Select Case Err.Number
             Case 75
                 Sleep 0.75  '*** Wait another 3/4 second. ***
                 Resume Try_Again
             Case Else
                 MsgBox "Module:" & vbTab & "BillingsCode" & vbCrLf & _
                        "Routine:" & vbTab & "EmailMailBills" & vbCrLf & _
                        "Error: " & Err.Number & " " & _
                        Err.Description, vbCritical + vbOKOnly, _
                        "Unexpected Error:"
                 Resume GetOut
       End Select
       
    GetOut:
       Set rst = Nothing     '*** Close RecordSet ***
       Set oMyAttach = Nothing
       Set miMail = Nothing
       Set appOL = Nothing
       
       SwitchPrinters strDfltPrt
       
    End Sub                   '*** EmailBills() ***
    Any Ideas?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    I've been doing a little debugging and have come up with an interesting development.

    When I strip the routine down to just creating an email w/o the database stuff it works just fine with late & early binding.

    Code:
    Option Compare Database
    Option Explicit
    
    #Const LateBinding = 1
    
    Sub TestEmail()
    
       Dim zMsgBody   As String
    #If LateBinding = 0 Then    'Early Binding
       Dim appOL      As Outlook.Application
       Dim miMail     As Outlook.mailitem
    #Else
       Dim appOL      As Object
       Dim miMail     As Object
    #End If
    
       Dim oMyAttach  As Object
       Dim zAttFN     As String
        
       Set appOL = CreateObject("Outlook.Application")
       zMsgBody = "Please find your WPOA annual dues statement attached." & _
                  vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                  vbCrLf & "Attachment: "
     '******* Begin Send Email
    
    #If LateBinding = 0 Then
           Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
    #Else
           Set miMail = appOL.CreateItem(0)
    #End If
    
           With miMail
               .To = "SmithJones@gmail.com"
               .Subject = "WPOA Annual Dues Statement: Test"
               .Body = zMsgBody & "Bill Test" & _
                       " Owner: Test"
               .ReadReceiptRequested = True
    '           zAttFN = zBillPath & "Bill" & _
    '                    Trim(Str(rst![OwnerID])) & ".pdf"
    '           Set oMyAttach = miMail.Attachments.Add(zAttFN)
               .Save
           End With   'miMail
    
           Set miMail = Nothing
    
    '******* End Send Email
       
    GetOut:
    '   Set oMyAttach = Nothing
       Set miMail = Nothing
       Set appOL = Nothing
          
    End Sub                   '*** TestEmail() ***
    Another interesting observation is that it used to create the emails in the inbox of Outlook if Outlook was closed when the code ran and in the Drafts folder is Outlook was open. No it works the same if Outlook is OPEN but doesn't create the email if Outlook is CLOSED and no error messages ensue!

    Go Figure...
    Last edited by RetiredGeek; 2016-03-01 at 21:15.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I used this instead of the .To, and it works just fine:
    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(0)
    With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add(rs!EmailAddress)
    objOutlookRecip.Type = 1
    .Subject = strSubject
    ' etc,
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    RetiredGeek (2016-03-02)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mark,

    Thanks a Million! That did just the trick. Do you have any idea why the .To will only work with early binding?

    BTW: I didn't quite understand the need for the Set objOutlookRecip = part of the code. Since the object was not used any where else in the code. So I tested it w/o that and it works just fine. Any reason I should keep it in?

    The final code is:
    Code:
           With miMail
               .Recipients.Add (rst![EMail])
               .Subject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
               .Body = zMsgBody & "Bill" & Trim(Str(rst![OwnerID])) & _
                       " Owner: " & rst![OwnerLName]
               .ReadReceiptRequested = True
               zAttFN = zBillPath & "Bill" & _
                        Trim(Str(rst![OwnerID])) & ".pdf"
               Set oMyAttach = miMail.Attachments.Add(zAttFN)
               .Save
           End With   'miMail
    Actually, looking at the code now I think I'll try to get rid of the Set oMyAttach = .miMail also! Can't hurt to try.

    Thanks again for the much appreciated assistance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I also have no idea why its there! Don't know when or where I got the code from, I just copy it from project to project. It works so I don't question it!

    Glad it solved your problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by RetiredGeek View Post
    Actually, looking at the code now I think I'll try to get rid of the Set oMyAttach = .miMail also! Can't hurt to try.
    Removing that also worked just fine. Makes the code simpler and less objects to declare too.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    New Lounger
    Join Date
    May 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing values using variable

    Hello Team

    I'm trying to sendan email using your codes, with attachment of XL or PDF, the issue I'm having is when passingvalues using a variable to filter my report or query, it prompt toinput the values!
    I have tried it ondifferent machines with different windows and access versions;
    Windows 10, Access2013
    Windows 7, Access2010
    Windows 7, Access2007
    Here are the codes after making appropriate changes to work with my db,I tried using docmd with my variable as filter, where "Order_rpt" isreport name, "zWhere" is the criteria/filter, it prompt an inbox toenter value for zWhere!
    zWhere is hasstring value (Customer name)

    Code:
    Public Sub Daily_eml_rpt()
    
        Dim dbName As Database
        Dim rst As Recordset
        Dim lRecNo As Long
        Dim leMlCnt As Long
        Dim zWhere As String
        Dim zMsgBody As String
        Dim zEmail As String
        Dim zSubject As String
        Dim zDocname As String
        
        
            'Asssign Report Name to variable
        zDocname = "Order_Status_rpt"
        
            'Opens Current database
        
        Set dbName = CurrentDb()
            
            'Opens Recordset/Table
        Set rst = dbName.OpenRecordset("Customer_tbl", dbOpenDynaset)
        rst.MoveFirst
    
    
        leMlCnt = 0
      
            'Looping through the recordst to assign the customer name to variable
        Do While Not rst.EOF
    
    
            If rst![email] = "" Then
                MsgBox "No Data is available for selected date", vbOKOnly, "Daily Order Status"
            Else
                zWhere = rst![Customer]
                
                    'Assign filter to the report using variable
                DoCmd.OpenReport zDocname, acViewPreview, , "[Customer] = " & zWhere, acWindowNormal
                
                
                zEmail = rst![email]
                zSubject = "WPOA Annual Dues Statement: " & rst![Customer]
                zMsgBody = "Hi " & rst![Customer] & vbCrLf & "Please find your Today's Order Status attached."
                DoCmd.SendObject acReport, zDocname, acFormatPDF, zEmail, , , zSubject, zMsgBody, True
                DoCmd.Close acReport, zDocname, acSaveNo
                leMlCnt = leMlCnt + 1  '*** Count Emails Created ***
            End If
    
    
            rst.MoveNext        '*** Move to Next Record ***
        Loop
    
    
        MsgBox Format(lBillCnt, "#,###") & " Email Messages Created.", , "Customer Emails"
        Set rst = Nothing     '*** Close RecordSet ***
        
    End Sub
    Last edited by RetiredGeek; 2016-05-12 at 14:07. Reason: Added Code Tags

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Wasijk,

    Welcome to the Lounge as a new poster!

    Try it this way:

    Code:
                zWhere = "[Customer] = " &  rst![Customer]  '*** If Customer is a number use Str(rst![Customer])
                
                    'Assign filter to the report using variable
                DoCmd.OpenReport zDocname, acViewPreview, ,  zWhere, acWindowNormal
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    New Lounger
    Join Date
    May 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello HTH

    I appreciate your prompt response, I tried your suggestion but still the same issue, it prompt for inputbox.

    Customer is a text field containing business names only.



    Quote Originally Posted by RetiredGeek View Post
    Wasijk,

    Welcome to the Lounge as a new poster!

    Try it this way:

    Code:
                zWhere = "[Customer] = " &  rst![Customer]  '*** If Customer is a number use Str(rst![Customer])
                
                    'Assign filter to the report using variable
                DoCmd.OpenReport zDocname, acViewPreview, ,  zWhere, acWindowNormal
    HTH

  11. #10
    New Lounger
    Join Date
    May 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing values using variable

    Hello

    I selected the Microsoft Excel 15.0 Object Library....and it did the TRICK

    I'm now able to open the report based on filter criteria which I was assigning to the variable zWhere.

    I THANK YOU for your efforts, help and support

  12. #11
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    For some reason I've also found 2 systems with the same Office installed I had to take
    .to = me.email << works on original system but not on other
    and change it to
    .to = me.email.value

    Not a clue why.
    --
    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •