Email Report as Attachment using Caption Name and without Outlook Reference

[nikademous]

Hello, I have the VBA below I am using to attach reports and email through outlook 2016. My report name is the actual name of the report like rptSomeReport. How can I use the reports cation name as my attached report name.

Next, I don't have my database split and everytime I change versions of access it breaks reference to outlook. Can I change the way it opens outlook to avoid using outlook reference?

        Public Function EmailAsPDF()
'==================================================================================================
'//Code works with right click for my reports
'
'//Reference: Microsoft Outlook 12.0 or 16.0 or 16.0 Object Library
'==================================================================================================
    On Error GoTo Error_Handler
    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem
    Dim strSubject As String
    Dim strMessageText As String
    Dim rptCur As Access.Report
    Dim AttachmentName As String
    Set rptCur = Screen.ActiveReport

    'strSubject = "Something Here for subject. " & rptCur.txtEmployeeName
    'strMessageText = "Something Here for body."
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)
    AttachmentName = SaveOpenReportAsPDF(rptCur.Name)
    'Debug.Print AttachmentName
    With objEmail
        '.To = strgTo
        .Subject = strSubject
        .Body = strMessageText
        .Attachments.Add AttachmentName
        .Display
    End With
    DeleteSavedReport AttachmentName    'Deletes the saved .pdf
    CloseAllReports    'Close Report
Exit_Here:
    Set objOutlook = Nothing
    Exit Function
Error_Handler:
    MsgBox Err & ": " & Err.Description
    CloseAllReports
    Resume Exit_Here
End Function

Public Function DeleteSavedReport(FileName As String)
'==================================================================================================
'//Delete the saved .pdf, Filename is complete path and file name
'==================================================================================================
    On Error GoTo ErrorHandler
    If Dir(FileName) <> "" Then    ' the file already exists--delete it
        VBA.SetAttr FileName, vbNormal    ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill FileName    ' delete the file.
    End If
ErrorHandler:
    MsgBox Error$
End Function

 

[Andrzejek]

Can I change the way it opens outlook to avoid using outlook reference? "
You should be able to do that with late binding.


---- Andy

There is a great need for a sarcasm font.