Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2010, how to email an open report.....

    Please bear with me I'm very new at VBA programing.

    I'm trying to email a report. I have a button on the report (code below) and it works however I need to modify the code to populate the DoCmd.SendObject "to" with the email address on the form.
    ----------------------------------------------------------------------------------------

    Private Sub cmdEMailLicense_Click()

    Dim strWhere As String

    If Me.Dirty Then 'Save any edits.
    Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
    MsgBox "Select a record to print"
    Else
    On Error Resume Next
    strWhere = "[Lic#] = """ & Me.[Lic#] & """"

    DoCmd.SendObject acReport, "SewerCleanersLicenseEmailPDF", "PDFFormat(*.pdf)", "", "", "", "Your license is attached", "Thank you!", True, ""

    End If


    End Sub
    -----------------------------------------------------------------------

    This works but obviously the "To" is blank on the Outlook email that it generates.

    I've tried to have the code look on the form and pull the email address in by adding the form & field but that gives me the error msg! "Run-time error '2498' An Expression you entered is the wrong data type for one of the arguments"

    DoCmd.SendObject acReport, "SewerCleanersLicenseEmailPDF", "PDFFormat(*.pdf)", "
    =([Sewer Cleaners Master List Entry Form].[SCEmailAddress])", "", "", "Your license is attached", "Thank you!", True, ""

    Can this be done (populating the "To" field) using the DoCmd.SendObject?

    Thanes in advance!!

    -Jim McCue

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The DoCmd.SendObject method has several properties that you can complete. They include the "To" properties, the "Subject" property and the "MessageText" property. You have already set the Subject, but your attempt to set the To property should not have "" characters around it, and should simply be either Forms![SewerCleaners Master List Entry Form].[SCEmailAddress] or more concisely, Me.SCEmailAddress presuming you are referring to a control on the active form.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Wendell,

    That got me real close. When I insert Me.SCEmailAddress I get the email address from the active form BUT it comes in with the "mailto" tag and looks like this; myemail@abc.com#mailto:myemail@abc.com#

    The filed type for SCEmailAddress is hyperlink, should it be something else? Any other suggestions?

    Obviously I'm in over my head!!

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I just use plain text fields for email addresses to avoid this sort of problem.

    In your case the email address is what comes after mailto:, minus the last #

    Try this:

    Dim strEmail as string
    strEmail = Me.SCEmailAddress
    strEmail = right(strEmail, len(strEmail) -Instr(1,strEmail,":")
    strEmail = left(strEmail,Len(strEmail)-1)

    This finds the : and returns everything after it, then removes the last character.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Jan 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,

    Thanks for the suggestion.

    I'm embarrassed to ask but just where would I put that string of code?

    Currently it's:
    DoCmd.SendObject acReport, "SewerCleanersLicenseEmailPDF", "PDFFormat(*.pdf)", Me.SCEmailAddress, "", "", "Your license is attached", "Thank you!", True, ""

    I've tried varitions of:

    DoCmd.SendObject acReport, "SewerCleanersLicenseEmailPDF", "PDFFormat(*.pdf)", Me.SCEmailAddress,
    Dim strEmail As String
    strEmail = Me.SCEmailAddress
    strEmail = right(strEmail, len(strEmail) -Instr(1,strEmail,":")
    strEmail = Left(strEmail, Len(strEmail) - 1),"", "", "Your license is attached", "Thank you!", True, ""

    End If

    But I always get an error msg.

    Any suggestion?

    -

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try this:


    Dim strEmail As String
    strEmail = Me.SCEmailAddress
    strEmail = right(strEmail, len(strEmail) -Instr(1,strEmail,":")
    strEmail = Left(strEmail, Len(strEmail) - 1)
    DoCmd.SendObject acReport, "SewerCleanersLicenseEmailPDF", "PDFFormat(*.pdf)", strEmail,"", "", "Your license is attached", "Thank you!", True, ""
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Jan 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks John,that works great!

    Very, VERY much appreciated!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •