{"id":1095,"date":"2016-01-07T18:06:09","date_gmt":"2016-01-07T23:06:09","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=1095"},"modified":"2016-07-04T16:29:53","modified_gmt":"2016-07-04T20:29:53","slug":"email","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=1095","title":{"rendered":"eMailing specific Reports to specific Recipients…"},"content":{"rendered":"

Question<\/strong>: Can anyone tell me how to eMail a report with content based data based on the individual?<\/p>\n

Answer<\/strong>:
\nCreate a Module, name it anything you like except the name of the Function.<\/p>\n

At the top of your Module under\u2026<\/p>\n

Option Compare Database
\nOption Explicit<\/p>\n

\u2026place<\/p>\n

\r\nPublic olApp As Object\r\nPublic olNameSpace As Object\r\nPublic objRecipients As Object\r\nPublic objNewMail As Object 'Outlook.MailItem\r\n<\/pre>\n
Then, we need something to initialize Outlook, so, copy\/paste the below into the same Module\u2026<\/p>\n
\r\nFunction InitializeOutlook() As Boolean\r\n' This function is used to initialize the global Application and\r\n' NameSpace variables.\r\n    \r\n    On Error GoTo Init_Err\r\n    Set olApp = CreateObject("Outlook.Application", "LocalHost")  ' Application object\r\n    Set olNameSpace = olApp.GetNamespace("MAPI")  ' Namespace object\r\n    Set objNewMail = olApp.CreateItem(0)\r\n    InitializeOutlook = True\r\nInit_Bye:\r\n    \r\n    Exit Function\r\nInit_Err:\r\n    InitializeOutlook = False\r\n    Resume Init_Bye\r\n    \r\nEnd Function\r\n<\/pre>\n
And now we can copy\/paste the Function into the same Module\u2026
\nNote: After you have copied it you will need to change the Paths, Query, Report and Field Names to match your own.<\/em><\/p>\n
\r\nFunction streMailOverdue() As String\r\nOn Error GoTo Error_Proc\r\n\u2018From: http:\/\/regina-whipp.com\/blog\/ \r\n    \r\n    DoCmd.Hourglass True\r\n    \r\n    'Set global Application and NameSpace object variables, if necessary.\r\n    If olApp Is Nothing Then\r\n        If InitializeOutlook = False Then\r\n            MsgBox "Unable to initialize Microsoft Outlook!"\r\n        End If\r\n    End If\r\n    \r\n    'Create new MailItem object.\r\n    Set objNewMail = olApp.CreateItem(0)\r\n    \r\n    Dim strTo As String\r\n    Dim strSQL As String\r\n    Dim rs As Recordset\r\n    Dim strSubject As String\r\n    Dim strBody As String\r\n    Dim strAttachment As String\r\n    \r\n    'Get the eMails for those who are to receive a Report\r\n    strSQL = "SELECT apAssociateID, apeMailAddress " & _\r\n                "FROM qryeMailAddresses"\r\n\r\n    Set rs = CurrentDb.OpenRecordset(strSQL)\r\n    \r\n    'Save Report outside Access\r\n    With rs\r\n        .MoveFirst\r\n            Do While Not .EOF\r\n                DoCmd.OutputTo acOutputReport, "rpteMailReport", acFormatPDF, "\\\\SERVERNAME\\data\\eMailReports\\" & !apAssociateID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"\r\n                strAttachment = "\\\\SERVERNAME\\data\\eMailReports\\" & !apAssociateID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"\r\n            'Send eMail and Report\r\n            Set objNewMail = olApp.CreateItem(0)\r\n            With objNewMail\r\n                .To = rs.Fields("apeMailAddress")\r\n                .Subject = "To Do List Items Overdue!"\r\n                .Body = "See attachment..."\r\n                    If strAttachment <> "" Then\r\n                        .Attachments.Add strAttachment\r\n                    End If\r\n                .Send\r\n            End With\r\n        .MoveNext\r\n        Loop\r\n            'Delete the Reports\r\n            If Dir("\\\\SERVERNAME\\data\\eMailReports\\*.pdf") <> "" Then\r\n                Kill "\\\\SERVERNAME\\data\\eMailReports\\*.pdf"\r\n            End If\r\n        End With\r\n        \r\n        rs.Close\r\n        Set rs = Nothing\r\n    \r\nExit_Proc:\r\n    DoCmd.Hourglass False\r\n    Exit Function\r\nError_Proc:\r\n    Select Case Err.Number\r\n        Case 287:\r\n          'On Error Resume Next\r\n          Resume Exit_Proc 'ignore the error'\r\n        Case Else:\r\n          MsgBox "Error encountered streMailOverdue: " & Err.Description\r\n          Resume Exit_Proc 'display a message then exit'\r\n    End Select\r\n\r\nEnd Function\r\n<\/pre>\n
Now, on your Form in the Event Procedure<\/strong> of your Command Button<\/strong> place…<\/p>\n
Call eMailOverdue<\/strong><\/p>\n
All done!<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Question: Can anyone tell me how to eMail a report with content based data based on the individual?<\/p>\n
Answer: Create a Module, name it anything you like except the name of the Function.<\/p>\n
At the top of your Module under\u2026<\/p>\n
Option Compare Database Option Explicit<\/p>\n
\u2026place<\/p>\n
 Public olApp As Object Public olNameSpace As Object Public […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[64,76,81,30,21],"class_list":["post-1095","post","type-post","status-publish","format-standard","hentry","category-access-tips","tag-access-tips","tag-email","tag-outlook","tag-reports","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1095","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1095"}],"version-history":[{"count":6,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1095\/revisions"}],"predecessor-version":[{"id":1101,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1095\/revisions\/1101"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1095"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1095"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1095"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}