Using .fot outlook template to send email from access

Simon_ICSNET_UK
Simon_ICSNET_UK used Ask the Experts™
on
Hi all,

I am attempting to use the code shown below to send email directly from a MS Access form using VBA behind a button although I keep getting a runtime 13 error when stepping through and get to the line Set MyMail = MyOutlook.CreateItemFromTemplate("c:\emailtemplates\einvoice.oft")

I have the following references enabled ...
Visual Basic for Applications
MS Access 14.0 Object Libary
MS Outlook 14.0 Object Libary
OLE Automation
MS Office 14,0 Access database engine object libary
MS Internet Controls
MS Outlook View Control
MS ActiveX data objects 6.0 Libary


Public Function SendMyEmail()
Dim db As DAO.Database
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MyMail = MyOutlook.CreateItemFromTemplate("c:\emailtemplateseinvoice.oft")
MyMail.SentOnBehalfOfName = "a@a.com"
MyMail.To = Form_frmTemplate.txtTo.Value
MyMail.CC = Form_frmTemplate.txtE_CC.Value
MyMail.Subject = Form_frmTemplate.txtE_sub.Value
MyMail.OriginatorDeliveryReportRequested = False
MyMail.ReadReceiptRequested = False
MyMail.HTMLBody = Replace(MyMail.HTMLBody, "Variable Field", Form_frmTemplate.txtClaim)
MyMail.Display
'MyMail.BodyFormat = olFormatRichText
'MyMail.Send
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Sorry, the subject should read ... Using .oft outlook template to send email from access

Commented:
Line 7 differs from what you quoted in your question.

Author

Commented:
Yes, I just noticed that. That is a typo when putting it up on this site. It had the additional \ in the code and is actually coded as ... Set MyMail = MyOutlook.CreateItemFromTemplate("c:\emailtemplates\einvoice.oft")

Hopefully someone can spot anything else thats wrong with the code which maybe the reason for the runtime 13

Commented:
how did you create the einvoice.oft template?

Commented:
The reason I ask is it looks like VBA cannot find the template at that address.

Commented:
Can you verify the template exists as specified?

Author

Commented:
I created the template in word and saved as a oft.
If I change a letter in the file string it reports an error reporting the file could not be found.

Commented:
I never use templates but this URL seems to think you should generate it in Outlook:

http://www.howtogeek.com/howto/windows/create-an-email-template-in-outlook-2003/

Author

Commented:
Hi, thanks for the url. I took a look and if I recreate the email in outlook as per the recommendations I still get exactly the same error.

Any other thoughts guys?

Commented:
It seems strange to me that you would use a template which is supposed to have To:, CC:, Subject,  and Body already filled in - yet you redefine them all.  Anyway, I suggest you use the Windows browser to locate the template - then double-click on icon.  If it has been created properly, you should be taken to Outlook Inbox with a new message completed with the parameters you used to create the .oft file.  Does that work?

Author

Commented:
Yes that works just fine when opeinig the template through the windows explorer.

The reason for using the template is to use a predefinded email message with colours and content for several things - sending an e-invoice, appointment confirmations, work orders being opened, updated and closed down. I would just use html directly in access although thought it would be easier to use the outlook template file.

Commented:
Can you re-post the code you showed in the question?
I agree with GrayL - if you are creating the .oft, I would save it from Outlook.

I use a similar technique, but create the template in Word, and then grab the email body from Word. It might give you a new avenue to try if you are stuck. I've added some simplified code below from an app we have written recently.

You can also use the bookmarks in word to add text into the template before you create the email - but your replace method is just as good.



Open the
Set GetWordDoc = objWord.Documents.Add(strTemplatePath)
'* process the doc if you want to 
Set wItem = objDoc.MailEnvelope.Item
wItem.Save

myID = wItem.EntryID
objDoc.Close SaveChanges:=wdDoNotSaveChanges
Set oWordDoc = Nothing
Set wItem = Nothing
    
Set ObjNS = golApp.GetNamespace("MAPI")
Set oItem = ObjNS.GetItemFromID(myID)

'* Now you have the mail you can process the body
strMailbody = oItem.htmlbody

Open in new window

sorry - GetWordDoc should read objDoc in the code above.

I compiled the code I published from several sub routines using different variable names - and forgot to change that one.
Another couple of things to try.

Dim MyMail As Object, not as a mail (I presume the .oft is a mail)


Try getting an existing Outlook session rather than creating a new one.
Set objOutlook = GetObject(, "Outlook.Application")

Commented:
Simon:  I'm still puzzled why you want to use a template and then change the components of the EMail using code.  BTW, I singled stepped thought the Example listed in VB Help - CreateItemFromTemplate Method using the Immediate Pane and in place of

MyItem.Display

i ran:

MyItem.Send

using a template addressed to me and it worked without a problem.  

Commented:
Can you re-post the code as you now have it in your Module?

Author

