Results 1 to 8 of 8
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Help navigating blank field in a sendobject vba


    I am using DoCmd.SendObject vba to email a report. I am using a dlookup to preset who the email should be sent to. However, sometimes the dlookup field may be blank. This causes the sendobject vba to error out. Any suggestions on how to get around this? Is there a way to just ignore the dlookup function if the field is empty. Below is the full sendobject code.

    Code:
    Private Sub btnEmailReport_Click()
    On Error GoTo btnEmailReport_Click_Err
    
    
        Dim ReportName As String
        Dim Subject As String
        Dim ToRecipient As String
        Dim CCRecipient As String
        Dim BCCRecipient As String
        Dim Message As String
    
    
        ReportName = "rptEmailReport"
        Subject = "Report"
        ToRecipient = ""
        CCRecipient = DLookup("ManagerEmail", "tblContacts", "Location='" & Me.Location & "'")
        BCCRecipient = ""
        Message = "Hello"
    
    
        DoCmd.RunCommand acCmdRefresh
        DoCmd.SendObject acReport, ReportName, "PDFFormat(*.pdf)", ToRecipient, CCRecipient, BCCRecipient, Subject, Message, True, ""
    
    
    btnEmailReport_Click_Exit:
        Exit Sub
    
    
    btnEmailReport_Click_Err:
    
    
        MsgBox "Error #" & Err.Number & " - " & Err.Description, , "RMIS Error"
    
    
        
        Resume btnEmailReport_Click_Exit
        
    End Sub

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Replace it with a dummy address?

    If CCRecipient = "" Then CCRecipient = "ReplaceThisAddress"

    However I'm surprised you don't get an error on the DLookup if it trys to set the variable to a null value ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    What is the point of trying to send an email with no destination addresses?
    Your To is blank, your BCC is blank, and you might not get a CC?

    So why send the email, which you obviously cannot, as you have to have at least one email address?

    You could send it to yourself of course, that would tell you that there were no valid addresses?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by Welshgasman View Post
    What is the point of trying to send an email with no destination addresses?
    Your To is blank, your BCC is blank, and you might not get a CC?

    So why send the email, which you obviously cannot, as you have to have at least one email address?

    You could send it to yourself of course, that would tell you that there were no valid addresses?
    The sendobject is marked as true which means the outlook window pops up. It doesn't just automatically send the email.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried to wrap the DLookup in the Nz() function?
    Code:
     CCRecipient = Nz(DLookup("ManagerEmail", "tblContacts", "Location='" & Me.Location & "'"),"")
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    @Vlad - That was my point if it was Null the OP would get an error in the DLookup, before the send object command was ever reached - so it must be ZLS?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think I see it now, the error would be not in the dLookup itself but when attempting to assign the null to the string variable.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    That worked!! Thanks so much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 18
    Last Post: 07-16-2017, 04:52 AM
  2. Replies: 3
    Last Post: 11-18-2016, 12:03 PM
  3. Replies: 15
    Last Post: 06-29-2015, 11:12 AM
  4. Replies: 5
    Last Post: 09-05-2014, 12:06 PM
  5. SendObject - HTML tags in 'To' field
    By Hello World in forum Forms
    Replies: 1
    Last Post: 10-20-2011, 02:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums