Insert records into outlook from Excel.

[Guest]

Hi Everybody,
I have an Excel file with several hundred records as follow
A B
Name Project
Bob a
Bob b
John c
John d
John e
Mike f
. .
As you can see there might be several projects for each person (1 to 30) ,
also I have a standard letter in outlook that I want to email it to each
person and include all the projects he/she has. I want to use use VBA code to
achive this task any help greatly appriciated .
Note: I can't use mail merge because I don't want to send several emails to
each person when they have more than one project .
I'm mainly looking for the code to read excel from outlook also insert those
data in the body text of outlook in a proper paragraph.

 

[Michael Bauer [MVP - Outlook]]

From Outlook, please add a reference to Excel to your VBA project via
Tools/References. Then you can see Excel's object model in the object
browser (f2) as well.

To open a workbook see Excel.Application.Workbooks.Open. You can access a
single cell with the Range object and move to the next cell with the Offset
function.

Please open the Excel VBA help and see samples for the mentioned methods.

There's different ways to add text to an e-mail template. Easiest approach
might be using placeholders in the e-mail. E.g.: "... interesting text #1
and more..."

Then you can use the replace function to replace #1 by anythign else:

Item.Body = Replace(Item.Body, "#1", "anything else")


--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Thu, 4 Oct 2007 14:22:02 -0700 schrieb Edward:

 

[Guest]

Thanks Michael, My specific question now is : In word when we want to insert
a text after the third paragraph we can write:
Activedocument.paragrphs(3).range.insertafter "text"
what is the equivalent code in Outlook . I think placeholders are not a good
option because the text i want to insert varies between 1 to 30 paragraphs(
project names inserting line after line). Any thoughts?

 

[Michael Bauer [MVP - Outlook]]

As you already know Word's object model you might want to use that. Simply
ensure that Word is being used as mail editor (Outlook options), and then
the Word.Document object is available through the Inspector.WordEditor
property.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Fri, 5 Oct 2007 13:28:00 -0700 schrieb Edward:

 

[Guest]

Thanks Michael , acctually I'm calling Outlook from word for sending the
documnets
and it works fine. Now I'm looking for a way to open Outlook contacts email
address list in word. Now I have to type the email address in a text box
inorder to use it in the code for sending the email to a proper person but i
want to know if there is a way to open Outlook email address dialogbox in
word?

 

[Michael Bauer [MVP - Outlook]]

In OL07 there's the GetSelectNamesDialog function, which returns the
SelectNamesDialog object. Please see the object browser (f2) for details.

Prior to OL07 you would have to add the CDO 1.21 Library to your project and
call the AddressBook function.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Tue, 9 Oct 2007 12:19:01 -0700 schrieb Edward:

 

[Guest]

Thanks Michael again ! I coudn't find the CDO 1.21 library to add as a
refrence but I used a different trick , I display the outlook mailitem with
all the data inserted from word into it and now user can user outlooks
address book and use its send button to send the email.
Thanks a lot for you time.