Commented:
Public Function SendMyEmail()
Dim db As DAO.Database
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MyMail = MyOutlook.CreateItemFromTemplate("c:\emailtemplateseinvoice.oft")
MyMail.SentOnBehalfOfName = "a@a.com"
MyMail.To = Form_frmTemplate.txtTo.Value
MyMail.CC = Form_frmTemplate.txtE_CC.Value
MyMail.Subject = Form_frmTemplate.txtE_sub.Value
MyMail.OriginatorDeliveryReportRequested = False
MyMail.ReadReceiptRequested = False
MyMail.HTMLBody = Replace(MyMail.HTMLBody, "Variable Field", Form_frmTemplate.txtClaim)
MyMail.Display
'MyMail.BodyFormat = olFormatRichText
'MyMail.Send
End Function

Author

Commented:
Ok update on the issue.

After making the changes that David mentioned in relation to calling an existing outlook session and DIM MyMail as object I now get the new message to load, the subject is inserted and the to and cc fields get populated.

The problem now is that the Replace is not working. It doesn't show an error when stepping through although just doesn't change the text in the new email. There is a piece of text "Variable Field" and when the new message is shown this still shows the placeholder and not the data that the replace function was supposed to of used from the DB.

Thanks for you help so far guys, its greatly appreciated. Hopefully, if this one can be sorted, I will be well away with the new email app for our users to start trying.
Have a look in the HTML that is being created as the HTML Body. You may find that what you thought was 'Variable Field'  is now 'Variable%blah%Field' or something.

If you can change the name 'Variable Field' in the template so that it has no spaces and is just plain text in the template - no frills - then Outlook is less likely to screw it up for you.

Author

Commented:
I updated both the email template and the VBA so the placeholder is now [subfirstname].

I still get the same result where the data is not updated.
Did you look at the HTML code to see what your placeholder looks like?

It may seem pedantic, but I would put the data into variables and then do replace on the variables - if nothing else it makes it easier to debug.

Dim myBody as string
Dim myNewBody as string
Dim myField as string
Dim mtReplace as string
myBody = MyMail.HTMLBody
myField = "XXtextXX"     'I would use a name as simple as this
'myReplace = Form_frmTemplate.txtClaim
'To start I would test with simple text
myReplace = "Hello World"
myNewBody = Replace(myBody, myField, myReplace)
MyMail.HTMLBody = myNewBody
Just noticed syntax for creating Outlook session

should be:

Set objOutlook = CreateObject("Outlook.Application")

Author

Commented:
Ok thats working great. Nothing pedantic about getting a solution working.

How would I replace 3 fields using this method? Would I have to have a seperate myField and myReplace ie. myField2 etc

Author

Commented:
I have managed to get it working as per the code below, although if I wanted to replace several pieces of data then this code would get large. Just seems like there must be an easier way to do more than one replace.

Ive attached the code ...
Public Function SendMyEmail()
Dim db As DAO.Database
Dim MyOutlook As Outlook.Application
Dim MyMail As Object
Dim myBody As String
Dim myNewBody As String
Dim myNewBody2 As String
Dim myField As String
Dim mtReplace As String
Dim myField2 As String
Dim mtReplace2 As String

Set MyOutlook = GetObject(, "Outlook.Application")
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MyMail = MyOutlook.CreateItemFromTemplate("C:\EmailTemplates\ics-einvoice.oft")
MyMail.SentOnBehalfOfName = "billing@icsnet.co.uk"
MyMail.To = Forms![Customers]![EmailAddress]

myBody = MyMail.HTMLBody
myField = "subfirstname"
myReplace = [Forms]![Customers]![FirstName]
myField2 = "invno"
myReplace2 = Forms![Customers]![InvoiceNumber]
myNewBody = Replace(myBody, myField, myReplace)
myNewBody2 = Replace(myNewBody, myField2, myReplace2)
MyMail.HTMLBody = myNewBody2

MyMail.OriginatorDeliveryReportRequested = False
MyMail.ReadReceiptRequested = False
MyMail.Display
'MyMail.BodyFormat = olFormatRichText
'MyMail.Send
End Function

Open in new window

First - 'Set MyOutlook = New Outlook.Application'  is redundant - delete it

Second - There are ways you can loop through and change variable names, but because of the way you are retrieving the form data, I think you will need to create a long piece of code.

I would reuse the variable names, and set them to empty before re-using them.

myBody = MyMail.HTMLBody

myField = "subfirstname"
myReplace = [Forms]![Customers]![FirstName]
myBody = Replace(myBody, myField, myReplace)

myReplace = ""               ' you need this in case Forms![Customers]![InvoiceNumber] gives you an error and you will enter the previous data again

myField = "invno"
myReplace = Forms![Customers]![InvoiceNumber]
myBody = Replace(myBody, myField, myReplace)

etc

MyMail.HTMLBody = myBody

Author

Commented:
Thank you for all your knowledge on this one. I have been trying to achieve this for over a year on and off.

BTW, you don't happen to have any code to print a report to a PDF seamlessly through one click?
Re: code to print a report to a PDF -
- Probably best to start a new question on that one

Author

Commented:
Fantastic support on this one. Thank you.

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