Populate an Outlook 2007 template with spreadsheet data.

[Gwendolyn3883]

I need some basic advice about whether this is at all possible, and if
so, what it would entail.

I have a form, it spits data to a spreadsheet. (Currently both are
hosted on the web, but could both be hosted on local servers.)

I'd like an Outlook template to automagically populate with specific
data from the spreadsheet. I'd want to be able to do this once a week
with the new information submitted to the spreadsheet.

E.G.

Spreadsheet data:

1. John Accounting I love accounting
2. Sam Warehouse Betty has a new dog
3. Skip Operations I prefer tea, not coffee
4. Joe Accounting I was late to work on Sunday.
5. Sara Operations Red is not my favorite color.


E-mail:

Dear Boss,

Here is what folks had to say about their workgroups:

Accounting

* I love accounting
* I was late to work on Sunday

Operations

* Red is not my favorite color
* I prefer tea, not coffee

Warehouse

* Betty has a new dog.

Is this technically possible? I have beginner's experience with VB, but
I'm a very zippy learner. I'm curious right now if this is technically a
possibility. If so, can someone point me in the right direction?

Thank you very much for any help, in advance!

 

[Michael Bauer [MVP - Outlook]]

It's possible.

I'd use a class module as a collection for what the people have ot say:

<workgroup.cls>
Public Name$
Private m_Text$
Public Sub AddText(Text$)
m_Text = m_Text & Text & vbcrlf
End Sub
Public Function GetText() as String
GetText=m_Text
End Sub
</workgroup.cls>

In another module hold the collection of workgroup objects etc.:

Private m_Workgroups as New VBA.Collection

Public Sub ReadWorksheed()
On Error Resume Next
' open the worksheet, and read its content, that is in a loop walk through
the rows
' then add each row to a workgroup object, for instance read the second
column into the Name variable, and the third into the Text variable
Dim Name$, Text$
Dim wg as workgroup

Do While... ' loop through the rows
Set wg=m_Workgroups(Name)
If Err Then
' That group doesn't exist yet
Err.Clear
Set wg=New Workgroup
wg.Name=Name
wg.AddText Text
m_workgroups.Add wg,Name
Else
wg.AddText Text
Endif
Wend

For reading a worksheet, in Excel see the Workbooks.Open function, the Range
object, and the Range.Offset function.

--
Best regards
Michael Bauer - MVP Outlook
Category Manager - Easily share your categories:
SAM - Automatically choose sending account, signature, and sent message
folder:
<http://www.vboffice.net/product.html?lang=en>


Am Wed, 26 May 2010 15:12:00 -0400 schrieb Gwendolyn3883: