Sending Email from Access 2007 through Outlook 2007 using template

[Simon Hughes (ICSNET UK)]

I run a small home run I.T. Support company any have developed an Access 2007
database for all our business activities. I currently send email using a VBA
script although it does not allow me to be as flexible as I would like.

I often send emails to customers where I would like to have a good looking
email, as I have created an outlook template (oft) with our logo and the
formatting just as I want it.

An example of our requirement is to be able to add a time and date value
from an access form into one of these templates, along with initially opening
the template, then adding the email address again from a record value in the
access form.

My idea was to do it in a similar way to how we achieve sending data to a
Word 2007 template - by using bookmarks. This would allow us to preformat the
standard emails in a oft template file and then simply drop the required data
from the Access form into it at the bookmark locations.
Do you know where I can go with this, as I've spent hours crawling the
forums and have hit a wall!!

Look forward to your reply.

 

[Sue Mosher [MVP]]

An Outlook .oft file doesn't have bookmarks. You have two choices here:

1) Use a Word document as the starting point, and use the code technique
shown at http://www.outlookcode.com/codedetail.aspx?id=1333 to generate the
email messages.

-or-

2) Use placeholder text in your .oft file to mark the locations where you
want to replace that placeholder text with, for example, the date and time.

#2 is easier. Just call the Replace() function on the item's HTMLBody
property as needed.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"Simon Hughes (ICSNET UK)" <Simon Hughes (ICSNET
UK)@discussions.microsoft.com> wrote in message
news:[email protected]...

 

[Simon Hughes (ICSNET UK)]

Hi Sue,

Thanks for your reply, very helpful.

However, I am quite new to VBA and I am a little unsure of how to code the
Replace() function and how to call the oft templete to open and inset the
email address.

I ok with calling a new email message and sending the to,cc,bbc, subject and
body and even HTMLBody although not sure of the replace function.

Could you help at all, maybe you have an example that you have worked with
previously to point me in the right direction?

Thanks in advance.

Simon

 

[Sue Mosher [MVP]]

To create a new message from an .oft file, use the Outlook object model's
Application.CreateItemFromTemplate() method, passing the path to the .oft
file as the argument.

Replace() works the same no matter what text you use it on:

updatedText = Replace(originalText, findText, replaceText)

If you have problems making that work with your variables, post your
existing code.

There's a detailed example in my book, which is available on Google Books.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Simon Hughes (ICSNET UK)]

Hi Sue,

I appreciate your reply. I have just bought your book from Amazon am I am
expecting deliver on Thursday. Hopefully I can learn a thing or two from it !!

Which part of the book contains the sample code relating to my query?

Simon

 

[Sue Mosher [MVP]]

17.4.2 - Creating a message from a boilerplate template

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Simon Hughes (ICSNET UK)]

Hi Sue,

I have been reading through your book although I seem unable to grasp the
idea of writing the VBA for opening a oft file and utilising the replace
function.

I want to be able to send an email address to the email once the vba has
opened it, then replace some data with data from a MS Access form which is
where the vba is going to be initiated from.

Could you help at all?

Simon

 

[Sue Mosher [MVP]]

Do you have some particular questions? If not, then perhaps you should
implement the specific example in the book and step through its code in the
debugger, so you can get a better sense of how it all comes together.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Simon Hughes (ICSNET UK)]

Hi Sue,

Sorry for the unclear question.

What I was getting at was, the article in the book seemed to be aimed at
programatically replying to an email already received rather than generating
a new message from VBA in Access and inserting data from the DB into the
email template that I somehow need to open from VBA.

Hope this is a little clearer.

Regards,

Simon Hughes
ICSNET UK
t: 01495 70 80 90
m: 07747 41 41 41

 

[Ken Slovak - [MVP - Outlook]]

Sue's off for a few days.

To create a brand new blank email item and have a reference available to it
for adding data from a DB your code would start like this, assuming you have
an Outlook.Application object oOL:

Dim oMail As Outlook.MailItem
Set oMail = oOL.CreateItem(olMailItem)

You then add whatever properties you want from the DB information such as
Subject or Body or HTMLBody, etc. You can get the Recipients collection of
the mail item and add one or more recipients to that, etc.

 

[Laura Sheldon]

To Simon Hughes, I am very interested in how you built your email using the VBA script and would like to know how you have it working in concert with Access. I'm trying to figure out a way to send an email from access (theoretically) that would insert specific field information (names, customer names, etc) automatically based on clicking a button or running a macro/script. Kind of like using a Word mail merge that inserts names and addresses, etc.

Thanks, Laura



Simon Hughes (ICSNET UK) wrote:

Sending Email from Access 2007 through Outlook 2007 using template
16-Jan-10

I run a small home run I.T. Support company any have developed an Access 200
database for all our business activities. I currently send email using a VB
script although it does not allow me to be as flexible as I would like

I often send emails to customers where I would like to have a good lookin
email, as I have created an outlook template (oft) with our logo and th
formatting just as I want it

An example of our requirement is to be able to add a time and date valu
from an access form into one of these templates, along with initially openin
the template, then adding the email address again from a record value in th
access form

My idea was to do it in a similar way to how we achieve sending data to
Word 2007 template - by using bookmarks. This would allow us to preformat th
standard emails in a oft template file and then simply drop the required dat
from the Access form into it at the bookmark locations
Do you know where I can go with this, as I have spent hours crawling th
forums and have hit a wall!

Look forward to your reply.

Previous Posts In This Thread:

Sending Email from Access 2007 through Outlook 2007 using template
I run a small home run I.T. Support company any have developed an Access 200
database for all our business activities. I currently send email using a VB
script although it does not allow me to be as flexible as I would like

I often send emails to customers where I would like to have a good lookin
email, as I have created an outlook template (oft) with our logo and th
formatting just as I want it

