Results 1 to 4 of 4

If report is empty - skip sending

  1. #1
    tomazm71 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9

    If report is empty - skip sending

    Hi all,

    I have the code that runs the reports and put them as html into email's body. What I should write if I want the report to skip on sending the email if report is empty? Let's say the report in Mail 2 - Barbara is empty, I want the first one to be displayed and third one and the middle one to be skipped. The same situation could be with mail 1 and mail 2. I tried many macros but all of them stopped the action and did not go to Mail 3.

    Private Sub Combo_mail_Click()

    Dim sFile As String, lFile As Long, sHtml As String
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem


    'Mail 1 - Output the report to HTML in the temp directory
    sFile = Environ$("HOMEPATH") & "\Documents" & ".html"
    DoCmd.OutputTo acOutputReport, "Report_John", acFormatHTML, sFile

    'Read in the HTML File
    lFile = FreeFile

    Open sFile For Input As lFile
    sHtml = Input$(LOF(lFile) - 1, lFile)
    Close lFile

    'Put the file contents in the email body


    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "john@email.com"
    olMail.CC = "mark@email.com"
    olMail.Subject = "Report"
    olMail.HTMLBody = sHtml
    olMail.Display

    'Mail 2 - Output the report to HTML in the temp directory
    sFile = Environ$("HOMEPATH") & "\Documents" & ".html"
    DoCmd.OutputTo acOutputReport, "Report_Barbara", acFormatHTML, sFile

    'Read in the HTML File
    lFile = FreeFile

    Open sFile For Input As lFile
    sHtml = Input$(LOF(lFile) - 1, lFile)
    Close lFile

    'Put the file contents in the email body
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "barbara@email.com"
    olMail.CC = "mark@email.com"
    olMail.Subject = "Report"
    olMail.HTMLBody = sHtml
    olMail.Display

    'Mail 3 - Output the report to HTML in the temp directory
    sFile = Environ$("HOMEPATH") & "\Documents" & ".html"
    DoCmd.OutputTo acOutputReport, "Report_Andre", acFormatHTML, sFile

    'Read in the HTML File
    lFile = FreeFile

    Open sFile For Input As lFile
    sHtml = Input$(LOF(lFile) - 1, lFile)
    Close lFile

    'Put the file contents in the email body
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "andre@email.com"
    olMail.CC = "mark@email.com"
    olMail.Subject = "Report"
    olMail.HTMLBody = sHtml
    olMail.Display

    End

    End Sub


    Thanks,
    Tom

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,843
    You have 3 report objects? Are these 3 reports identical in structure? Are you sending the same HTML file to all 3?

    Code can open the report and test for data:

    DoCmd.OpenReport "Report_Barbara", acViewPreview, , , acHidden
    If Reports!Report_Barbara.HasData = True Then
    ...code
    End If
    DoCmd.Close acReport, "Report_Barbara", acSaveNo
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,337

  4. #4
    tomazm71 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    Hi June,

    It works perfectly fine now. Thanks for your help.

    #Solved

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

Similar Threads

  1. Sending a Report by Email
    By Robert2150 in forum Programming
    Replies: 3
    Last Post: 10-25-2014, 10:14 PM
  2. Sending A Report Via Email
    By Ka-Tet in forum Reports
    Replies: 0
    Last Post: 09-02-2013, 08:18 AM
  3. Combining 'sending mail' with 'sending a report'.
    By Mattbro in forum Programming
    Replies: 2
    Last Post: 11-25-2012, 06:42 AM
  4. Sending Info into A report
    By JayX in forum Access
    Replies: 18
    Last Post: 12-19-2011, 09:06 AM
  5. Replies: 0
    Last Post: 07-06-2010, 07:12 AM

Tags for this Thread

Posting Permissions

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