Results 1 to 2 of 2
  1. #1
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40

    Send Multiple Objects to Single Recipient - Only if Object Exists

    I have a function to send multiple reports to a single recipient. Error codes allow me to skip reports that were not created due an 'on error code' (in the report properties) to cancel the report. The problem I'm having is I don't want the emails to go out if there are no attachments. This may be a simple fix but alas it stumps me at the moment.



    Function sndrpt_Dept1()
    On Error GoTo 0
    On Error Resume Next

    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem
    Dim strAttach1 As String
    Dim strAttach2 As String
    Dim strAttach3 As String
    Dim strAttach4 As String
    Dim strAttach5 As String
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)

    'Output Reports
    DoCmd.OutputTo acOutputReport, "Team #1", acFormatPDF, "C:\Temp\Team #1 Report.pdf", False
    DoCmd.OutputTo acOutputReport, "Team #2", acFormatPDF, "C:\Temp\Team #2 Report.pdf", False
    DoCmd.OutputTo acOutputReport, "Team #3", acFormatPDF, "C:\Temp\Team #3 Report.pdf", False

    'Set Attachments
    strAttach1 = "C:\Temp\Team #1 Report.pdf"
    strAttach2 = "C:\Temp\Team #2 Report.pdf"
    strAttach3 = "C:\Temp\Team #3 Report.pdf"

    'Generate email
    With objEmail
    .To = DLookup("[Department Manager Email]", "Dept Email Listings", "[Department]='Department #1")
    .Subject = "OverDue Items"
    .Body = "Fix these things."
    .Display
    .Attachments.Add strAttach1
    .Attachments.Add strAttach2
    .Attachments.Add strAttach3
    .Send
    End With

    'Remove attachments from drive
    Kill strAttach1
    Kill strAttach2
    Kill strAttach3

    End Function

  2. #2
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Added the functions below and was able to use an If statement before generating the email to stop the email if no attachments are found.

    Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose: Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    ' bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note: Does not look inside subdirectories for the file.
    'Author: Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long
    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
    If bFindFolders Then
    lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
    'Strip any trailing slash, so Dir does not look inside the folder.
    Do While Right$(strFile, 1) = ""
    strFile = Left$(strFile, Len(strFile) - 1)
    Loop
    End If
    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
    End Function
    Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
    End Function
    Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
    If Right(varIn, 1) = "" Then
    TrailingSlash = varIn
    Else
    TrailingSlash = varIn & ""
    End If
    End If
    End Function

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

Similar Threads

  1. Replies: 2
    Last Post: 11-20-2015, 03:21 PM
  2. Replies: 3
    Last Post: 09-18-2014, 07:13 AM
  3. Send object as PDF with caption name
    By Flytkjaer in forum Reports
    Replies: 6
    Last Post: 02-12-2012, 03:38 AM
  4. send object report
    By ldappa in forum Access
    Replies: 1
    Last Post: 07-19-2010, 10:10 AM
  5. Send Object to addresses in table
    By cm-net in forum Access
    Replies: 1
    Last Post: 04-26-2010, 02:36 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