An example of our requirement is to be able to add a time and date valu
from an access form into one of these templates, along with initially openin
the template, then adding the email address again from a record value in th
access form

My idea was to do it in a similar way to how we achieve sending data to
Word 2007 template - by using bookmarks. This would allow us to preformat th
standard emails in a oft template file and then simply drop the required dat
from the Access form into it at the bookmark locations
Do you know where I can go with this, as I have spent hours crawling th
forums and have hit a wall!

Look forward to your reply.

An Outlook .oft file does not have bookmarks.
An Outlook .oft file does not have bookmarks. You have two choices here

1) Use a Word document as the starting point, and use the code techniqu
shown at http://www.outlookcode.com/codedetail.aspx?id=1333 to generate th
email messages

-or

2) Use placeholder text in your .oft file to mark the locations where yo
want to replace that placeholder text with, for example, the date and time

property as needed

-
Sue Mosher, Outlook MV
Author of Microsoft Outlook 2007 Programming
Jumpstart for Power Users and Administrator
http://www.outlookcode.com/article.aspx?id=5

"Simon Hughes (ICSNET UK)" <Simon Hughes (ICSNET

Hi Sue,Thanks for your reply, very helpful.
Hi Sue

Thanks for your reply, very helpful

However, I am quite new to VBA and I am a little unsure of how to code th
Replace() function and how to call the oft templete to open and inset th
email address

I ok with calling a new email message and sending the to,cc,bbc, subject an
body and even HTMLBody although not sure of the replace function

Could you help at all, maybe you have an example that you have worked with
previously to point me in the right direction?

Thanks in advance.

Simon



:

To create a new message from an .
To create a new message from an .oft file, use the Outlook object model's
Application.CreateItemFromTemplate() method, passing the path to the .oft
file as the argument.

Replace() works the same no matter what text you use it on:

updatedText = Replace(originalText, findText, replaceText)

If you have problems making that work with your variables, post your
existing code.

There is a detailed example in my book, which is available on Google Books.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

Hi Sue,I appreciate your reply.
Hi Sue,

I appreciate your reply. I have just bought your book from Amazon am I am
expecting deliver on Thursday. Hopefully I can learn a thing or two from it !!

Which part of the book contains the sample code relating to my query?

Simon

:

17.4.
17.4.2 - Creating a message from a boilerplate template

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

Hi Sue,I have been reading through your book although I seem unable to grasp
Hi Sue,

I have been reading through your book although I seem unable to grasp the
idea of writing the VBA for opening a oft file and utilising the replace
function.

I want to be able to send an email address to the email once the vba has
opened it, then replace some data with data from a MS Access form which is
where the vba is going to be initiated from.

Could you help at all?

Simon

:

Do you have some particular questions?
Do you have some particular questions? If not, then perhaps you should
implement the specific example in the book and step through its code in the
debugger, so you can get a better sense of how it all comes together.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

Hi Sue,Sorry for the unclear question.
Hi Sue,

Sorry for the unclear question.

What I was getting at was, the article in the book seemed to be aimed at
programatically replying to an email already received rather than generating
a new message from VBA in Access and inserting data from the DB into the
email template that I somehow need to open from VBA.

Hope this is a little clearer.

Regards,

Simon Hughes
ICSNET UK
t: 01495 70 80 90
m: 07747 41 41 41



:

Sue's off for a few days.
Sue's off for a few days.

To create a brand new blank email item and have a reference available to it
for adding data from a DB your code would start like this, assuming you have
an Outlook.Application object oOL:

Dim oMail As Outlook.MailItem
Set oMail = oOL.CreateItem(olMailItem)

You then add whatever properties you want from the DB information such as
Subject or Body or HTMLBody, etc. You can get the Recipients collection of
the mail item and add one or more recipients to that, etc.




Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Custom Validation Using the Enterprise Library
http://www.eggheadcafe.com/tutorial...c-33ef1ec7d1a3/wpf-custom-validation-usi.aspx

 

[Simon Hughes (ICSNET UK)]

Hi Sue,

I have implemented code to open (with help from your book) an OFT, pass data
from placeholders in the template from my MS Access Database.

The code does open the template and display it although the data is not
passed. The code runs without error ???

Hope you don't mind, I have copied the code below >>>


#########################################
Sub EmailEInvoice_w_balance()

Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem
Dim objDoc As Object
Dim OutlookRecip As String
Dim strCustomer As String
Dim strHTML As String


Set myOlApp = CreateObject("Outlook.Application")
Set MyItem =
myOlApp.CreateItemFromTemplate("C:\EmailTemplates\EInvoice_withbalance.oft")
strCustomer = "Simon"
strHTML = Replace(MyItem.HTMLBody,"%subfirstname%", strCustomer)
MyItem.Display
Set objDoc = MyItem.GetInspector.WordEditor
objDoc.Windows(1).Selection.Find.ClearFormatting
objDoc.Windows(1).Selection.Find.Execute strSender
Set myOlApp = Nothing
Set MyItem = Nothing
Set objDoc = Nothing

End Sub
###################################

 

[Simon Hughes (ICSNET UK)]

Hi Laura,

I would be happy to help you with this. If you would like to email me
directly at (e-mail address removed) I can see what I can do to help !!

Simon

 

[Tony Toews [MVP]]

To Simon Hughes, I am very interested in how you built your email using the VBA script and would like to know how you have it working in concert with Access. I'm trying to figure out a way to send an email from access (theoretically) that would insert specific field information (names, customer names, etc) automatically based on clicking a button or running a macro/script. Kind of like using a Word mail merge that inserts names and addresses, etc.

Also see Microsoft Access Email FAQ
http://www.granite.ab.ca/access/email.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/