Send email from access using outlook template (1 Viewer)

[frankt68]

Hi!

I know this has been asked many times, but I couldn't find an answer that would work, at least not for me.
I'd like to send a report as pdf by email, using a Microsoft Outlook Template file (.oft). I have tried to use the OnClick event of a command button using DoCmd.SendObject and specifying the template file like this

DoCmd.SendObject acSendReport, "Report_Name", acFormatPDF, , , , "Test", , , "C:\Users\myName\AppData\Roaming\Microsoft\Templates\OutlookTemplate.oft"

It works and opens the email, but does not use the template file specified, just creates a blank new email with the report as an attachment.

I have also tried to use EMailDatabaseObject Macro, but the result is the same as with the above procedure.

However, I can open the template file with Outlook directly.
Does anyone know how to send an email using a saved Microsoft Outlook template?
I'm using Office 365.

 

[Gasman]

I do not believe you can use a Template with that command.?

You will need to use the Outlook object instead.

Here is one of mine.

    ' Get appdata path
    strAppdata = Environ("Appdata")
    
    ' Set paths
    strTemplatePath = strAppdata & "\Microsoft\Templates"

        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")

HTH

 

[frankt68]

I do not believe you can use a Template with that command.?

Well, Microsoft says you can, like this
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.sendobject
I took my pice of code from there. Obviously, it doesn't work....

You will need to use the Outlook object instead.

Here is one of mine.

    ' Get appdata path
    strAppdata = Environ("Appdata")
  
    ' Set paths
    strTemplatePath = strAppdata & "\Microsoft\Templates"

        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")

I'm afraid my knowledge of Access and VBA is too weak. As I understand it, this code sets the appropriate template as an Outlook object. What I don't know, however, is how can I use this to create an email with a report as an attachment?

 

[Gasman]

I stand corrected, but it does not work for me either and I tested my template path in windows explorer and that is correct.

Where I found the syntax, it also shows how to do via VBA.?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.sendobject

Your call.?

 

[frankt68]

After quite a few attempts, I came up with this code:

Sub CreateMail()
Dim myItem As Object
Dim olApp As New Outlook.Application
 
strAppdata = Environ("Appdata")

strTemplatePath = strAppdata & "\Microsoft\Templates"
 
Set myItem = olApp.CreateItemFromTemplate(strTemplatePath & "\OutlookTemplate.oft")
 
myItem.Display
    
Set olApp = Nothing
 
End Sub

Now a new email opens using the template, however, now I don't know how to attach a report as pdf?

 

[The_Doc_Man]

To add attachments try the example shown in this article:

Attachments.Add method (Outlook)

Office VBA reference topic

docs.microsoft.com

Look closer to the bottom of the article to see it.

 

[Gasman]

After quite a few attempts, I came up with this code:

Sub CreateMail()
Dim myItem As Object
Dim olApp As New Outlook.Application

strAppdata = Environ("Appdata")

strTemplatePath = strAppdata & "\Microsoft\Templates"

Set myItem = olApp.CreateItemFromTemplate(strTemplatePath & "\OutlookTemplate.oft")

myItem.Display
   
Set olApp = Nothing

End Sub

Now a new email opens using the template, however, now I don't know how to attach a report as pdf?

Google is *ALWAYS* my first point of call.

https://docs.microsoft.com/en-us/office/vba/api/outlook.attachments.add

I use it a lot.

 

[frankt68]

To add attachments try the example shown in this article:

Attachments.Add method (Outlook)

Office VBA reference topic

docs.microsoft.com

Look closer to the bottom of the article to see it.

Thank you for your answer.
With this approach, I guess I would have to export the report as pdf file first, save it to the disk, and then add it as an attachment. With DoCmd.SendObject I don't have to do this, I can send a report as pdf directly, without saving it to the disk.
I would rather use the DoCmd.SendObject method, but if I could not get it to work properly, I will probably follow the suggested approach.

 

[Gasman]

Thank you for your answer.
With this approach, I guess I would have to export the report as pdf file first, save it to the disk, and then add it as an attachment. With DoCmd.SendObject I don't have to do this, I can send a report as pdf directly, without saving it to the disk.
I would rather use the DoCmd.SendObject method, but if I could not get it to work properly, I will probably follow the suggested approach.

Yes, but as the MS way does not appear to work (I tried as well remember?) then consider it a workaround. You can always delete the file after the attachment. Clunky perhaps, but at least it does work.?

Think of it as a workaround while you contact MS to ask why it does not work? Good luck with that.

 

[Gasman]

Google is my first call point also and I use it a lot. Sometimes you can spend hours and hours, even days, looking for the right answer, but you can’t find it. Sometimes all you need is someone, who can point you in the right direction. Sometimes you need a more comprehensive help.
I guess you haven't thought about the possibility that if I could find the right answer to my problem, I wouldn’t be asking questions here, haven't you? I did an extensive Google search myself, but couldn’t find a suitable answer. In doing so, I also came across the solution you offered. As you can see from my answer above, it is not exactly my preferred solution. And it doesn't answer why I can't use the outlook template with the DoCmd.SendObject method to send an email.

TBH a lot of people appear to not even bother to use Google.? You are in the minority. You even mentioned that you had not found an answer in the first post. My Google comment was on how to attach a file.?
I would also like to know why the MS solution does not appear to work,now that it has been pointed out to me, but as I have another way to do it if need be, I am happy with that.
Whilst I can be stubborn at times, I can recognise sometimes, it is best to move on and not let it get to me, if I have an alternative solution.
A little more work perhaps, but also has the benefit of learning a little more.?

Good luck with it anyway.

 

[zeroaccess]

Unfortunately SendObject can only send plain text emails. I do not know what happens if you couple that with a template that is Rich Text or HTML. Perhaps that is where the hangup is.

 

[frankt68]

Unfortunately, SendObject can only send plain text emails. I do not know what happens if you couple that with a template that is Rich Text or HTML. Perhaps that is where the hangup is.

I tried also with a plain text template (created in outlook and saved as a .txt file). Unfortunately, it does not work either.

 

[June7]

If you really want SendObject, instead of a template file (which I can't get to work with SendObject either), build email body in VBA. In other words, the 'template' is hard-coded in VBA.

 

[xwavefive]

On the File menu, point to New, and then click Mail Message. In the message body, enter the content you want. In the message window, click the Microsoft Office Button. In the Save As dialog box, in the Save as type list, click Outlook Template.

 

[isladogs]

Instead of using your suggested approach with an Outlook template, consider using CDO (collaborative data objects) to send email directly from Access and avoid using Outlook altogether. This will give you full control over the appearance of the email
CDO includes all standard email features. For example: it can be used with plain text or HTML emails and attachments can be added.

See my example app: CDO EMail Tester