Need late binding syntax for emailing in Microsoft Access VBA.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Need late binding syntax for the following code.
Currently I have a bunch of people with different versions of Microsoft Access and need to late-bind:
Anyone to try and tackle this I appreciate it - or if anyone has a better version that doesn't use CDO - that would be great too!

Public Function SendEmailReport(sReportName, sFromName As String, sFromEmail As String, sToEmail As String) As Boolean

  Dim bSuccess As Boolean
  Dim oApp As New Outlook.Application
  Dim oEmail As Outlook.MailItem
  Dim fileName As String, todayDate As String

  'test email sending to ... so it doesn't go to Jim until he sees the coding piece of it.
  sToEmail = "youremail@yahoo.com"

  'Export report in same folder as db with date stamp
  todayDate = Format(Date, "MMDDYYYY")
  fileName = Application.CurrentProject.Path & "\" & sReportName & "_" & todayDate & ".pdf"
  DoCmd.OutputTo acReport, sReportName, acFormatPDF, fileName, False

  'Email the results of the report generated
  Set oEmail = oApp.CreateItem(olMailItem)
  With oEmail
      .Recipients.Add sToEmail
      .Subject = "RE: Timesheet for " & sFromName
      .Body = "Please review timesheet for Week: " & " Approval for: " & sFromName
      .Attachments.Add fileName
      .Send
  End With

  bSuccess = True

Exit_Proc:

  SendEmailReport = bSuccess
  Exit Function


  MsgBox "Email successfully sent!", vbInformation, "EMAIL S"

End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
With Late Binding you essentially use Object instead of the actual Object name:

Dim oApp As Object ' New Outlook.Application
Dim oEmail As Object 'Outlook.MailItem

You'd then create the items:

Set oApp = CreateObject("Outlook.Application")

You'd also have to either include your Constants, or use the actual value. For example, the value of olMailItem is 0, so you'd do this:

Dim olMailItem As Integer
olMailItem = 0

Or you could just use the value of 0 instead:

Set oEmail = oApp.CreateItem(0)

Finally, you'd remove the reference to the Outlook library.

Author

Commented:
Thank you so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial