Open Outlook template and pass info from Access

[scottb]

Is it possible to launch and open an MS Outlook 2010 email template from a form within Access 2010 and pass an email address to populate in the Outlook "To:" field?
I have a template C:\Users\test\AppData\Roaming\Microsoft\Templates\Acceptance Letter.oft It is currently being launched as a desktop icon.
Question 1: How can I launch this from within an Access form? Question 2: Can I open this template and pass the email address and/or subject from fields (txtEmailAddress, txtProperty)) on my form (frmListing). Any help would be appreciated. Thanks. - Scott

[HansV]

Assuming you run the code from the form frmListing, you can use something like this:

Code: Select all

DoCmd.SendObject To:=Me!txtEmailAddress, Subject:=Me.txtProperty, _
    EditMessage:=True, MessageText:="...", _
    TemplateFile:="C:\Users\test\AppData\Roaming\Microsoft\Templates\Acceptance Letter.oft"

[scottb]

Thank you Hans. The fields are populating as desired in the new email but I cannot get the template to load into the body of the email.
Private Sub Label61_Click()
DoCmd.SendObject To:=Me!txtAgentEmail, Subject:="Award Notification", EditMessage:=True, MessageText:="...", TemplateFile:="C:\Users\Test\AppData\Roaming\Microsoft\Templates\Acceptance Letter.oft"
End Sub
Is there another command or other that I need to include to pull up the template?
I appreciate your help.

[HansV]

I'm sorry - I thought Access would use .oft templates, but I see the TemplateFile is actually used to specify a HTML file to be used when sending a database object in HTML format.
Unfortunately, this means that you can't use DoCmd.SendObject for your purpose - you'll have to automate Outlook from Access. Try this:

Code: Select all

Sub TestMail()
    If CreateMessage(Me!txtAgentEmail, "Award Notification", _
            "C:\Users\test\AppData\Roaming\Microsoft\Templates\Acceptance Letter.oft") = False Then
        MsgBox "Something went wrong!", vbExclamation
    End If
End Sub

Function CreateMessage( _
        Recipient As String, _
        Subject As String, _
        Template As String) As Boolean

    Dim objOL As Object
    Dim objMI As Object
    Dim blnNotActive As Boolean
    Dim objRecip As Object

    ' Check whether Outlook is active
    On Error Resume Next
    Set objOL = GetObject(, "Outlook.Application")
    blnNotActive = (Err <> 0)

    If blnNotActive Then
        ' If not, we start Outlook
        Err.Clear
        Set objOL = CreateObject("Outlook.Application")
    End If

    On Error GoTo Err_Mail

    ' Create e-mail message
    Set objMI = objOL.CreateItemFromTemplate(Template)
    Set objRecip = objMI.Recipients.Add(Recipient)
    objMI.Subject = Subject
    objMI.Display
    CreateMessage = True

Exit_Mail:
    ' Release object memory
    On Error Resume Next
    Set objMI = Nothing
    Set objOL = Nothing
    Exit Function

Err_Mail:
    CreateMessage = False
    Resume Exit_Mail
End Function

[scottb]

Hans,
Thank you for the work you put into this. I need a little direction. Do I create a sub with the testmail() code and another function with the createmessage code or does it all make up one function? And at that point should I be able to call it with an onclick or other event by TestMail? Sorry for the dumb questions. I'm trying to get what you made to work. Thank you. Scott

[HansV]

CreateMessage is a generic function that you can use to create an e-mail message from a template. You should copy it into a general module (created by selecting Insert | Module in the Visual Basic Editor).

TestMail is an example of calling CreateMessage. You could use the On Click event procedure of a command button on your form:

Code: Select all

Private Sub cmdMail_Click()
    If CreateMessage(Me!txtAgentEmail, "Award Notification", _
            "C:\Users\test\AppData\Roaming\Microsoft\Templates\Acceptance Letter.oft") = False Then
        MsgBox "Something went wrong!", vbExclamation
    End If
End Sub

where cmdMail is the name of the command button.

[grovelli]

TemplateFile is actually used to specify a HTML file to be used when sending a database object in HTML format.

Under which circumstances would one want to send a database object in HTML format?

[HansV]

To be displayed on a website, perhaps?

[grovelli]

So if you send, say, a form in html format, once you display it on a website, would that form be able to accept and process data as it does in Access or would it be the same as displaying a picture of it?

[HansV]

DoCmd.SendObject merely creates a static copy of the data; the result is not interactive.

By the way, recent versions of Access do have the ability to create interactive web databases.