Results 1 to 5 of 5

Automated Emails via Reports

  1. #1
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    59

    Automated Emails via Reports

    Hello,

    I have a report grouped by customer. The report itself is in excess of 300 pages - so it's rather large. So customers only have 1 page while others have 2 to 4 pages. Typically, we would take each the pages for a particular customer and manually email them. I would like to somehow automate emailing the report directly to the customer the report is for.

    Ideally, I would like the body of the report to be pasted inside the email, but it can also go as a PDF attachment if that is the only option. Is there a way to automate this so I can send the corresponding page(s) to the correct customer somehow through Access?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,881
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    Wayne is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Toronto, Canada
    Posts
    2
    Actually, I just went through this last week. Through some help from some smarter people than me, this is the code I came up with:

    Code:
    Private Sub btnEMailLeadTech_Click()
    
        On Error GoTo Err_btnEMailLeadTech_Click
    
    
        'Define some object variables for Outlook
        Dim olApp As Outlook.Application    
        Dim olNS As Outlook.NameSpace
        Dim olFolder As Outlook.MAPIFolder
        
        'Create a reference to the email item
        Dim olMailItem As Outlook.MailItem
        Dim strBodyText As String
        Dim strEMail As Variant
        Dim Signature As String
        Dim SigString As String
        Dim strPathWorkOrders As String
        Dim strPathTempFiles As String
        Dim strAddress As String
        Dim strPhone As String
        Dim strLine As String
        Dim intFile As Integer
        Dim MyLogo As String
        
        intFile = FreeFile()
        
        'Create the Outlook Object
        Set olApp = CreateObject("Outlook.Application")
        Set olNS = olApp.GetNameSpace("MAPI")
        Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
        Set olMailItem = olFolder.Items.Add("IPM.Note")
        
        'Create the string for the email address
        strEMail = DLookup("[EmpEmailAddress]", "tblEmployees", "[EmpFullName] = '" & Me.LeadTechnician & "'")
        
        If IsNull(Me.ClientAptNumber) Then
            strAddress = "<html><font face=Arial><font size=3>" & _
                        (Me.ClientStreetAddress & "," & "<br>" & vbCrLf & _
                        Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode & ".")
            Else
            strAddress = "<html><font face=Arial><font size=3>" & _
                        ("# " & Me.ClientAptNumber & " - " & Me.ClientStreetAddress & "," & "<br>" & vbCrLf & _
                        Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode & ".")
            End If
            
        If IsNull(Me.ClientPhone2) Then
            strPhone = "<html><font face=Arial><font size=3>" & _
            (Me.ClientPhone1 & " (" & Me.ClientPhoneType1 & ")")
            Else
            strPhone = "<html><font face=Arial><font size=3>" & _
            (Me.ClientPhone1 & " (" & Me.ClientPhoneType1 & ")" & "<br>" & vbCrLf & _
            Me.ClientPhone2 & " (" & Me.ClientPhoneType2 & ")")
            End If
    
    
        'Create the string for the Work Orders Directory Path
        strPathWorkOrders = DLookup("[WorkOrdersDirectoryPath]", "tblSysConfig", "[CompanyName] = '" & [Forms]![frmOrders]![CompanyName] & "'")
        
        'Create the string for the Temp Folder for the report to go to
        strPathTempFiles = DLookup("[TempFilePath]", "tblSysConfig", "[CompanyName] = '" & [Forms]![frmOrders]![CompanyName] & "'")
        
        'Create the body text report and store in a temporary directory
        DoCmd.OutputTo acOutputReport, "rptServiceDetails", acFormatHTML, strPathTempFiles & "\Order Details - " & Me.OrderNumber & _
            " - " & Me.ClientUserlastName & ".html"
            
        MyLogo = "<picture><img src='http://blah, blah, blah></picture><br>"
            
        'Create the signature for the email
        SigString = "C:\Filepath\Signatures\Wayne2.htm"
            If Dir(SigString) <> "" Then
                Signature = GetBoiler(SigString) & MyLogo
            Else
                MsgBox "Requested signature does not exist.", vbInformation
            End If
        
        'Open the report
        Open strPathTempFiles & "\Order Details - " & Me.OrderNumber & " - " & Me.ClientUserlastName & ".html" For Input As #intFile
        
        'Create the body of the message from the data in the form
        If IsNull(Me.ClientNotes) Then
            strBodyText = "<body><html><font face=Arial><font size=3>" & _
            "As lead technician assigned to this job, here is a copy of Work Order for customer " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName & "." & _
            "<br><br><b><u>" & "Service Date set for:" & "</b></u><br>" & _
            Format(Me.ServiceDate, "mmmm, dd, yyyy") & "." & "<br><br>" & vbCrLf & _
            "<b><u>" & "Arrival/start time of: " & "</b></u><br>" & _
            Format(Me.ApptTime, "h:mm AMPM") & " - " & _
            Format(Me.ApptTimeEnd, "h:mm AMPM") & "." & "<br><br>" & vbCrLf & vbCrLf & _
            "<b><u>" & "Job Address:" & "</b></u><br>" & vbCrLf & _
            strAddress & vbCrLf & "<br>" & strPhone & "<br><br>"
        Else
            strBodyText = "<body><html><font face=Arial><font size=3>" & _
            "As lead technician assigned to this job, here is a copy of Work Order for customer " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName & "." & _
            "<br><br><b><u>" & "Service Date set for:" & "</b></u><br>" & _
            Format(Me.ServiceDate, "mmmm, dd, yyyy") & "." & "<br><br>" & vbCrLf & _
            "<b><u>" & "Arrival/start time of: " & "</b></u><br>" & _
            Format(Me.ApptTime, "h:mm AMPM") & " - " & _
            Format(Me.ApptTimeEnd, "h:mm AMPM") & "." & "<br><br>" & vbCrLf & vbCrLf & _
            "<b><u>" & "Job Address:" & "</b></u><br>" & vbCrLf & _
            strAddress & "<br>" & vbCrLf & strPhone & "<br>" & vbCrLf & vbCrLf & _
            "<u><b>" & "Special Notes:" & "</u></b>" & "<br>" & Me.ClientNotes & "<br><br>"
        End If
        
        'Add in the report to the body of the email
        Do While Not EOF(intFile)
            Line Input #intFile, strLine
            
            strBodyText = strBodyText & strLine
        Loop
        
        Close #intFile
        
        'Attach the work order to the email  
        If Len(Dir(strPathWorkOrders & "\" & Me.OrderNumber & " " & _
                    Me.ClientUserlastName & " POD" & ".pdf")) = 0 Then
            MsgBox "The Work Order you are trying to attach does not exist in the directory selected.", vbInformation
            Exit Sub
            End If
            If IsNull(Me.ApptTime) Then
            MsgBox "You haven't entered an appointment start time. You must have a start time.", vbInformation
            Exit Sub
            Else
        'Update the new email object with the form data
        With olMailItem
            .Subject = Me.OrderNumber & " - " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName
            .To = Replace(Mid(strEMail, InStr(1, strEMail, ":") + 1), "#", "")
            .BCC = "MyEMail"
            .ReadReceiptRequested = True
            .HTMLBody = strBodyText & "</body></html><br>" & Signature
            .Importance = olImportanceHigh
            .Display
            .Attachments.Add strPathWorkOrders & "\" & Me.OrderNumber & " " & _
                    Me.ClientUserlastName & " POD" & ".pdf"
        End With
        End If
        
        'Release all of the object variables
        Set olMailItem = Nothing
        Set olFolder = Nothing
        Set olNS = Nothing
        Set olApp = Nothing
        
        'Get rid of the temporary file
        Kill (strPathTempFiles & "\Order Details - " & Me.OrderNumber & " - " & Me.ClientUserlastName & ".html")
        
    Exit_btnEMailLeadTech_Click:
        Exit Sub
        
    Err_btnEMailLeadTech_Click:
        MsgBox Err.Description, vbInformation
        Resume Exit_btnEMailLeadTech_Click
        
    End Sub
    It does put the report in the body of the email message, but it is for only one email at a time. You can put a looping routine in to send it to all recipients. Instead of .To in the message, you could use .Recipients for the email addresses.

    Hope this helps.

    Wayne

  4. #4
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    59
    pbaldy thank you for the link. I saw you posted this link on a different string from about 8 years ago. Honestly, it was too technical for me and I was not able to follow it even a little bit LOL. But, thank you for sharing. I am going to look at what Wayne posted and see if I can modify it to make it work for me.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,881
    No problem, that was the link that helped me get started with this type of thing, so I often post it in these situations. Post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 02-08-2017, 06:18 AM
  2. Automated email for reports
    By caniread in forum Reports
    Replies: 4
    Last Post: 08-02-2016, 08:39 PM
  3. Can this be automated on access
    By montypython in forum Database Design
    Replies: 1
    Last Post: 05-23-2012, 09:22 AM
  4. Automated import
    By salisbut in forum Import/Export Data
    Replies: 2
    Last Post: 07-16-2010, 11:56 AM
  5. Automated Email Reports - monthly no clicks
    By Bamber in forum Reports
    Replies: 1
    Last Post: 05-12-2010, 11:34 AM

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