VBA Script to Create Multiple e-mails...

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

Hi,

In my office we currently, on a daily basis, using the Mail Merge
function of Word to e-mail customer bookings from our call centre to
the relevant branches. This makes use of an Excel spreadsheet with the
data to merge. However, one problem is that the branch managers would
really like to have the customer's name as part of the subject line
e.g. "Booking Information - <<CUSTOMERNAME>>". This is not possible
with Office 2000 Word e-mail merge function.

It has been suggested to me before that this is possible in VBA. I am
not a novice to programming - at all. However, I am not ver
proficient in VBA and its Ins & Outs.

What I need to be able to do is (from Outlook, Word or Excel - whatever
makes it easiest) create an e-mail message object (I think there is
such a class) and then build up the content in the message using each
row in the Excel spreadsheet.

I dont mind building the message up programatically i.e. (not VB syntax
I know):

message.line.write("Customer Booking for " +
Excel.row.cell[2].value);

and

message.subject = "Booking Notice: " + Excel.row.cell[2].value;

Obviously I would need this to loop until it comes to the last row in
the spreadsheet.

Any help with this would be greatly appreciated...

Mark Young
 
Am 21 Dec 2005 09:31:35 -0800 schrieb (e-mail address removed):

Mark, your question is how to loop through the rows of an Excel spreadsheet,
correct?

If the code is located in Excel you can access its object model immediately,
if its located somewhere else you need to add a reference to Excel via
Tools/References. That´s not really necessary but makes programming easier.)

Here´s a sample that loops through the first column, starting in A:1 until
there´s a cell containing no data. It´s being assumed that at least one
Workbook is opened and you want to search in Sheet(1) of the currently
active Workbook:

Dim oWb As Excel.Workbook
Dim oWs As Excel.Worksheet
Dim oRn As Excel.Range
Dim oCell As Excel.Range
Dim sFirstAdr As String

Set oWb = Application.ActiveWorkbook
Set oWs = oWb.Sheets(1)
Set oRn = oWs.Columns(1)

With oRn
Set oCell = .Find("*", .Cells(65535), xlValues)
If Not oCell Is Nothing Then
sFirstAdr = oCell.Address
Do
' regarding your pseudo code oCell.Value is row(i).cell(1).value
' The next column´s value in the same row is oCell.Offset(0,1).value
message.subject = oCell.Offset(0,1).value
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <> sFirstAdr
End If
End With
 
Back
Top